SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け

SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け

作成日:
読了:約69分
更新日:

当サイトは 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 の結果
00 という数値がある偽
''(空文字)長さ 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 テーブル:

idnamephonepointsreferrer_id
1user_a090-0000-0001120NULL
2user_bNULL01
3user_c(空文字)NULL1
4user_dNULL50NULL
5user_e090-0000-0005NULL2

orders テーブル:

idcustomer_idamountcoupon
10113000WELCOME
10211500NULL
10322000NULL
104NULL800WELCOME
1055NULLNULL

ポイントは、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

電話番号が未登録の顧客を探すつもりで、次のように書いたとします。

= NULL で探す(意図どおりに動かない)
SELECT id, name FROM customers WHERE phone = NULL;

結果は 0 行です。エラーにはならず、ただ1行も返りません。<> で「NULL ではない行」を探しても同じく 0 行になります。

不等号で NULL と比較しても 0 行
SELECT id, name FROM customers WHERE phone <> NULL;

理由は、NULL との比較の結果が、TRUE でも FALSE でもなく NULL(UNKNOWN)になるためです。比較の結果そのものを表示すると確認できます。

NULL との比較結果を表示する
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;
eqnenull_eq_nullnull_ne_nulllt
NULLNULLNULLNULLNULL

NULL = NULL ですら TRUE になりません。「不明な値」と「不明な値」が等しいかどうかは、やはり不明だからです。PostgreSQL のドキュメントも、NULL は不明な値を表すので、2つの不明な値が等しいかどうかはわからない、という趣旨で expression = NULL と書いてはいけない理由を説明しています。

NULL かどうかを調べるには、専用の述語 IS NULL と IS NOT NULL を使います。

IS NULL で探す
SELECT id, name, phone FROM customers WHERE phone IS NULL;
idnamephone
2user_bNULL
4user_dNULL
IS NOT NULL で探す
SELECT id, name, phone FROM customers WHERE phone IS NOT NULL;
idnamephone
1user_a090-0000-0001
3user_c(空文字)
5user_e090-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

aNOT a
TRUEFALSE
FALSETRUE
UNKNOWNUNKNOWN

不明なものを否定しても、不明なままです。これが後で出てくる落とし穴の多くの原因になります。

AND

ANDTRUEFALSEUNKNOWN
TRUETRUEFALSEUNKNOWN
FALSEFALSEFALSEFALSE
UNKNOWNUNKNOWNFALSEUNKNOWN

OR

ORTRUEFALSEUNKNOWN
TRUETRUETRUETRUE
FALSETRUEFALSEUNKNOWN
UNKNOWNTRUEUNKNOWNUNKNOWN

表を丸暗記する必要はありません。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 として表示します。

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_falsenull_and_truenull_or_truenull_or_falsenot_null
0NULL1NULLNULL

真理値表のとおりです。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 以上の顧客を取り出します。

points が 50 以上
SELECT id, name, points FROM customers WHERE points >= 50;
idnamepoints
1user_a120
4user_d50

では、その条件を NOT で反転すると「残り全員」が返るでしょうか。

条件を NOT で反転する
SELECT id, name, points FROM customers WHERE NOT (points >= 50);
idnamepoints
2user_b0

返ってきたのは user_b だけです。5人のうち、points が NULL の user_c と user_e はどちらの結果にも出てきません。NULL >= 50 は UNKNOWN で、NOT UNKNOWN も UNKNOWN だからです。

2値論理なら「A または A でない」は必ず真ですが、SQL では成り立ちません。

「50 以上、または 50 未満」でも全員にならない
SELECT id, name, points FROM customers WHERE points >= 50 OR points < 50;
idnamepoints
1user_a120
2user_b0
4user_d50

「〜以外」を取り出す条件でも同じことが起きます。「ポイントが 0 ではない顧客」のつもりで <> を使うと、NULL の行は含まれません。

points が 0 ではない顧客
SELECT id, name, points FROM customers WHERE points <> 0;
idnamepoints
1user_a120
4user_d50

NULL の行も含めたいなら、明示的に OR points IS NULL を足します。

NULL の行も含める
SELECT id, name, points FROM customers WHERE points <> 0 OR points IS NULL;
idnamepoints
1user_a120
3user_cNULL
4user_d50
5user_eNULL

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 は決して一致しません。

単純 CASE の 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;
namesimple_casesearched_case
user_aotherother
user_botherother
user_cothernull
user_dotherother
user_eothernull

NULL を判定したいときは、検索形の CASE WHEN 列 IS NULL THEN ... を使います。

