SQLのNULLとは ― IS NULLと3値論理

SQLのNULLは、列に値が入っていないことを表す印です。NULLを=や<で比べると、結果は真でも偽でもないUNKNOWNになり、WHEREの条件ではその行が選ばれません。

SELECT文のWHERE句に 点数 = NULL と書いても、1行も選ばれません。NULLを含む列を条件や集計に使うと、思ったとおりの結果にならないことがあります。

次の成績の表を例にします。井上さんと江藤さんは部活に入っておらず、上田さんはテストを欠席しました。

部活と点数の列には、非NULL制約を付けていません。そのため、値の無い欄にはNULLが入ります。NULLは値の一種ではなく、値が無い状態を示す目印として扱います。

井上さんの点数の0は、テストを受けて0点だったという値です。上田さんの点数のNULLは、受けていないので点数が無いことを表します。0や空文字は値があり、NULLは値が無い状態です。

空文字は、長さが0の文字列です。MySQLの文書は、電話番号の列を例に違いを説明しています。NULLは番号が分からない状態で、空文字は電話を持っていないと分かっている状態です。PostgreSQLでも、空文字にIS NULLを使うと結果は偽になります。

ただし、Oracle Databaseは長さ0の文字列をNULLとして扱います。Oracleの文書は、この扱いが将来も続くとは限らないとして、空文字とNULLを同じものとして扱わないよう勧めています。

点数が無い行を探すつもりで、次のように書くと、結果は0行になります。

SELECT 氏名 FROM 成績 WHERE 点数 = NULL;   -- 0行

NULLは分からない値なので、何と比べても等しいかどうかが決まりません。そのため、比較の結果は真でも偽でもないUNKNOWNになります。NULL = NULL も、分からないものどうしなので、結果はUNKNOWNです。

NULLかどうかを調べるときは、=ではなくIS NULLを使います。IS NULLは、NULLなら真、値があれば偽を返し、UNKNOWNにはなりません。逆に、値がある行を選ぶときはIS NOT NULLを使います。

SELECT 氏名 FROM 成績 WHERE 点数 IS NULL;      -- 上田
SELECT 氏名 FROM 成績 WHERE 点数 IS NOT NULL;  -- 青木・井上・江藤・小野

NULLどうしを等しいとみなして比べる書き方もあります。標準SQLのIS NOT DISTINCT FROMがそれで、PostgreSQLで使えます。MySQLでは、同じ働きの演算子として<=>があります。

SQLの条件は、TRUE・FALSE・UNKNOWNの3つの値のどれかになります。この考え方を3値論理と呼びます。条件をANDやORでつなぐと、UNKNOWNが混じったときの結果は下の表のとおりになります。

ANDは、片方がFALSEなら、もう片方がUNKNOWNでもFALSEに決まります。ORは、片方がTRUEなら、もう片方がUNKNOWNでもTRUEに決まります。NOTはTRUEとFALSEを入れ替えますが、UNKNOWNはUNKNOWNのままです。

WHEREは、条件がTRUEの行だけを残す

WHERE句が結果に残すのは、条件がTRUEになった行だけです。FALSEの行とUNKNOWNの行は、どちらも結果から除かれます。PostgreSQLの文書も、結果が偽かNULLの行は捨てると説明しています。

検査制約では、扱いが逆になります。CHECKが拒否するのはFALSEの行だけで、UNKNOWNの行は追加されます。価格がNULLの行がCHECK (価格 >= 0) を通る例は、CREATE TABLEと制約の記事にあります。

次の図では、最初に示した成績の表にWHEREの条件を当てはめます。条件を選ぶと、各行の判定と、結果に残るかどうかが切り替わります。結果は、PostgreSQLで実際に実行して確かめたものです。

SELECT 氏名 FROM 成績
WHERE 点数 = NULL;

SELECT 氏名 FROM 成績
WHERE 点数 IS NULL;

SELECT 氏名 FROM 成績
WHERE 点数 IS NOT NULL;

SELECT 氏名 FROM 成績
WHERE 点数 < 60;

SELECT 氏名 FROM 成績
WHERE NOT (点数 >= 60);

