
SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け
当サイトは Amazon.co.jp を宣伝しリンクすることで紹介料を得る手段を提供する、Amazonアソシエイト・プログラムの参加者です。価格・在庫はリンク先の最新情報をご確認ください。
WHERE phone = NULL と書いたら1行も返ってこない。NOT IN で「注文のない顧客」を出そうとしたら結果が空になる。平均値が思っていた数字と合わない。どれも SQL の NULL に関する典型的なつまずきで、原因はひとつにまとまります。NULL との比較は TRUE でも FALSE でもなく、UNKNOWN(不明)になるという規則です。
この規則さえ押さえれば、IS NULL を使う理由も、NOT IN が 0 行になる理由も、同じ理屈で説明できます。一方で、GROUP BY や UNIQUE 制約、ORDER BY のように「比較」とは別の規則で動く場面もあり、そこはデータベース製品ごとの差も出ます。
この記事では、小さなサンプルテーブルを最初に1回だけ定義し、すべてのクエリをそのデータに対して実行して結果を並べます。3値論理の仕組みから始めて、落とし穴になりやすい場面と、その回避方法を順に確認します。
NOTE
記事中の結果表は、特記のない限り SQLite 3.54.0(sqlite3 --version の出力は 3.54.0 2026-04-09)のメモリ DB で実際に実行した出力です。結果表の NULL は NULL 値、(空文字) は長さ 0 の文字列を表します。PostgreSQL の挙動のうち「PostgreSQL 14.20 で確認」と書いた箇所は、手元の一時クラスタで実行しました。MySQL・SQL Server・Oracle の挙動と、PostgreSQL 15 以降の機能は、各公式ドキュメントで確認した内容を載せています(手元での実行はしていません)。
NULL とは - 「値がない」ことを表す印
NULL は、その列に値が入っていないことを表す印です。Oracle のドキュメントは、NULL を「実際の値がわからないとき、または値に意味がないとき」に使うものと説明しています。たとえば次のような状況です。
- まだ決まっていない(発送日が未定の注文)
- わからない(電話番号を聞いていない顧客)
- 当てはまらない(紹介者のいない顧客の「紹介者 ID」)
大事なのは、NULL は値の一種ではないという点です。数値の 0 や、長さ 0 の文字列(空文字)とは別のものです。
| 入っているもの | 意味 | IS NULL の結果 |
|---|---|---|
0 | 0 という数値がある | 偽 |
''(空文字) | 長さ 0 の文字列がある | 偽(Oracle を除く。後述) |
| NULL | 値がない・不明 | 真 |
MySQL のリファレンスも、NULL を空文字と同じものだと考えるのは初心者によくある誤解だとしたうえで、NOT NULL の列に 0 や空文字を入れることは問題なくできる、と書いています。0 も空文字も「NULL ではない値」だからです。
サンプルデータ
以降の例はすべて次のデータを使います。
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT, -- 未登録は NULL
points INTEGER, -- 未集計は NULL
referrer_id INTEGER REFERENCES customers(id) -- 紹介者(いなければ NULL)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id), -- ゲスト購入は NULL
amount INTEGER, -- 金額未確定は NULL
coupon TEXT -- クーポン未使用は NULL
);
INSERT INTO customers VALUES
(1, 'user_a', '090-0000-0001', 120, NULL),
(2, 'user_b', NULL, 0, 1),
(3, 'user_c', '', NULL, 1),
(4, 'user_d', NULL, 50, NULL),
(5, 'user_e', '090-0000-0005', NULL, 2);
INSERT INTO orders VALUES
(101, 1, 3000, 'WELCOME'),
(102, 1, 1500, NULL),
(103, 2, 2000, NULL),
(104, NULL, 800, 'WELCOME'),
(105, 5, NULL, NULL);customers テーブル:
| id | name | phone | points | referrer_id |
|---|---|---|---|---|
| 1 | user_a | 090-0000-0001 | 120 | NULL |
| 2 | user_b | NULL | 0 | 1 |
| 3 | user_c | (空文字) | NULL | 1 |
| 4 | user_d | NULL | 50 | NULL |
| 5 | user_e | 090-0000-0005 | NULL | 2 |
orders テーブル:
| id | customer_id | amount | coupon |
|---|---|---|---|
| 101 | 1 | 3000 | WELCOME |
| 102 | 1 | 1500 | NULL |
| 103 | 2 | 2000 | NULL |
| 104 | NULL | 800 | WELCOME |
| 105 | 5 | NULL | NULL |
ポイントは、NULL・0・空文字をわざと混ぜてあることです。
- user_b の
pointsは 0、user_c と user_e のpointsは NULL - user_b と user_d の
phoneは NULL、user_c のphoneは空文字 - 注文 104 は
customer_idが NULL のゲスト購入 - 注文 105 は
amountが NULL(金額未確定)
= NULL が効かない理由と IS NULL
電話番号が未登録の顧客を探すつもりで、次のように書いたとします。
SELECT id, name FROM customers WHERE phone = NULL;結果は 0 行です。エラーにはならず、ただ1行も返りません。<> で「NULL ではない行」を探しても同じく 0 行になります。
SELECT id, name FROM customers WHERE phone <> NULL;理由は、NULL との比較の結果が、TRUE でも FALSE でもなく NULL(UNKNOWN)になるためです。比較の結果そのものを表示すると確認できます。
SELECT 1 = NULL AS eq,
1 <> NULL AS ne,
NULL = NULL AS null_eq_null,
NULL <> NULL AS null_ne_null,
1 < NULL AS lt;| eq | ne | null_eq_null | null_ne_null | lt |
|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL |
NULL = NULL ですら TRUE になりません。「不明な値」と「不明な値」が等しいかどうかは、やはり不明だからです。PostgreSQL のドキュメントも、NULL は不明な値を表すので、2つの不明な値が等しいかどうかはわからない、という趣旨で expression = NULL と書いてはいけない理由を説明しています。
NULL かどうかを調べるには、専用の述語 IS NULL と IS NOT NULL を使います。
SELECT id, name, phone FROM customers WHERE phone IS NULL;| id | name | phone |
|---|---|---|
| 2 | user_b | NULL |
| 4 | user_d | NULL |
SELECT id, name, phone FROM customers WHERE phone IS NOT NULL;| id | name | phone |
|---|---|---|
| 1 | user_a | 090-0000-0001 |
| 3 | user_c | (空文字) |
| 5 | user_e | 090-0000-0005 |
IS NULL と IS NOT NULL は、必ず TRUE か FALSE のどちらかを返します。user_c の空文字は NULL ではないので、IS NOT NULL の側に入っている点にも注目してください。「電話番号が実質的に未登録の顧客」を探すなら、NULL と空文字の両方を条件に入れる必要があります(後述の NULLIF で1つにまとめられます)。
NOTE
PostgreSQL には、x = NULL を x IS NULL に読み替える transform_null_equals という設定があります。ただしドキュメントは、= NULL が真になることを期待するアプリケーションは標準 SQL に従うよう修正することを強く勧める、としています。設定で合わせるのではなく、IS NULL と書くのが筋です。
「自分自身と比較しても等しくならない値」という点では、浮動小数点数の NaN に似ています(浮動小数点数 (IEEE 754) 入門)。ただし NaN 同士の等価比較が FALSE を返すのに対し、NULL の比較は第3の値 UNKNOWN を返します。この「第3の値」があることが、SQL の条件式を独特なものにしています。
3値論理 - TRUE・FALSE・UNKNOWN
多くのプログラミング言語の真偽値は TRUE と FALSE の2値ですが、SQL の条件式は TRUE・FALSE・UNKNOWN の3値で評価されます。PostgreSQL のドキュメントは「SQL は true、false、そして unknown を表す null の3値論理を使う」と書き、真理値表を載せています。
NOT
| a | NOT a |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
不明なものを否定しても、不明なままです。これが後で出てくる落とし穴の多くの原因になります。
AND
| AND | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
OR
| OR | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
表を丸暗記する必要はありません。UNKNOWN を「TRUE か FALSE のどちらかだが、どちらかわからない値」と読み替えると、すべて導けます。
FALSE AND UNKNOWNは FALSE。相手が TRUE でも FALSE でも、片方が FALSE なら AND は FALSE に決まるTRUE OR UNKNOWNは TRUE。相手がどちらでも、片方が TRUE なら OR は TRUE に決まるTRUE AND UNKNOWNは UNKNOWN。相手が TRUE なら TRUE、FALSE なら FALSE で、結果が決まらないFALSE OR UNKNOWNも UNKNOWN。同じ理由で結果が決まらない
SQLite で確かめます。SQLite は真偽値を 1 と 0 で表示し、UNKNOWN は NULL として表示します。
SELECT (NULL AND 0) AS null_and_false,
(NULL AND 1) AS null_and_true,
(NULL OR 1) AS null_or_true,
(NULL OR 0) AS null_or_false,
(NOT NULL) AS not_null;| null_and_false | null_and_true | null_or_true | null_or_false | not_null |
|---|---|---|---|---|
| 0 | NULL | 1 | NULL | NULL |
真理値表のとおりです。MySQL のリファレンスにも、1 AND NULL は NULL、0 AND NULL は 0、1 OR NULL は 1、0 OR NULL は NULL、NOT NULL は NULL という同じ結果が例として載っています。
NOTE
標準 SQL には、条件式の結果が UNKNOWN かどうかを調べる IS UNKNOWN、IS TRUE、IS NOT TRUE といった述語もあります。PostgreSQL のドキュメントは、これらは被演算子が NULL でも必ず true か false を返す、と説明しています。対応状況は製品によって異なるため、使う前に各製品のドキュメントを確認してください。
WHERE は TRUE の行だけを残す
3値論理が実際に効いてくるのは、条件式の結果をどう使うかの場面です。WHERE 句は、条件が TRUE になった行だけを残します。FALSE の行も UNKNOWN の行も、同じように捨てられます。Oracle のドキュメントは、この動きを「UNKNOWN はほとんど FALSE のように振る舞う」と表現しています。
ポイントが 50 以上の顧客を取り出します。
SELECT id, name, points FROM customers WHERE points >= 50;| id | name | points |
|---|---|---|
| 1 | user_a | 120 |
| 4 | user_d | 50 |
では、その条件を NOT で反転すると「残り全員」が返るでしょうか。
SELECT id, name, points FROM customers WHERE NOT (points >= 50);| id | name | points |
|---|---|---|
| 2 | user_b | 0 |
返ってきたのは user_b だけです。5人のうち、points が NULL の user_c と user_e はどちらの結果にも出てきません。NULL >= 50 は UNKNOWN で、NOT UNKNOWN も UNKNOWN だからです。
2値論理なら「A または A でない」は必ず真ですが、SQL では成り立ちません。
SELECT id, name, points FROM customers WHERE points >= 50 OR points < 50;| id | name | points |
|---|---|---|
| 1 | user_a | 120 |
| 2 | user_b | 0 |
| 4 | user_d | 50 |
「〜以外」を取り出す条件でも同じことが起きます。「ポイントが 0 ではない顧客」のつもりで <> を使うと、NULL の行は含まれません。
SELECT id, name, points FROM customers WHERE points <> 0;| id | name | points |
|---|---|---|
| 1 | user_a | 120 |
| 4 | user_d | 50 |
NULL の行も含めたいなら、明示的に OR points IS NULL を足します。
SELECT id, name, points FROM customers WHERE points <> 0 OR points IS NULL;| id | name | points |
|---|---|---|
| 1 | user_a | 120 |
| 3 | user_c | NULL |
| 4 | user_d | 50 |
| 5 | user_e | NULL |
NOT LIKE や NOT BETWEEN でも同様です。WHERE phone NOT LIKE '090%' を実行すると、返るのは空文字の user_c だけで、phone が NULL の user_b と user_d は返りません。否定形の条件を書くときは、対象の列に NULL がありうるかを必ず確認する、と覚えておくと事故が減ります。
HAVING 句と、JOIN の ON 句も WHERE と同じく、TRUE の行(組み合わせ)だけを採用します。
CASE 式でも同じ規則が働く
CASE 式の単純形(CASE 列 WHEN 値 THEN ...)は、内部で = による比較を行います。そのため WHEN NULL は決して一致しません。
SELECT name,
CASE points WHEN NULL THEN 'null' ELSE 'other' END AS simple_case,
CASE WHEN points IS NULL THEN 'null' ELSE 'other' END AS searched_case
FROM customers;| name | simple_case | searched_case |
|---|---|---|
| user_a | other | other |
| user_b | other | other |
| user_c | other | null |
| user_d | other | other |
| user_e | other | null |
NULL を判定したいときは、検索形の CASE WHEN 列 IS NULL THEN ... を使います。
CHECK 制約は UNKNOWN を通す
WHERE が「TRUE だけを通す」のに対して、CHECK 制約は逆に 「FALSE だけを弾く」という規則で動きます。UNKNOWN は通過します。
CREATE TABLE items (
id INTEGER PRIMARY KEY,
price INTEGER CHECK (price > 0)
);
INSERT INTO items VALUES (1, 100);
INSERT INTO items VALUES (2, NULL); -- 成功する
INSERT INTO items VALUES (3, -5); -- 失敗するSQLite での結果は次のとおりです。NULL の行は入り、負の値だけが弾かれます。
CHECK constraint failed: price > 0| id | price |
|---|---|
| 1 | 100 |
| 2 | NULL |
NULL > 0 は UNKNOWN であり、FALSE ではないので制約違反になりません。これは SQLite に限った話ではなく、各製品のドキュメントに同じ規則が書かれています。
- PostgreSQL: チェック式が true または null に評価されれば制約は満たされる。被演算子に NULL があるとほとんどの式は NULL になるので、チェック制約は NULL の格納を防がない
- SQL Server: CHECK 制約は FALSE に評価される値を拒否する。NULL は UNKNOWN に評価されるので、式に NULL があると制約をすり抜けることがある
- SQLite: CHECK 式が NULL に評価された場合は制約違反ではない
PostgreSQL 14.20 でも同じ CREATE TABLE と INSERT を実行し、NULL の行は入り、-5 の行だけが violates check constraint で弾かれることを確認しました。
NULL も防ぎたいなら、NOT NULL 制約を併記します。
CREATE TABLE items2 (
id INTEGER PRIMARY KEY,
price INTEGER NOT NULL CHECK (price > 0)
);
INSERT INTO items2 VALUES (2, NULL);NOT NULL constraint failed: items2.price| 場面 | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
WHERE・HAVING・ON | 行を残す | 行を捨てる | 行を捨てる |
CHECK 制約 | 通す | 弾く | 通す |
同じ UNKNOWN でも、場面によって「FALSE 寄り」にも「TRUE 寄り」にも扱われる点が、3値論理のわかりにくさの正体です。
NOT IN に NULL が混ざると 0 行になる
NULL の落とし穴のなかで、実害がいちばん大きいのがこれです。「注文が1件もない顧客」を NOT IN で取り出します。
SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);結果は 0 行です。user_c と user_d には注文がないのに、1行も返りません。エラーも警告も出ないので、気付かないまま「該当なし」として処理が進んでしまいます。
原因は、サブクエリの結果に NULL(ゲスト購入の注文 104)が含まれていることです。
式を展開して確かめる
IN は、= を OR でつないだ式の省略形です。サブクエリの結果は 1, 1, 2, NULL, 5 なので、重複を除いて展開すると次のようになります。
id IN (1, 2, 5, NULL)
-- 次の式と同じ
id = 1 OR id = 2 OR id = 5 OR id = NULLNOT IN はその否定です。OR の否定は、各項を否定して AND でつないだものになります(ド・モルガンの法則)。
id NOT IN (1, 2, 5, NULL)
-- 次の式と同じ
id <> 1 AND id <> 2 AND id <> 5 AND id <> NULL最後の id <> NULL は、id が何であっても UNKNOWN です。注文のない user_c(id が 3)で評価してみます。
3 <> 1 AND 3 <> 2 AND 3 <> 5 AND 3 <> NULL
TRUE AND TRUE AND TRUE AND UNKNOWN
= UNKNOWNTRUE AND UNKNOWN は UNKNOWN なので、WHERE で捨てられます。注文のある user_a(id が 1)は 1 <> 1 が FALSE なので、全体が FALSE になります。つまり、リストに NULL が1つでもあると、NOT IN の結果は FALSE か UNKNOWN にしかならず、TRUE になる行が存在しなくなります。
値を直接並べた場合も同じです。
SELECT 3 IN (1, 2, NULL) AS in_result,
3 NOT IN (1, 2, NULL) AS not_in_result,
1 IN (1, 2, NULL) AS in_hit,
1 NOT IN (1, 2, NULL) AS not_in_hit;| in_result | not_in_result | in_hit | not_in_hit |
|---|---|---|---|
| NULL | NULL | 1 | 0 |
PostgreSQL 14.20 でも 3 NOT IN (1, 2, NULL) は NULL、1 NOT IN (1, 2, NULL) は false になることを確認しました。PostgreSQL のドキュメントは、NOT IN について「左辺が NULL の場合、または等しい値が右辺になく、右辺の行に少なくとも1つ NULL がある場合、結果は true ではなく null になる」と明記しています。MySQL のリファレンスも、IN() は標準 SQL に従い、一致する値がなくリストに NULL があるときは NULL を返す、としています。
なお、肯定形の IN は NULL が混ざっていても実害が出にくい点も押さえておきましょう。一致する値があれば TRUE になり、なければ UNKNOWN になって捨てられるだけなので、結果の行は変わりません。
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);| name |
|---|
| user_a |
| user_b |
| user_e |
回避方法1: NOT EXISTS を使う
いちばん確実なのは、NOT EXISTS に書き換えることです。
SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);| name |
|---|
| user_c |
| user_d |
EXISTS は「サブクエリが1行でも返すかどうか」だけを見るので、結果は必ず TRUE か FALSE です。ゲスト購入の行は o.customer_id = c.id が UNKNOWN になってサブクエリの結果に入らないだけで、外側の判定を巻き込みません。
回避方法2: サブクエリから NULL を除く
NOT IN のまま、サブクエリ側で NULL を除く方法もあります。
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);| name |
|---|
| user_c |
| user_d |
結果は同じですが、条件を書き忘れると元の問題に戻ります。また、外側の列(ここでは customers.id)が NULL になりうる場合は、その行も UNKNOWN になって落ちます。列に NOT NULL 制約が付いていることがスキーマで保証されている場合を除き、NOT EXISTS を選ぶほうが安全です。
回避方法3: LEFT JOIN と IS NULL
外部結合で相手が見つからなかった行を拾う書き方もあります。
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;| name |
|---|
| user_c |
| user_d |
3つの書き方の性能面の違いや、セミジョイン・アンチジョインとしての扱いは、SQL の JOIN の種類と違いで取り上げています。
集約関数と NULL
集約関数は、基本的に NULL を飛ばして計算します。例外は COUNT(*) で、これだけは行そのものを数えます。
SELECT COUNT(*) AS all_rows,
COUNT(points) AS cnt_points,
SUM(points) AS sum_points,
AVG(points) AS avg_points,
MIN(points) AS min_points,
MAX(points) AS max_points
FROM customers;| all_rows | cnt_points | sum_points | avg_points | min_points | max_points |
|---|---|---|---|---|---|
| 5 | 3 | 170 | 56.666666666666664 | 0 | 120 |
COUNT(*)は 5。行数を数えるCOUNT(points)は 3。pointsが NULL ではない行だけを数えるSUM(points)は 170(120 + 0 + 50)。NULL は足さないAVG(points)は 56.67。170 を 5 ではなく 3 で割っている
MySQL のリファレンスは、COUNT()・MIN()・SUM() などの集約関数は NULL 値を無視し、例外は行を数える COUNT(*) である、と書いています。PostgreSQL のドキュメントでも、count(*) は入力行の数、count(式) は値が NULL ではない入力行の数、と区別されています。
AVG の分母に注意する
平均値は、NULL の行を「いないもの」として計算します。「未集計の顧客は 0 ポイントとして平均を出したい」のであれば、明示的に 0 へ置き換える必要があります。
SELECT AVG(points) AS avg_skip_null,
AVG(COALESCE(points, 0)) AS avg_null_as_zero,
SUM(points) * 1.0 / COUNT(*) AS sum_div_rows
FROM customers;| avg_skip_null | avg_null_as_zero | sum_div_rows |
|---|---|---|
| 56.666666666666664 | 34.0 | 34.0 |
56.67 と 34.0 のどちらが正しいかは、NULL が何を意味しているかで決まります。「未集計なので平均に含めない」のか、「0 として扱う」のかを、クエリを書く前に決めておく必要があります。
空集合の SUM は 0 ではなく NULL
対象の行が1行もないとき、COUNT は 0 を返しますが、SUM は NULL を返します。
SELECT COUNT(*) AS cnt,
SUM(amount) AS sum_amount,
COALESCE(SUM(amount), 0) AS sum_or_zero
FROM orders
WHERE customer_id = 999;| cnt | sum_amount | sum_or_zero |
|---|---|---|
| 0 | NULL | 0 |
PostgreSQL のドキュメントも「count を除き、これらの関数は行が1つも選択されなかったとき null を返す。特に、行がないときの sum は、期待されるかもしれない 0 ではなく null を返す」と注意を促し、coalesce で 0 に置き換える方法を案内しています。PostgreSQL 14.20 でも、空の入力に対して sum が NULL、count が 0 になることを確認しました。
アプリケーション側で合計を数値として受け取る場合、NULL が返ると型の不一致や計算ミスにつながります。合計を出すクエリでは COALESCE(SUM(...), 0) と書く習慣を付けておくと安全です。
NOTE
SQLite には、空集合でも 0.0 を返す独自の集約関数 TOTAL() があります(上のクエリで TOTAL(amount) は 0.0 を返しました)。ただし上の例のとおり結果は整数の 0 ではなく浮動小数点数の 0.0 で、SQLite 独自の関数なので移植性はありません。
COUNT(DISTINCT 列) も NULL を数えない
SELECT COUNT(DISTINCT coupon) AS distinct_coupons,
COUNT(coupon) AS cnt_coupon
FROM orders;| distinct_coupons | cnt_coupon |
|---|---|
| 1 | 2 |
クーポンの種類は WELCOME の1種類として数えられ、NULL は種類に含まれません。
ウィンドウ関数として使う場合も、集約関数が NULL を飛ばす点は同じです(SQL ウィンドウ関数入門)。
算術と文字列連結での NULL 伝播
NULL を含む計算の結果は、原則として NULL になります。「不明な値に 10 を足した結果」は、やはり不明だからです。
SELECT name,
points,
points + 10 AS plus10,
points * 0 AS times0,
name || ':' || phone AS label
FROM customers;| name | points | plus10 | times0 | label |
|---|---|---|---|---|
| user_a | 120 | 130 | 0 | user_a:090-0000-0001 |
| user_b | 0 | 10 | 0 | NULL |
| user_c | NULL | NULL | NULL | user_c: |
| user_d | 50 | 60 | 0 | NULL |
| user_e | NULL | NULL | NULL | user_e:090-0000-0005 |
NULL * 0 も 0 にはならず NULL です。また、phone が NULL の user_b と user_d は、連結した結果の文字列全体が NULL になり、名前まで消えています。空文字の user_c は消えていません。
SQLite の公式ページ「NULL Handling in SQLite」には、各製品で同じ実験をした結果の表があり、「NULL に何かを足すと NULL になる」「NULL に 0 を掛けると NULL になる」は、調査対象のすべての製品で共通だったとされています。
文字列連結は製品による差が大きい
算術と違い、文字列連結は製品ごとに挙動が分かれます。連結演算子(縦棒2つ)と CONCAT 関数で結果が違う製品もあります。
SELECT 'a' || NULL || 'b' AS pipe_result, -- NULL
concat('a', NULL, 'b') AS concat_result, -- ab
concat_ws('-', 'a', NULL, 'b') AS concat_ws_result; -- a-b各製品の扱いを整理します。
- PostgreSQL: 連結演算子は NULL が1つでもあれば NULL(14.20 で確認)。
concat関数は、ドキュメントに「NULL の引数は無視される」とあり、concat_wsも区切り文字以外の NULL を無視する - SQLite: 連結演算子は NULL になり、
concatとconcat_wsは NULL を飛ばす(3.54.0 で確認) - MySQL:
CONCAT()は引数のどれかが NULL なら NULL を返す。CONCAT_WS()は区切り文字より後ろの NULL を飛ばす。なお、縦棒2つの演算子は既定では論理 OR であり、文字列連結になるのは SQL モードPIPES_AS_CONCATを有効にした場合だけ - SQL Server:
CONCATは NULL を空文字に暗黙変換する。+演算子による連結は、CONCAT_NULL_YIELDS_NULLが ON のとき NULL になる。ドキュメントによると OFF の設定は非推奨で、SQL Server 2017 以降は常に ON - Oracle: 長さ 0 の文字列を NULL として扱う一方、連結演算子では NULL を長さ 0 の文字列のように扱うので、もう一方の値がそのまま返る。NULL になるのは両方が NULL のときだけ
同じ「CONCAT 関数」でも、PostgreSQL と SQL Server は NULL を飛ばし、MySQL は全体を NULL にします。製品をまたいで動かす SQL では、NULL になりうる値を COALESCE で空文字に置き換えてから連結するのが確実です。
COALESCE・NULLIF と、製品固有の関数
NULL を別の値に置き換える、あるいは特定の値を NULL に変える関数を整理します。
COALESCE - 最初の NULL ではない値を返す
COALESCE は、引数を左から順に見て、最初に見つかった NULL ではない値を返します。すべて NULL なら NULL を返します。標準 SQL の関数で、引数はいくつでも並べられます。
SELECT name,
COALESCE(phone, '未登録') AS phone_label,
COALESCE(points, 0) + 10 AS plus10
FROM customers;| name | phone_label | plus10 |
|---|---|---|
| user_a | 090-0000-0001 | 130 |
| user_b | 未登録 | 10 |
| user_c | (空文字) | 10 |
| user_d | 未登録 | 60 |
| user_e | 090-0000-0005 | 10 |
user_c の phone は空文字なので、COALESCE では置き換わりません。空文字は NULL ではないからです。
PostgreSQL のドキュメントによると、COALESCE は結果を決めるのに必要な引数だけを評価し、最初の NULL ではない引数より右側は評価しません。PostgreSQL 14.20 で COALESCE(NULL, 1, 1/0) を実行すると、0 除算のエラーにならず 1 が返りました。
NULLIF - 特定の値を NULL に変える
NULLIF(a, b) は、a と b が等しければ NULL を、そうでなければ a を返します。COALESCE の逆向きの操作です。空文字を NULL に寄せるときに使えます。
SELECT name,
phone,
NULLIF(phone, '') AS phone_or_null,
COALESCE(NULLIF(phone, ''), '未登録') AS phone_label
FROM customers;| name | phone | phone_or_null | phone_label |
|---|---|---|---|
| user_a | 090-0000-0001 | 090-0000-0001 | 090-0000-0001 |
| user_b | NULL | NULL | 未登録 |
| user_c | (空文字) | NULL | 未登録 |
| user_d | NULL | NULL | 未登録 |
| user_e | 090-0000-0005 | 090-0000-0005 | 090-0000-0005 |
COALESCE(NULLIF(列, ''), 既定値) の組み合わせで、NULL と空文字の両方を既定値に置き換えられます。
NULLIF で 0 除算を避ける
NULLIF のもうひとつの定番が、割り算の分母です。分母が 0 のときに NULL へ変えておけば、割り算の結果が NULL になり、エラーを避けられます。
SELECT 100 / NULLIF(0, 0) AS div_nullif, -- NULL
COALESCE(100 / NULLIF(0, 0), 0) AS div_or_zero; -- 0実際の集計では、次のような形になります。
SELECT SUM(amount) / NULLIF(COUNT(amount), 0) AS avg_amount
FROM orders
WHERE customer_id = 999;0 除算の扱いそのものが製品で違う点にも注意が必要です。PostgreSQL 14.20 では SELECT 100 / 0 は ERROR: division by zero になり、100 / NULLIF(0, 0) は NULL を返しました。SQLite 3.54.0 では 100 / 0 はエラーにならず NULL を返します。MySQL はリファレンスの例で IFNULL(1/0, 10) が 10 を返しており、0 除算の結果が NULL になる場合があることがわかります(SQL モードによる違いは未確認です)。製品に依存しない書き方として、分母を NULLIF で包むのが無難です。
IFNULL・NVL・ISNULL - 製品固有の関数
COALESCE と似た働きの関数が、製品ごとに別の名前で用意されています。
| 関数 | 使える製品(確認できた範囲) | 引数 | 補足 |
|---|---|---|---|
COALESCE(a, b, ...) | PostgreSQL・MySQL・SQLite・SQL Server・Oracle | 2個以上 | 標準 SQL。迷ったらこれを使う |
IFNULL(a, b) | MySQL・SQLite | 2個 | a が NULL でなければ a、NULL なら b |
NVL(a, b) | Oracle | 2個 | a が NULL なら b、そうでなければ a |
ISNULL(a, b) | SQL Server | 2個 | 戻り値の型は第1引数と同じ |
NULLIF(a, b) | PostgreSQL・MySQL・SQLite | 2個 | 等しければ NULL、違えば a |
PostgreSQL のドキュメントは、COALESCE について「他のデータベースシステムで使われている NVL や IFNULL と同様の機能を提供する、標準 SQL の関数」と説明しています。
注意したいのは ISNULL です。名前は同じでも、製品によって意味が違います。
- SQL Server の
ISNULL(a, b)は、NULL をbに置き換える関数。ドキュメントによると、戻り値は第1引数と同じ型で、置き換える値は第1引数の型へ暗黙変換できるものでなければならない - MySQL の
ISNULL(expr)は、引数が1つで、NULL なら 1、そうでなければ 0 を返す判定用の関数
移植性を考えると、置き換えには COALESCE を使うのが安全です。
IS DISTINCT FROM - NULL を含めて比較する
「2つの列の値が同じかどうか」を、NULL 同士も同じとみなして比較したい場面があります。変更前と変更後の値を比べて差分を検出する処理などです。= では NULL 同士が一致しないので、素直に書くと長くなります。
WHERE (a = b) OR (a IS NULL AND b IS NULL)標準 SQL には、このための述語 IS DISTINCT FROM と IS NOT DISTINCT FROM があります。結果は必ず TRUE か FALSE になります。
| a | b | a = b | a IS NOT DISTINCT FROM b | a IS DISTINCT FROM b |
|---|---|---|---|---|
| 1 | 1 | TRUE | TRUE | FALSE |
| 1 | 2 | FALSE | FALSE | TRUE |
| 1 | NULL | UNKNOWN | FALSE | TRUE |
| NULL | NULL | UNKNOWN | TRUE | FALSE |
サンプルデータで、「紹介者が同じ顧客の組」を探します。まず = で結合した場合です。
SELECT a.name, a.referrer_id AS a_ref, b.name AS other, b.referrer_id AS b_ref
FROM customers a
JOIN customers b
ON a.id < b.id AND a.referrer_id = b.referrer_id;| name | a_ref | other | b_ref |
|---|---|---|---|
| user_b | 1 | user_c | 1 |
IS NOT DISTINCT FROM に変えると、紹介者がいない(NULL)同士の組も一致します。
SELECT a.name, a.referrer_id AS a_ref, b.name AS other, b.referrer_id AS b_ref
FROM customers a
JOIN customers b
ON a.id < b.id AND a.referrer_id IS NOT DISTINCT FROM b.referrer_id;| name | a_ref | other | b_ref |
|---|---|---|---|
| user_a | NULL | user_d | NULL |
| user_b | 1 | user_c | 1 |
製品ごとの対応状況
| 製品 | NULL 安全な比較 | 確認した内容 |
|---|---|---|
| PostgreSQL | IS DISTINCT FROM / IS NOT DISTINCT FROM | 公式ドキュメントに記載。14.20 で実行して確認 |
| SQLite | IS DISTINCT FROM / IS NOT DISTINCT FROM、独自の IS / IS NOT | 3.39.0(2022-06-25)で追加。IS と IS NOT は SQLite の拡張 |
| MySQL | <=> 演算子 | NULL 安全な等価比較。標準 SQL の IS NOT DISTINCT FROM と等価と明記 |
| SQL Server | IS [NOT] DISTINCT FROM | ドキュメントの適用対象は SQL Server 2022 (16.x) 以降 |
| Oracle | 未確認 | DECODE 関数は2つの NULL を等しいとみなす、という記述は確認 |
MySQL の <=> は、両方が NULL なら 1、片方だけが NULL なら 0 を返します。リファレンスの例では、1 <=> 1 と NULL <=> NULL が 1、1 <=> NULL が 0 です。否定は NOT (a <=> b) と書きます。
SQLite の IS は、右辺に NULL 以外も書ける独自の拡張で、a IS b が a IS NOT DISTINCT FROM b と同じ意味になります。SQLite のドキュメントは、この短い書き方は標準 SQL にはなく、他の多くの製品では IS NOT DISTINCT FROM と書く必要がある、と注意しています。
GROUP BY・DISTINCT・UNION では NULL 同士が同じ扱いになる
ここまで「NULL 同士は等しくならない」と説明してきましたが、行をグループにまとめたり重複を除いたりする処理では、NULL 同士は同じものとして扱われます。比較演算子とは別の規則が使われるためです。
SELECT referrer_id, COUNT(*) AS cnt
FROM customers
GROUP BY referrer_id;| referrer_id | cnt |
|---|---|
| NULL | 2 |
| 1 | 2 |
| 2 | 1 |
紹介者のいない user_a と user_d が、NULL という1つのグループにまとまっています。
SELECT DISTINCT coupon FROM orders;| coupon |
|---|
| WELCOME |
| NULL |
SELECT NULL AS v UNION SELECT NULL UNION SELECT 1;| v |
|---|
| NULL |
| 1 |
UNION ALL は重複を除かないので、NULL の行が2行残ります。
MySQL のリファレンスは「2つの NULL 値は GROUP BY では等しいとみなされる」と書いています。SQLite の「NULL Handling in SQLite」によると、SELECT DISTINCT と UNION で NULL を別々のものとして扱う製品は、調査した範囲に1つもありませんでした。同ページは、NULL が UNIQUE 列では別々に扱われるのに SELECT DISTINCT と UNION では同じに扱われるのは不可解だが、他の製品との互換性を優先してそう実装した、という経緯も記しています。
ウィンドウ関数の PARTITION BY や、再帰 CTE の UNION による重複除去も、同じく「まとめる」側の処理です。
ORDER BY での NULL の並び順
NULL は値ではないので、他の値との大小は決まりません。それでも並べ替えの結果には NULL の行も含める必要があるため、各製品が「NULL を先頭に置くか、末尾に置くか」を独自に決めています。そして、この既定の位置が製品によって逆です。
SQLite での実行結果です。
SELECT name, points FROM customers ORDER BY points;| name | points |
|---|---|
| user_c | NULL |
| user_e | NULL |
| user_b | 0 |
| user_d | 50 |
| user_a | 120 |
SQLite は NULL を「他のどの値よりも小さい」ものとして並べるので、昇順では先頭、降順では末尾に来ます。PostgreSQL 14.20 で同じ趣旨のクエリを実行すると、昇順では NULL が末尾、降順では先頭に来ました。
| 製品 | 昇順(ASC)での NULL | 降順(DESC)での NULL | NULLS FIRST / NULLS LAST |
|---|---|---|---|
| PostgreSQL | 末尾 | 先頭 | 使える |
| Oracle | 末尾 | 先頭 | 使える |
| MySQL | 先頭 | 末尾 | 8.4 の SELECT 構文に記載なし |
| SQLite | 先頭 | 末尾 | 3.30.0(2019-10-04)以降で使える |
| SQL Server | 先頭 | 末尾 | ORDER BY 句のドキュメントに記載を確認できず |
根拠は次のとおりです。
- PostgreSQL: 既定では NULL は NULL ではないどの値よりも大きいものとして並ぶ。つまり
DESCではNULLS FIRST、それ以外ではNULLS LASTが既定 - Oracle: 昇順では
NULLS LAST、降順ではNULLS FIRSTが既定 - MySQL:
ORDER BY ... ASCでは NULL が最初、ORDER BY ... DESCでは最後に表示される - SQLite: 並べ替えでは NULL を他のどの値よりも小さいとみなす
- SQL Server: NULL 値は最も小さい値として扱われる
PostgreSQL から MySQL へ、あるいはその逆へ移行すると、何も変えていないクエリの並び順が変わることになります。一覧画面の「未設定の行が上に来るか下に来るか」が変わる、ページングの境界がずれる、といった形で表に出ます。
NULLS FIRST / NULLS LAST で明示する
位置を明示できる製品では、NULLS FIRST か NULLS LAST を書いておくと、既定に依存しなくなります。
SELECT name, points FROM customers ORDER BY points ASC NULLS LAST;| name | points |
|---|---|
| user_b | 0 |
| user_d | 50 |
| user_a | 120 |
| user_c | NULL |
| user_e | NULL |
この構文が使えない製品では、「NULL かどうか」を先頭の並べ替えキーにする方法があります。points IS NULL は NULL の行で真、それ以外で偽になるので、偽の行が先に並びます。
SELECT name, points FROM customers ORDER BY points IS NULL, points;結果は上の表と同じ並びになりました。真偽値を並べ替えキーにできるかどうかは製品によるので、使えない場合は CASE WHEN points IS NULL THEN 1 ELSE 0 END に置き換えます。
UNIQUE 制約と NULL
UNIQUE 制約を付けた列に、NULL を複数入れられるかどうかも製品で分かれます。
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE
);
INSERT INTO accounts VALUES (1, NULL), (2, NULL), (3, 'a@example.com'); -- 成功する
INSERT INTO accounts VALUES (4, 'a@example.com'); -- 失敗するSQLite では NULL の2行は問題なく入り、同じメールアドレスの4行目だけが弾かれます。
UNIQUE constraint failed: accounts.email| id | |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | a@example.com |
「NULL 同士は等しくない」のだから重複にはならない、という理屈です。PostgreSQL 14.20 でも、UNIQUE 列に NULL を2行入れられることを確認しました。
| 製品 | UNIQUE 列の複数の NULL | 根拠 |
|---|---|---|
| PostgreSQL | 許す(既定)。15 以降は NULLS NOT DISTINCT で禁止できる | 公式ドキュメント、15 のリリースノート |
| MySQL | 許す | 「UNIQUE インデックスは、NULL を含められる列に複数の NULL 値を許す」 |
| SQLite | 許す | 「UNIQUE 制約では、NULL は他の NULL を含むすべての値と別のものとみなす」 |
| SQL Server | 許さない(1つだけ) | 「UNIQUE 制約は NULL を許すが、列ごとに1つの NULL だけが許される」 |
| Oracle | 許す(SQLite 公式の比較表による。Oracle 公式では未確認) | 「NULL Handling in SQLite」の表 |
PostgreSQL のドキュメントは、一意制約での NULL の扱いは標準 SQL では実装依存とされており、他の実装は異なる挙動を持つので、移植性を意識するアプリケーションでは注意するように、と書いています。
PostgreSQL 15 の NULLS NOT DISTINCT
PostgreSQL 15 では、一意制約とインデックスで NULL を「別々ではない」ものとして扱う指定が追加されました。リリースノートには、以前は NULL は常に別々の値として扱われていたが、UNIQUE NULLS NOT DISTINCT で変更できるようになった、とあります。
CREATE TABLE accounts (
id integer PRIMARY KEY,
email text UNIQUE NULLS NOT DISTINCT
);この指定を付けると、NULL の行は1行しか入りません。複数列の一意制約で「片方の列が NULL の行同士も重複として弾きたい」場合に役立ちます。PostgreSQL のドキュメントは、15 より前の方法として、IS NULL の条件を付けた一意な部分インデックスで NULL を1つに制限する方法も紹介しています。
外部結合で生まれる NULL
ここまでの NULL は、テーブルに保存されているものでした。もうひとつ、クエリの途中で新しく生まれる NULL があります。外部結合で相手が見つからなかった行です。
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;| name | order_id | amount |
|---|---|---|
| user_a | 101 | 3000 |
| user_a | 102 | 1500 |
| user_b | 103 | 2000 |
| user_c | NULL | NULL |
| user_d | NULL | NULL |
| user_e | 105 | NULL |
user_c と user_d の NULL は「注文がない」ことを表し、user_e の amount の NULL は「注文はあるが金額が未確定」を表します。同じ NULL でも意味が違います。両者を区別するには、NULL になりえない列(ここでは主キーの o.id)を見ます。
WHERE に条件を書くと内部結合と同じになる
この結果に対して、「2000 円以上の注文だけ」に絞る条件を WHERE に書くとします。
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount >= 2000
ORDER BY c.id, o.id;| name | order_id | amount |
|---|---|---|
| user_a | 101 | 3000 |
| user_b | 103 | 2000 |
LEFT JOIN で残したはずの user_c・user_d・user_e が消えました。相手がいない行は o.amount が NULL なので、NULL >= 2000 が UNKNOWN になり、WHERE で捨てられたためです。結果は INNER JOIN と同じになります。
顧客を全員残したまま、結合する注文だけを絞りたいなら、条件を ON に書きます。
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount >= 2000
ORDER BY c.id, o.id;| name | order_id | amount |
|---|---|---|
| user_a | 101 | 3000 |
| user_b | 103 | 2000 |
| user_c | NULL | NULL |
| user_d | NULL | NULL |
| user_e | NULL | NULL |
ON と WHERE の違いの詳しい説明は、SQL の JOIN の種類と違いにあります。
外部結合の後の集計
外部結合の結果を集計するときは、COUNT(*) と COUNT(列) の違いがそのまま効いてきます。
SELECT c.name,
COUNT(*) AS cnt_star,
COUNT(o.id) AS cnt_orders,
SUM(o.amount) AS sum_amount,
COALESCE(SUM(o.amount), 0) AS sum_or_zero
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;| name | cnt_star | cnt_orders | sum_amount | sum_or_zero |
|---|---|---|---|---|
| user_a | 2 | 2 | 4500 | 4500 |
| user_b | 1 | 1 | 2000 | 2000 |
| user_c | 1 | 0 | NULL | 0 |
| user_d | 1 | 0 | NULL | 0 |
| user_e | 1 | 1 | NULL | 0 |
注文のない user_c と user_d でも、COUNT(*) は 1 を返します。外部結合が「相手のいない行」を1行作っているからです。注文件数を数えるなら、右テーブルの主キーを指定した COUNT(o.id) を使います。
インデックスと IS NULL
「NULL はインデックスに入らないので、IS NULL の検索は遅い」と言われることがあります。これは製品によって事情が違います。確認できた範囲を整理します。
- PostgreSQL: ドキュメントに「インデックス列に対する
IS NULLやIS NOT NULLの条件は、B-tree インデックスで使える」と明記されている - MySQL: リファレンスに「
col_name IS NULLに対して、col_name = 定数と同じ最適化を行える。たとえばIS NULLで NULL を探すのにインデックスと範囲を使える」とある。NOT NULLと宣言された列へのIS NULL条件は、最適化で取り除かれる - SQLite: 手元で確認したところ、
IS NULLの検索にインデックスが使われた(下記) - Oracle: ドキュメントに「キー列がすべて NULL の行は、ビットマップインデックスなどの例外を除いてインデックスに含めない」とある。単一列の B-tree インデックスには NULL の行が入らないことになるため、
IS NULLの検索では別の手当てが必要になる
SQLite での確認結果です。
CREATE INDEX idx_customers_phone ON customers(phone);
EXPLAIN QUERY PLAN SELECT id FROM customers WHERE phone IS NULL;QUERY PLAN
`--SEARCH customers USING COVERING INDEX idx_customers_phone (phone=?)SEARCH と表示されており、インデックスを使った検索になっています。一方、列を関数で包むと、インデックスの全体を読む SCAN に変わりました。
EXPLAIN QUERY PLAN SELECT id FROM customers WHERE COALESCE(phone, '') = '';QUERY PLAN
`--SCAN customers USING COVERING INDEX idx_customers_phoneCOALESCE(phone, '') = '' は NULL と空文字をまとめて探せる便利な書き方ですが、列を関数で包むとインデックスによる絞り込みが効かなくなります。これは NULL に限らない一般的な性質です(データベースインデックス入門)。行数の多いテーブルでは、phone IS NULL OR phone = '' のように列をそのまま比較する形にするか、そもそも NULL と空文字が混在しないようにデータを整えるほうが有利です。
実際にインデックスが使われるかどうかは、NULL の行が全体に占める割合にも左右されます。大半の行が NULL なら、インデックスを使わずに全体を読むほうが速いと判断されることもあります。判断の仕組みはクエリ実行計画入門で説明しています。
Oracle では空文字が NULL として扱われる
冒頭で「空文字と NULL は別のもの」と書きましたが、Oracle は例外です。Oracle のドキュメントには「Oracle Database は、長さ 0 の文字値を NULL として扱う」と明記されています。同じ箇所に、この挙動は将来のリリースで変わる可能性があるので、空文字を NULL と同じものとして扱わないことを勧める、という注記もあります。
この記述から導かれる影響を挙げます(Oracle での実行はしていません)。
- 空文字を
INSERTした列が、IS NULLで一致する NOT NULLの列に空文字を入れられない列 = ''という条件は、NULL との比較になるため、どの行にも一致しない- 他の製品では区別できていた「未入力(NULL)」と「空で入力(空文字)」が、区別できなくなる
PostgreSQL 14.20 では '' IS NULL は false で、空文字と NULL は別のものです。Oracle から PostgreSQL や MySQL へ移行する場合、または同じアプリケーションを複数の製品で動かす場合は、空文字の扱いを最初に確認しておく必要があります。
設計上の指針
NULL の落とし穴の多くは、クエリの書き方より前の、テーブル設計の段階で減らせます。
NOT NULL を基本にする
PostgreSQL のドキュメントには「ほとんどのデータベース設計では、列の大多数は not null と指定されるべきである」という助言があります。列を定義するときは、まず NOT NULL を付け、NULL が本当に必要な列だけ外す、という順番で考えるのが安全です。
NOT NULL の列が増えるほど、3値論理を意識しなければならない場所が減ります。NOT IN も、否定形の条件も、AVG の分母も、NULL がなければ直感どおりに動きます。
NULL を許すなら、意味を1つに決める
NULL を許す列では、その NULL が何を表すのかを1つに決めておきます。「未入力」「該当なし」「不明」が同じ NULL に混ざっていると、集計のたびに解釈が必要になります。
- 意味が複数あるなら、状態を表す列を別に持つ(たとえば
phone_statusに「未確認」「なし」を入れる) - 「当てはまらない」ことが多い属性は、別テーブルに切り出すことも検討する(データベース正規化入門)
既定値で NULL を避けるかは、意味で判断する
数量やカウンタのように「何もなければ 0」が自然な列は、NOT NULL DEFAULT 0 にするのが素直です。一方で、「未計測」と「0」を区別する必要がある列を 0 で埋めてしまうと、平均値が実態より低く出ます。先ほどの points の例では、NULL を 0 とみなすかどうかで平均が 56.67 と 34.0 に分かれました。
文字列の列で NULL と空文字を混在させないことも重要です。「未入力は NULL に統一する」か「空文字に統一して NOT NULL にする」かを決め、アプリケーションの入力処理でそろえます。Oracle を使う可能性があるなら、空文字に意味を持たせる設計は避けます。
外部キーの NULL は「関連がない」を表す
referrer_id や customer_id のような外部キーの NULL は、「関連する行がない」ことを表す正当な使い方です。ただし、その列を NOT IN のサブクエリで使うと 0 行になる、という落とし穴は残ります。NULL を許す外部キーに対しては NOT EXISTS を使う、と決めておくと迷いません。
クエリを書くときの確認項目
- 条件に使う列は
NOT NULLか。NULL を許すなら、NULL の行をどう扱うかを決めたか - 否定形(
<>、NOT IN、NOT LIKE、NOT (...))の条件で、NULL の行が意図せず落ちていないか NOT INのサブクエリが NULL を返す可能性はないか- 合計や平均を返すクエリで、該当行がないときに NULL が返ってもよいか
- 並び順を NULL の既定位置に頼っていないか
早見表
| 場面 | NULL の扱い | 対処 |
|---|---|---|
列 = NULL、列 <> NULL | 常に UNKNOWN。1行も返らない | IS NULL、IS NOT NULL を使う |
WHERE・HAVING・ON | UNKNOWN の行は捨てられる | 必要なら OR 列 IS NULL を足す |
CHECK 制約 | UNKNOWN は通過する | NOT NULL を併記する |
NOT IN (..., NULL) | TRUE になる行がなくなり 0 行 | NOT EXISTS に書き換える |
IN (..., NULL) | 一致すれば TRUE。結果の行は変わらない | そのままでよい |
単純 CASE の WHEN NULL | 一致しない | CASE WHEN 列 IS NULL と書く |
COUNT(*) | NULL の行も数える | 行数を数えるときに使う |
COUNT(列)、SUM、AVG、MIN、MAX | NULL を飛ばす | 0 とみなすなら COALESCE(列, 0) |
空集合の SUM | NULL を返す | COALESCE(SUM(列), 0) |
| 算術演算 | 結果が NULL になる | COALESCE で既定値に置き換える |
| 文字列連結 | 製品と書き方で違う | COALESCE(列, '') してから連結する |
| NULL も含めた等価比較 | = では一致しない | IS NOT DISTINCT FROM(MySQL は <=>) |
GROUP BY、DISTINCT、UNION | NULL 同士は同じ扱い | そのままでよい |
ORDER BY | 既定の位置が製品で逆 | NULLS FIRST、NULLS LAST を明示する |
UNIQUE 制約 | 複数の NULL を許す製品が多い。SQL Server は1つだけ | PostgreSQL 15 以降は NULLS NOT DISTINCT |
| 外部結合 | 相手のいない行の列が NULL になる | 右テーブルの絞り込み条件は ON に書く |
| Oracle の空文字 | NULL として扱われる | 空文字に意味を持たせない |
まとめ
- NULL は値ではなく、値がないことを表す印。0 や空文字とは別のもの(Oracle は空文字を NULL として扱う)。
- NULL との比較は、TRUE でも FALSE でもなく UNKNOWN になる。
NULL = NULLも UNKNOWN。NULL かどうかはIS NULLとIS NOT NULLで調べる。 - SQL の条件式は TRUE・FALSE・UNKNOWN の3値で評価される。
NOT UNKNOWNは UNKNOWN、FALSE AND UNKNOWNは FALSE、TRUE OR UNKNOWNは TRUE。 WHEREは TRUE の行だけを残し、CHECK制約は FALSE の行だけを弾く。同じ UNKNOWN が、場面によって逆向きに扱われる。NOT INのリストやサブクエリに NULL が1つでもあると、TRUE になる行がなくなって 0 行になる。NOT EXISTSに書き換えるのが確実。- 集約関数は NULL を飛ばす。
COUNT(*)だけが行を数える。AVGの分母には NULL の行が入らず、空集合のSUMは NULL になる。 - 算術演算は NULL を伝播させる。文字列連結は、演算子か
CONCAT関数か、どの製品かで結果が違う。 - 置き換えには標準 SQL の
COALESCEを使う。NULLIFは空文字を NULL に寄せるときと、0 除算を避けるときに使える。 - NULL も含めて等しいかを比べるには
IS NOT DISTINCT FROM。MySQL では<=>が相当する。 GROUP BY・DISTINCT・UNIONでは NULL 同士が同じものとしてまとめられる。ORDER BYでの位置と、UNIQUE制約での扱いは製品で違う。- 外部結合は NULL を新しく作る。右テーブルへの条件を
WHEREに書くと、その NULL の行が落ちて内部結合と同じ結果になる。 - 列は
NOT NULLを基本にし、NULL を許す列では意味を1つに決める。
NULL を許す列に対して否定形の条件や NOT IN を書いたら、NULL を含む行を1行用意して結果を確かめてみてください。エラーにならず、行が静かに消えるだけなので、テストデータに NULL が入っていないと気付けません。
参考リンク
- Comparison Functions and Operators(PostgreSQL 公式)
- Logical Operators(PostgreSQL 公式)
- Conditional Expressions(PostgreSQL 公式)
- Subquery Expressions(PostgreSQL 公式)
- Aggregate Functions(PostgreSQL 公式)
- String Functions and Operators(PostgreSQL 公式)
- Sorting Rows (ORDER BY)(PostgreSQL 公式)
- Constraints(PostgreSQL 公式)
- Index Types(PostgreSQL 公式)
- PostgreSQL 15 リリースノート
- Working with NULL Values(MySQL 8.4 リファレンスマニュアル)
- Problems with NULL Values(MySQL 8.4 リファレンスマニュアル)
- Comparison Functions and Operators(MySQL 8.4 リファレンスマニュアル)
- Logical Operators(MySQL 8.4 リファレンスマニュアル)
- Flow Control Functions(MySQL 8.4 リファレンスマニュアル)
- String Functions and Operators(MySQL 8.4 リファレンスマニュアル)
- CREATE INDEX Statement(MySQL 8.4 リファレンスマニュアル)
- IS NULL Optimization(MySQL 8.4 リファレンスマニュアル)
- NULL Handling in SQLite Versus Other Database Engines(SQLite 公式)
- SQL Language Expressions(SQLite 公式)
- CREATE TABLE(SQLite 公式)
- SQLite Release 3.30.0
- SQLite Release 3.39.0
- IS NOT DISTINCT FROM / IS DISTINCT FROM(SQL Server 公式)
- Unique constraints and check constraints(SQL Server 公式)
- SELECT - ORDER BY clause(SQL Server 公式)
- CONCAT(SQL Server 公式)
- SET CONCAT_NULL_YIELDS_NULL(SQL Server 公式)
- ISNULL(SQL Server 公式)
- Nulls(Oracle Database 19c SQL Language Reference)
- Concatenation Operator(Oracle Database 19c SQL Language Reference)
- SELECT(Oracle Database 19c SQL Language Reference)
- NVL(Oracle Database 19c SQL Language Reference)
- Indexes and Index-Organized Tables(Oracle Database 19c Concepts)
関連記事
- SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
- SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け
- SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法
- データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
- データベース正規化 入門 - 第1〜第3正規形とBCNFを実例で理解する
- クエリ実行計画入門 - EXPLAINの読み方とプランナがJOINアルゴリズムを選ぶ理由
- 浮動小数点数 (IEEE 754) 入門 - なぜ 0.1 + 0.2 は 0.3 にならないのか