CHECK 制約は UNKNOWN を通す

WHERE が「TRUE だけを通す」のに対して、CHECK 制約は逆に 「FALSE だけを弾く」という規則で動きます。UNKNOWN は通過します。

CHECK (price > 0) の列に NULL を入れる
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 の行は入り、負の値だけが弾かれます。

3つめの INSERT のエラー(SQLite)
CHECK constraint failed: price > 0
idprice
1100
2NULL

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 制約を併記します。

NOT NULL と CHECK を併記する
CREATE TABLE items2 (
  id    INTEGER PRIMARY KEY,
  price INTEGER NOT NULL CHECK (price > 0)
);
 
INSERT INTO items2 VALUES (2, NULL);
エラー(SQLite)
NOT NULL constraint failed: items2.price
場面TRUEFALSEUNKNOWN
WHERE・HAVING・ON行を残す行を捨てる行を捨てる
CHECK 制約通す弾く通す

同じ UNKNOWN でも、場面によって「FALSE 寄り」にも「TRUE 寄り」にも扱われる点が、3値論理のわかりにくさの正体です。

NOT IN に NULL が混ざると 0 行になる

NULL の落とし穴のなかで、実害がいちばん大きいのがこれです。「注文が1件もない顧客」を NOT IN で取り出します。

注文のない顧客を 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 なので、重複を除いて展開すると次のようになります。

IN の展開
id IN (1, 2, 5, NULL)
-- 次の式と同じ
id = 1 OR id = 2 OR id = 5 OR id = NULL

NOT IN はその否定です。OR の否定は、各項を否定して AND でつないだものになります(ド・モルガンの法則)。

NOT IN の展開
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)で評価してみます。

id が 3 のときの評価
3 <> 1   AND  3 <> 2  AND  3 <> 5  AND  3 <> NULL
TRUE     AND  TRUE    AND  TRUE    AND  UNKNOWN
= UNKNOWN

TRUE AND UNKNOWN は UNKNOWN なので、WHERE で捨てられます。注文のある user_a(id が 1)は 1 <> 1 が FALSE なので、全体が FALSE になります。つまり、リストに NULL が1つでもあると、NOT IN の結果は FALSE か UNKNOWN にしかならず、TRUE になる行が存在しなくなります。

値を直接並べた場合も同じです。

値リストに NULL がある場合
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_resultnot_in_resultin_hitnot_in_hit
NULLNULL10

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 になって捨てられるだけなので、結果の行は変わりません。

IN は NULL が混ざっても結果が変わらない
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
name
user_a
user_b
user_e

回避方法1: NOT EXISTS を使う

いちばん確実なのは、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 を除く方法もあります。

サブクエリに IS NOT 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

外部結合で相手が見つからなかった行を拾う書き方もあります。

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(*) で、これだけは行そのものを数えます。

集約関数と NULL
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_rowscnt_pointssum_pointsavg_pointsmin_pointsmax_points
5317056.6666666666666640120
  • 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 へ置き換える必要があります。

NULL を飛ばす平均と、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_nullavg_null_as_zerosum_div_rows
56.66666666666666434.034.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;
cntsum_amountsum_or_zero
0NULL0

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 を数えない

COUNT(DISTINCT 列)
SELECT COUNT(DISTINCT coupon) AS distinct_coupons,
       COUNT(coupon)          AS cnt_coupon
FROM orders;
distinct_couponscnt_coupon
12

クーポンの種類は WELCOME の1種類として数えられ、NULL は種類に含まれません。

ウィンドウ関数として使う場合も、集約関数が NULL を飛ばす点は同じです(SQL ウィンドウ関数入門)。

算術と文字列連結での NULL 伝播

NULL を含む計算の結果は、原則として NULL になります。「不明な値に 10 を足した結果」は、やはり不明だからです。

NULL を含む算術と文字列連結
SELECT name,
       points,
       points + 10            AS plus10,
       points * 0             AS times0,
       name || ':' || phone   AS label
FROM customers;
namepointsplus10times0label
user_a1201300user_a:090-0000-0001
user_b0100NULL
user_cNULLNULLNULLuser_c:
user_d50600NULL
user_eNULLNULLNULLuser_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 関数で結果が違う製品もあります。

連結演算子と CONCAT 関数(SQLite 3.54.0 と PostgreSQL 14.20 で同じ結果)
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 の関数で、引数はいくつでも並べられます。

COALESCE で既定値に置き換える
SELECT name,
       COALESCE(phone, '未登録')    AS phone_label,
       COALESCE(points, 0) + 10     AS plus10