SELECT 氏名 FROM 成績
WHERE 部活 <> '野球部';

SELECT 氏名 FROM 成績
WHERE 部活 <> '野球部' OR 部活 IS NULL;

SELECT 氏名 FROM 成績
WHERE COALESCE(点数, 0) < 60;

点数 < 60 と NOT (点数 >= 60) は、どちらも上田さんの行を残しません。NOTを付けても、UNKNOWNはTRUEに変わらないためです。NULLの行も含めたいときは、OR 列 IS NULL を足すか、COALESCEで値を補います。

NULLを含む計算の結果は、NULLになります。たとえば全員に5点を足す 点数 + 5 では、上田さんの行だけが結果もNULLです。値の無いものに5を足しても、値は決まらないためです。

COALESCEは、NULLを別の値に置き換える

COALESCEは、並べた引数のうち、NULLでない最初の値を返す関数です。COALESCE(点数, 0) は、点数に値があればその値を返し、NULLなら0を返します。引数がすべてNULLのときだけ、結果はNULLになります。

SELECT 氏名, COALESCE(部活, '未所属') AS 部活
FROM 成績;   -- 井上と江藤の部活は「未所属」になる

集約関数は、NULLを飛ばして計算する

COUNT(*)は行の数を数えますが、COUNT(列名)はその列がNULLでない行だけを数えます。AVGやSUMも、NULLを飛ばして計算します。成績の表で比べると、次のようになります。

AVG(点数)は欠席した上田さんを除いた4人の平均で、0点の井上さんは含みます。欠席を0点として5人で平均したいなら、COALESCEで0に置き換えてから平均します。対象の行が1件も無いときの結果は、GROUP BYと集約関数の記事で説明しています。

野球部以外の生徒を探すつもりで WHERE 部活 <> '野球部' と書くと、残るのは上田さんだけです。部活がNULLの井上さんと江藤さんは、条件がUNKNOWNになって除かれます。WHERE 部活 NOT LIKE '野球%' でも、同じ2人が消えます。

否定の条件を書くときは、NULLの行を残したいかを先に決めます。残したいなら、OR 部活 IS NULL を足します。前の節の図で、2つの書き方の違いを確かめられます。

ほかにNULLが結果を左右する場面

• UNIONは、NULLどうしを同じ値として扱い、1行にまとめます。 =で比べるときとは動きが違います。
• NOT IN の右側にNULLが1つでもあると、どの行も残りません。理由と避け方はサブクエリの記事で説明しています。値を並べたNOT INで各行がどう判定されるかは、INとBETWEENの記事の図で確かめられます。
• 外部結合（相手の表に対応する行が無くても行を残す結合）では、相手の行が無い列にNULLが入ります。その列にIS NULLを使うと、相手の無い行を探せます。結合の種類はJOINの記事で説明しています。
• CASE 点数 WHEN NULL THEN と書いても、NULLの行はこの分岐に入りません。値どうしを=で比べる書き方なので、CASE WHEN 点数 IS NULL THEN と書きます。2つの書き方の違いはCASE式の記事で比べています。
• GROUP BYでは、NULLの行どうしが1つのグループにまとまります。ORDER BYの昇順では、PostgreSQLはNULLを最後に、MySQLは最初に並べます。

• PostgreSQL 18 文書「Logical Operators」（3値論理の真理値表）
• PostgreSQL 18 文書「Comparison Functions and Operators」（NULLとの比較、IS NULL、IS DISTINCT FROM）
• PostgreSQL 18 文書「Table Expressions」（WHERE句は条件が真の行だけを残す）
• PostgreSQL 18 文書「Conditional Expressions」（COALESCE）
• PostgreSQL 18 文書「Aggregate Functions」（COUNT・AVGとNULL）
• PostgreSQL 18 文書「Sorting Rows」（ORDER BYでのNULLの位置）
• MySQL 8.4 Reference Manual「Working with NULL Values」
• MySQL 8.4 Reference Manual「Problems with NULL Values」（NULLと空文字の違い、集約関数）
• MySQL 8.4 Reference Manual「Comparison Functions and Operators」（<=>）
• Oracle Database SQL Language Reference「Nulls」（長さ0の文字列の扱い）