SQLのNULLは0や空文字と何が違う?IS NULLと三値論理を例題で解説
NULLって0と同じじゃないの? = NULL と書いたら、なぜ1行も出てこないんだろう。
SQLの問題で、NULLの入った行がどう扱われるのか分からず、答えが合わなかったことはありませんか?
先に答えをお伝えします。NULLは、値が入っていない、または分からないことを表す印です。0は数の値、空文字は長さ0の文字列という値ですが、NULLには値そのものがありません。
そのため、NULLと何かを比べても、正しいとも間違いとも決まらず、UNKNOWN(不明)という結果になります。WHERE句は結果がTRUEになった行だけを残すので、UNKNOWNになった行は抽出されません。この記事では、この記事のために作った会員表を使い、どの行が残りどの行が外れるのかを順番に確かめます。
基本情報技術者試験の全体像を先に確かめたい人は、こちらの親記事が参考になります。
【関連記事】基本情報技術者試験とは?試験内容・難易度・勉強法を初心者向けに解説
NULLとは?0や空文字とは違う「値が無い」状態¶
データベースの表では、すべての列にいつも値が入っているとは限りません。まだ登録されていない、あるいは分からない値を表すために使うのがNULLです。
この記事で使う会員表¶
ここからは、次の会員表を使います。ポイント列と電話番号列に、0・空文字・NULLをわざと混ぜてあります。
| 会員番号 | 氏名 | ポイント | 電話番号 |
|---|---|---|---|
| 1 | 青木 | 100 | 090-0000-0001 |
| 2 | 井上 | 0 | 080-0000-0002 |
| 3 | 上田 | NULL | NULL |
| 4 | 江藤 | 50 | (空文字) |
井上さんのポイントは0で、これは「0ポイント持っている」という値です。上田さんのポイントはNULLで、何ポイントなのかが登録されていない状態を表します。
江藤さんの電話番号は空文字です。空文字とは、文字が1つも入っていない長さ0の文字列のことです。一方、上田さんの電話番号はNULLなので、値そのものが入っていません。
0・空文字・NULLの違い¶
3つの違いを表で比べておきましょう。
| 種類 | 意味 | 値があるか | = で比べられるか |
|---|---|---|---|
| 0 | 数の0という値 | ある | 比べられる(0 = 0 はTRUE) |
| 空文字 | 長さ0の文字列という値 | ある | 比べられる('' = '' はTRUE) |
| NULL | 値が無い、または分からない | ない | 比べられない(結果はUNKNOWN) |
0と空文字は、見た目は空っぽでも、きちんとした値です。NULLだけが値の無い状態なので、比較のルールが変わります。
なお、データベース製品の中には、空文字をNULLとして扱うものもあります。試験では問題文に書かれた説明に従って読みましょう。
NULLとの比較はUNKNOWNになる|三値論理の考え方¶
ふつうの条件は、TRUE(真)かFALSE(偽)のどちらかになります。真は条件が成り立つこと、偽は成り立たないことです。
SQLではこれにUNKNOWN(不明)を加えた3つの値で条件を判定します。この考え方を三値論理と呼びます。NULLが入った比較は、TRUEでもFALSEでもなく、UNKNOWNになります。
WHEREはTRUEの行だけを残す¶
WHERE句は、条件がTRUEになった行だけを結果に残します。FALSEの行もUNKNOWNの行も、どちらも残りません。
次のSQLで、ポイントが50以上の会員を探してみます。
SELECT 氏名
FROM 会員
WHERE ポイント >= 50
会員表を1行ずつ判定すると、次のようになります。
| 氏名 | ポイント | ポイント >= 50 | 結果に残るか |
|---|---|---|---|
| 青木 | 100 | TRUE | 残る |
| 井上 | 0 | FALSE | 残らない |
| 上田 | NULL | UNKNOWN | 残らない |
| 江藤 | 50 | TRUE | 残る |
結果は青木さんと江藤さんの2行です。上田さんは、ポイントが分からないので50以上かどうか判定できず、UNKNOWNになって外れます。
条件を反対にしても戻ってこない¶
今度は条件を反対にして、WHERE ポイント < 50 で探してみましょう。結果は、ポイントが0の井上さんの1行だけです。
ここで注意したいのは、上田さんがどちらの結果にも出てこないことです。50以上の2行と50未満の1行を合わせても3行で、会員表の4行になりません。
WHERE NOT (ポイント >= 50) と書いても同じです。UNKNOWNをNOTで反対にしてもUNKNOWNのままなので、上田さんの行は残りません。条件で行を2つに分けたつもりでも、NULLの行はどちらにも入らないと覚えておきましょう。
AND・ORとUNKNOWNの組み合わせ¶
条件をANDやORでつないだときも、UNKNOWNが関わると結果が変わります。代表的な組み合わせを表にまとめます。
| 組み合わせ | 結果 | 理由 |
|---|---|---|
| TRUE AND UNKNOWN | UNKNOWN | 片方が分からないので、両方TRUEとは言えない |
| FALSE AND UNKNOWN | FALSE | 片方がFALSEなら、もう片方に関係なくFALSE |
| TRUE OR UNKNOWN | TRUE | 片方がTRUEなら、もう片方に関係なくTRUE |
| FALSE OR UNKNOWN | UNKNOWN | 片方が分からないので、TRUEかどうか決まらない |
| NOT UNKNOWN | UNKNOWN | 分からないものを反対にしても分からない |
迷ったときは、UNKNOWNのところにTRUEとFALSEを両方入れてみると分かります。どちらを入れても結果が同じならその値に決まり、入れる値で結果が変わるならUNKNOWNです。
たとえば FALSE AND UNKNOWN は、UNKNOWNにTRUEを入れてもFALSEを入れてもFALSEになります。だから結果はFALSEに決まります。
IS NULL と IS NOT NULL|NULLを調べる正しい書き方¶
NULLかどうかを調べたいとき、WHERE ポイント = NULL と書きたくなるかもしれません。しかし、この書き方ではどの行も残りません。
NULLとの比較は、相手が何であってもUNKNOWNになるからです。NULL = NULL でさえTRUEにはならず、UNKNOWNになります。
IS NULLでNULLの行を探す¶
NULLの行を探すときは、IS NULLを使います。IS NULLは、値がNULLならTRUE、そうでなければFALSEを返す書き方です。UNKNOWNにはなりません。
SELECT 氏名
FROM 会員
WHERE ポイント IS NULL
結果は上田さんの1行です。反対に、値が入っている行を探すときは IS NOT NULL を使います。WHERE ポイント IS NOT NULL なら、青木さん・井上さん・江藤さんの3行が残ります。
| 書き方 | 上田さん(NULL)の判定 | 結果の行数 |
|---|---|---|
| ポイント = NULL | UNKNOWN | 0行 |
| ポイント IS NULL | TRUE | 1行 |
| ポイント IS NOT NULL | FALSE | 3行 |
空文字とNULLは別の条件で探す¶
電話番号の列でも、空文字とNULLは別物として扱います。WHERE 電話番号 = '' で残るのは、空文字の江藤さんだけです。
上田さんの電話番号はNULLなので、= '' ではUNKNOWNになり残りません。電話番号が無い会員をまとめて探したいなら、WHERE 電話番号 = '' OR 電話番号 IS NULL のように、2つの条件をつなぎます。
SELECT・WHEREの基本の読み方を確かめたい人は、こちらの記事が参考になります。
【関連記事】基本情報のSQL問題の解き方|SELECT・WHERE・ORDER BYを例題で解説
計算・集計関数・結合でのNULLの扱い¶
NULLは比較のほかにも、計算や集計で特別な扱いを受けます。試験の問題で結果の値が合わないときは、ここを見直すと原因が見つかることがあります。
計算するとNULLになる¶
NULLを含む計算は、結果もNULLになります。上田さんのポイントに10を足して ポイント + 10 としても、結果は10ではなくNULLです。
元の値が分からないので、10を足した値も分からない、と考えると納得しやすいでしょう。0として計算されるわけではない点に注意が必要です。
集計関数はNULLを無視する¶
COUNTやAVGなど、複数の行をまとめて計算する関数を集計関数と呼びます。集計関数の多くは、NULLの行を計算から外します。会員表のポイント列で比べてみましょう。
| 集計の書き方 | 計算の中身 | 結果 |
|---|---|---|
| COUNT(*) | すべての行を数える | 4 |
| COUNT(ポイント) | NULLでない行を数える | 3 |
| SUM(ポイント) | 100 + 0 + 50 | 150 |
| AVG(ポイント) | 150 ÷ 3 | 50 |
COUNT(*) は行そのものを数えるので4になります。COUNT(ポイント) は、ポイントがNULLの上田さんを数えないので3です。
AVGの結果にも注意しましょう。NULLを0とみなせば 150 ÷ 4 = 37.5 ですが、実際はNULLの行を外して 150 ÷ 3 = 50 になります。0の井上さんは値があるので、平均の計算に含まれます。
外部結合で生まれるNULL¶
表の中にNULLが無くても、SQLの結果にNULLが現れることがあります。代表的なのが外部結合です。
外部結合では、相手の表に組み合わせる行が見つからないとき、相手の列をNULLで埋めて行を残します。そのNULLをIS NULLで探せば、相手のいない行を見つけられます。結合の仕組みは、こちらの記事で図を使って解説しています。
【関連記事】基本情報のSQL結合(JOIN)がわからない!内部結合・外部結合を図解
NOT INとNULLの落とし穴¶
副問い合わせのNOT INでも、NULLが問題を起こします。WHERE 会員番号 NOT IN (2, NULL) は、会員番号 <> 2 AND 会員番号 <> NULL と同じ意味です。<> は等しくないことを表す記号です。
後ろの 会員番号 <> NULL は必ずUNKNOWNになります。前半がTRUEでも、TRUE AND UNKNOWN はUNKNOWNなので、どの行も残りません。副問い合わせの読み方とあわせて、同じ日に公開したこちらの記事で確かめてみてください。
【関連記事】SQLの副問い合わせ(サブクエリ)の読み方|IN・EXISTSを例題で理解
NULLの練習問題¶
ここまでの内容を使って、結果を予測する練習をしてみましょう。答えを見る前に、行ごとにTRUE・FALSE・UNKNOWNを書き出してから考えてみてください。
練習1|ORでつないだ条件¶
SELECT 氏名
FROM 会員
WHERE ポイント >= 50 OR 電話番号 IS NULL
青木さんと江藤さんは、前半がTRUEなので残ります。井上さんは、前半がFALSEで後半もFALSEなので残りません。
上田さんは、前半がUNKNOWNで、後半の IS NULL がTRUEです。UNKNOWN OR TRUE はTRUEなので残ります。結果は青木さん・上田さん・江藤さんの3行です。
練習2|ANDでつないだ条件¶
SELECT 氏名
FROM 会員
WHERE ポイント < 100 AND 電話番号 <> ''
井上さんは、ポイント0で前半がTRUE、電話番号が空文字ではないので後半もTRUEです。結果に残ります。
江藤さんは前半がTRUEでも、電話番号が空文字なので後半がFALSEになります。上田さんは、前半も後半もNULLとの比較でUNKNOWNです。青木さんは前半がFALSEなので外れ、結果は井上さんの1行になります。
NULLで間違えやすい点¶
結果が合わなかったときに見直せるよう、つまずきやすい点を表にまとめます。
| 間違えやすい点 | 起きること | 確かめ方 |
|---|---|---|
| NULLを0と同じだと思う | 計算や平均の値がずれる | NULLは値が無い状態、と思い出す |
| = NULL で探す | 結果が0行になる | IS NULL を使う |
| 条件の反対にNULLの行が入ると思う | 行数の合計が合わない | UNKNOWNはどちらにも入らない、と数え直す |
| COUNT(列) を行数だと思う | 数が少なく出る | COUNT(*) と区別する |
| NOT IN の中のNULLを見落とす | どの行も残らない | 一覧にNULLが無いかを確かめる |
1行ずつ判定する習慣は科目Bでも役に立つ¶
この記事では、会員表を1行ずつ取り出し、条件がTRUE・FALSE・UNKNOWNのどれになるかを書き出しました。この進め方は、プログラムの動きを1行ずつ追うトレースとよく似ています。
科目Bで出題される擬似言語の問題でも、条件分岐の判定を1回ずつ書き出していけば、結果を落ち着いて追えます。擬似言語とは、試験のために決められた書き方でプログラムを表したものです。科目Bの出題内容を知りたい人は、こちらの記事を読んでみてください。
【関連記事】基本情報技術者試験の科目Bとは?擬似言語・アルゴリズム対策を初心者向けに解説
私たちの Giji Academy では、擬似言語の講座を記法の基礎から条件分岐・繰返し・配列・アルゴリズム、科目B対策まで段階的に用意しています。擬似言語シミュレーターを使えば、プログラムをブラウザ上で1行ずつ実行し、変数の値が変わる様子を確かめられます。
一部の教材と入門レベルの練習問題は、無料会員のまま使えます。講座は擬似言語の講座一覧から始められます。
まとめ¶
SQLのNULLは、値が入っていない、または分からない状態を表す印です。0や空文字はきちんとした値ですが、NULLには値そのものがありません。
NULLとの比較はUNKNOWNになり、WHERE句はTRUEの行だけを残すので、NULLの行は抽出されません。条件を反対にしても戻ってこない点や、AND・ORでの組み合わせ方も押さえておきましょう。NULLを調べるときは、= NULL ではなく IS NULL を使います。
計算ではNULLが広がり、集計関数はNULLの行を外して計算します。行ごとにTRUE・FALSE・UNKNOWNを書き出す習慣を付ければ、NULLの入った問題も一歩ずつ確かめながら解けるようになります。