FROM customers;
namephone_labelplus10
user_a090-0000-0001130
user_b未登録10
user_c(空文字)10
user_d未登録60
user_e090-0000-000510

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 に寄せるときに使えます。

NULLIF で空文字を NULL にそろえる
SELECT name,
       phone,
       NULLIF(phone, '')                        AS phone_or_null,
       COALESCE(NULLIF(phone, ''), '未登録')    AS phone_label
FROM customers;
namephonephone_or_nullphone_label
user_a090-0000-0001090-0000-0001090-0000-0001
user_bNULLNULL未登録
user_c(空文字)NULL未登録
user_dNULLNULL未登録
user_e090-0000-0005090-0000-0005090-0000-0005

COALESCE(NULLIF(列, ''), 既定値) の組み合わせで、NULL と空文字の両方を既定値に置き換えられます。

NULLIF で 0 除算を避ける

NULLIF のもうひとつの定番が、割り算の分母です。分母が 0 のときに NULL へ変えておけば、割り算の結果が NULL になり、エラーを避けられます。

分母を NULLIF で包む
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・Oracle2個以上標準 SQL。迷ったらこれを使う
IFNULL(a, b)MySQL・SQLite2個a が NULL でなければ a、NULL なら b
NVL(a, b)Oracle2個a が NULL なら b、そうでなければ a
ISNULL(a, b)SQL Server2個戻り値の型は第1引数と同じ
NULLIF(a, b)PostgreSQL・MySQL・SQLite2個等しければ 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 同士が一致しないので、素直に書くと長くなります。

= だけで NULL も含めて比較する(長い)
WHERE (a = b) OR (a IS NULL AND b IS NULL)

標準 SQL には、このための述語 IS DISTINCT FROM と IS NOT DISTINCT FROM があります。結果は必ず TRUE か FALSE になります。

aba = ba IS NOT DISTINCT FROM ba IS DISTINCT FROM b
11TRUETRUEFALSE
12FALSEFALSETRUE
1NULLUNKNOWNFALSETRUE
NULLNULLUNKNOWNTRUEFALSE

サンプルデータで、「紹介者が同じ顧客の組」を探します。まず = で結合した場合です。

= で紹介者が同じ組を探す
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;
namea_refotherb_ref
user_b1user_c1

IS NOT DISTINCT FROM に変えると、紹介者がいない(NULL)同士の組も一致します。

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;
namea_refotherb_ref
user_aNULLuser_dNULL
user_b1user_c1

製品ごとの対応状況

製品NULL 安全な比較確認した内容
PostgreSQLIS DISTINCT FROM / IS NOT DISTINCT FROM公式ドキュメントに記載。14.20 で実行して確認
SQLiteIS DISTINCT FROM / IS NOT DISTINCT FROM、独自の IS / IS NOT3.39.0(2022-06-25)で追加。IS と IS NOT は SQLite の拡張
MySQL<=> 演算子NULL 安全な等価比較。標準 SQL の IS NOT DISTINCT FROM と等価と明記
SQL ServerIS [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 同士は同じものとして扱われます。比較演算子とは別の規則が使われるためです。

GROUP BY では NULL が1つのグループになる
SELECT referrer_id, COUNT(*) AS cnt
FROM customers
GROUP BY referrer_id;
referrer_idcnt
NULL2
12
21

紹介者のいない user_a と user_d が、NULL という1つのグループにまとまっています。

DISTINCT でも NULL は1つにまとまる
SELECT DISTINCT coupon FROM orders;
coupon
WELCOME
NULL
UNION でも 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 での実行結果です。

昇順(SQLite)
SELECT name, points FROM customers ORDER BY points;
namepoints
user_cNULL
user_eNULL
user_b0
user_d50
user_a120

SQLite は NULL を「他のどの値よりも小さい」ものとして並べるので、昇順では先頭、降順では末尾に来ます。PostgreSQL 14.20 で同じ趣旨のクエリを実行すると、昇順では NULL が末尾、降順では先頭に来ました。

製品昇順(ASC)での NULL降順(DESC)での NULLNULLS 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 を書いておくと、既定に依存しなくなります。

昇順で NULL を末尾に置く
SELECT name, points FROM customers ORDER BY points ASC NULLS LAST;
namepoints
user_b0
user_d50
user_a120
user_cNULL
user_eNULL

この構文が使えない製品では、「NULL かどうか」を先頭の並べ替えキーにする方法があります。points IS NULL は NULL の行で真、それ以外で偽になるので、偽の行が先に並びます。

構文に頼らずに NULL を末尾に置く(SQLite で実行)
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 を複数入れられるかどうかも製品で分かれます。

UNIQUE 列に NULL を2行入れる
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行目だけが弾かれます。

4行目のエラー(SQLite)
UNIQUE constraint failed: accounts.email
idemail
1NULL
2NULL
3a@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 で変更できるようになった、とあります。

NULLS NOT DISTINCT(PostgreSQL 15 以降。未実行)
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 があります。外部結合で相手が見つからなかった行です。

LEFT JOIN で相手のいない行に 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;
nameorder_idamount
user_a1013000
user_a1021500
user_b1032000
user_cNULLNULL
user_dNULLNULL
user_e105NULL

user_c と user_d の NULL は「注文がない」ことを表し、user_e の amount の NULL は「注文はあるが金額が未確定」を表します。同じ NULL でも意味が違います。両者を区別するには、NULL になりえない列(ここでは主キーの o.id)を見ます。

WHERE に条件を書くと内部結合と同じになる

この結果に対して、「2000 円以上の注文だけ」に絞る条件を WHERE に書くとします。

右テーブルの条件を 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;
nameorder_idamount
user_a1013000
user_b1032000

LEFT JOIN で残したはずの user_c・user_d・user_e が消えました。相手がいない行は o.amount が NULL なので、NULL >= 2000 が UNKNOWN になり、WHERE で捨てられたためです。結果は INNER JOIN と同じになります。

顧客を全員残したまま、結合する注文だけを絞りたいなら、条件を ON に書きます。

右テーブルの条件を 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;
nameorder_idamount
user_a1013000
user_b1032000
user_cNULLNULL
user_dNULLNULL
user_eNULLNULL

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;
namecnt_starcnt_orderssum_amountsum_or_zero
user_a2245004500
user_b1120002000
user_c10NULL0
user_d10NULL0
user_e11NULL0

注文のない 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 での確認結果です。

IS NULL でインデックスが使われるか(SQLite 3.54.0)
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 に変わりました。

列を COALESCE で包んだ場合
EXPLAIN QUERY PLAN SELECT id FROM customers WHERE COALESCE(phone, '') = '';
実行計画
QUERY PLAN
`--SCAN customers USING COVERING INDEX idx_customers_phone

COALESCE(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・ONUNKNOWN の行は捨てられる必要なら 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、MAXNULL を飛ばす0 とみなすなら COALESCE(列, 0)
空集合の SUMNULL を返すCOALESCE(SUM(列), 0)
算術演算結果が NULL になるCOALESCE で既定値に置き換える
文字列連結製品と書き方で違うCOALESCE(列, '') してから連結する
NULL も含めた等価比較= では一致しないIS NOT DISTINCT FROM(MySQL は <=>)
GROUP BY、DISTINCT、UNIONNULL 同士は同じ扱いそのままでよい
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 が入っていないと気付けません。

参考リンク

関連記事

SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法

SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法

約60分

SQL の WITH 句(CTE: 共通テーブル式)と再帰 CTE(WITH RECURSIVE)を実行結果つきで整理します。サブクエリ・ビュー・一時テーブルとの違い、複数 CTE の連結、段階的な集計、再帰 CTE の構造と評価の進み方、連番・日付の生成、組織図のような階層データの展開と経路・深さの出し方、UNION と UNION ALL の差、無限ループの防ぎ方、マテリアライズとインライン化、データ変更を伴う CTE、DB ごとの対応差までを解説します。

SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け

SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け

約41分

SQL のウィンドウ関数を実行結果つきで整理します。OVER と PARTITION BY の意味、ROW_NUMBER・RANK・DENSE_RANK の違い、LAG / LEAD、累計と移動平均、ROWS と RANGE の差、LAST_VALUE の落とし穴、グループごとの上位N件、WHERE で使えない理由、DBごとの対応差までを解説します。

SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する

SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する

約36分

SQL の JOIN の種類と違いを、同じ小さなサンプルデータに対する実行結果を並べて整理します。INNER JOIN・LEFT JOIN・RIGHT JOIN・FULL OUTER JOIN・CROSS JOIN・自己結合の結果の違い、ON と USING と NATURAL JOIN、LEFT JOIN で条件を ON に書く場合と WHERE に書く場合の差、NULL が一致しない理由、1対多の結合で行が増えて SUM が膨らむ問題と対処、EXISTS / NOT EXISTS によるセミジョイン・アンチジョインと NOT IN の落とし穴、LATERAL、PostgreSQL・MySQL・SQLite の対応差までを、各公式ドキュメントを一次ソースにまとめます。

楽天市場のおすすめPR / 楽天アフィリエイト