SQLのNULLの扱い｜COALESCE・NULLIFと落とし穴12選

SQLのNULLは「値がない・不明」を表す特別な印で、= では比較できず、IS NULL で判定し、COALESCE で別の値に置き換えます。 NULL を含む比較の結果は TRUE でも FALSE でもない「不明（UNKNOWN）」になり、WHERE は TRUE の行だけを返します。これが NULL にまつわる意外な結果のほとんどの原因です。

この記事では、3値論理の真理値表と、NOT IN で0件になる・COUNT の数が合わない・ソート位置がDBで違うなどの落とし穴12個を、PostgreSQL 16.13 と MySQL 8.0.46 の両方で実行した結果で比べます。NVL・IFNULL・ISNULL・COALESCE の DB 別対応表、NULLIF で0除算を避けるコピペSQL、NULL を増やさない設計の指針までまとめました。

SQLのNULLの扱い 早見表

「やりたいこと → 書き方」をこの表で探してください。 どのDBでも使える標準SQLを先に書いています。

NULLとは何か：3値論理の真理値表

NULL との比較（= <> > など）や計算（+ など）の結果は NULL（不明）になります。 SQL の条件は TRUE・FALSE・UNKNOWN の3つの値をとり、これを3値論理と呼びます。

SELECT NULL = NULL AS eq, NULL <> NULL AS ne, NULL IS NULL AS isnull, 1 + NULL AS plus;
SELECT (NULL AND FALSE) AS n_and_f, (NULL AND TRUE) AS n_and_t,
       (NULL OR TRUE)   AS n_or_t,  (NULL OR FALSE) AS n_or_f, (NOT NULL) AS not_n;

実行結果（PostgreSQL 16.13。NULLを (null) と表示する設定）：

   eq   |   ne   | isnull |  plus
--------+--------+--------+--------
 (null) | (null) | t      | (null)

 n_and_f | n_and_t | n_or_t | n_or_f | not_n
---------+---------+--------+--------+--------
 f       | (null)  | t      | (null) | (null)

MySQL 8.0.46 でも同じ結果で、表示が NULL / 1 / 0 になるだけです。

覚え方は「UNKNOWN は TRUE か FALSE のどちらか分からない値」です。 UNKNOWN AND FALSE は、どちらでも FALSE になるので FALSE。UNKNOWN OR TRUE は、どちらでも TRUE になるので TRUE。それ以外は UNKNOWN のままです。

WHERE・ON・HAVING は TRUE の行だけを残し、UNKNOWN は FALSE と同じく捨てます。 一方、CHECK 制約は「FALSE でなければ通す」ので、NULL は CHECK を通過します。

検証に使うテーブル

以降の実行結果は、次のテーブルで PostgreSQL 16.13 と MySQL 8.0.46 の両方で確かめたものです。

CREATE TABLE members (id int PRIMARY KEY, name varchar(20), email varchar(50), points int, dept_id int);
INSERT INTO members VALUES
 (1, '佐藤', 'sato@example.com', 100, 10),
 (2, '鈴木', NULL,               NULL, 20),
 (3, '高橋', '',                   50, NULL),
 (4, '田中', NULL,               NULL, 10);

CREATE TABLE depts (id int PRIMARY KEY, name varchar(20));
INSERT INTO depts VALUES (10, '開発'), (20, '営業'), (30, '総務');

落とし穴1：= NULL は常に0件

WHERE email = NULL は、どの行でも UNKNOWN になるので1件も返りません。エラーにもならない点に気をつけます。

SELECT id, name FROM members WHERE email = NULL;             -- 直す前
SELECT id, name FROM members WHERE email IS NULL ORDER BY id; -- 直した後

 id | name
----+------
(0 rows)

 id | name
----+------
  2 | 鈴木
  4 | 田中

落とし穴2：<>（〜以外）でNULLの行が消える

「メールが sato@example.com 以外の人」を <> で探すと、NULL の2人は結果に入りません。

SELECT id, name FROM members WHERE email <> 'sato@example.com' ORDER BY id;

 id | name
----+------
  3 | 高橋

NULL の行も含めたいときは OR email IS NULL を足すか、email IS DISTINCT FROM 'sato@example.com'（MySQLは NOT (email <=> 'sato@example.com')）と書きます。

落とし穴3：NOT IN のサブクエリにNULLがあると0件

NOT IN のリストに1つでも NULL があると、結果は必ず0件になります。 「どの会員もいない部署」を探す例です。members.dept_id には NULL（高橋さん）が入っています。

-- 直す前：0件になる
SELECT id FROM depts WHERE id NOT IN (SELECT dept_id FROM members);
-- 直した後①：NOT EXISTS に書き換える（おすすめ）
SELECT d.id FROM depts d
WHERE NOT EXISTS (SELECT 1 FROM members m WHERE m.dept_id = d.id);
-- 直した後②：サブクエリからNULLを除く
SELECT id FROM depts WHERE id NOT IN (SELECT dept_id FROM members WHERE dept_id IS NOT NULL);

 id
----
(0 rows)

 id
----
 30

 id
----
 30

MySQL 8.0.46 も同じく、直す前は Empty set、直した後は 30 でした。

なぜ0件になるのか： 30 NOT IN (10, 20, NULL, 10) は 30 <> 10 AND 30 <> 20 AND 30 <> NULL AND ... と同じ意味です。30 <> NULL が UNKNOWN なので、全体は TRUE になれません。リテラルでも同じで、WHERE id NOT IN (20, NULL) は両DBとも0件でした。一方 IN (10, NULL) は id = 10 が TRUE になれば OR で TRUE になるので、10 が返ります。

落とし穴4：COUNT(*) と COUNT(列) で数が違う

COUNT(*) は行数、COUNT(列) はその列が NULL でない行数を数えます。

SELECT COUNT(*) AS cnt_all, COUNT(points) AS cnt_points, COUNT(email) AS cnt_email,
       COUNT(DISTINCT dept_id) AS distinct_depts
FROM members;

 cnt_all | cnt_points | cnt_email | distinct_depts
---------+------------+-----------+----------------
       4 |          2 |         2 |              2

COUNT(email) は空文字 '' の高橋さんを数えます（NULL ではないため）。COUNT(DISTINCT dept_id) も NULL を除くので 2 です。

LEFT JOIN で件数を数えるときは、右側の列を COUNT します。 COUNT(*) だと、相手がいない部署も1と数えられます。

SELECT d.name, COUNT(*) AS cnt_star, COUNT(m.id) AS cnt_id
FROM depts d LEFT JOIN members m ON m.dept_id = d.id
GROUP BY d.id, d.name ORDER BY d.id;

 name | cnt_star | cnt_id
------+----------+--------
 開発 |        2 |      2
 営業 |        1 |      1
 総務 |        1 |      0

落とし穴5：AVG は NULL を除いて平均する

AVG(points) は NULL の行を分母に入れません。 NULL を0点として平均したいなら COALESCE で置き換えます。

SELECT SUM(points) AS sum_p, AVG(points) AS avg_p, AVG(COALESCE(points, 0)) AS avg_p0, MAX(points) AS max_p
FROM members;

どちらが正しいかは業務の意味で決まります。 「未受験は平均に入れない」なら AVG(points)、「未獲得は0ポイント」なら AVG(COALESCE(points, 0)) です。

落とし穴6：SUM は全部NULL（または0件）だと NULL

SUM の対象がすべて NULL、または対象行が0件のとき、結果は 0 ではなく NULL です。 アプリで数値として受け取る場合は、NULLの考慮が必要になります。

SELECT SUM(points) AS sum_null, COALESCE(SUM(points), 0) AS sum0, COUNT(points) AS c
FROM members WHERE id IN (2, 4);

 sum_null | sum0 | c
----------+------+---
   (null) |    0 | 0

MySQL も NULL | 0 | 0 でした。集計結果を返すときは COALESCE(SUM(...), 0) を定番にしましょう。 なお COUNT は0件でも 0 を返します。

落とし穴7：GROUP BY では NULL が1つのグループになる

比較では NULL 同士は等しくありませんが、GROUP BY・DISTINCT では NULL はまとめて1グループになります。

SELECT dept_id, COUNT(*) FROM members GROUP BY dept_id ORDER BY dept_id;

グループのまとまり方は同じで、並ぶ位置が違います（次の落とし穴）。

落とし穴8：NULLのソート位置がDBで違う

PostgreSQL と Oracle は NULL を「最大の値」として扱い、MySQL と SQL Server は「最小の値」として扱います。

SELECT id, points FROM members ORDER BY points;
SELECT id, points FROM members ORDER BY points DESC;

どのDBでも同じ順にしたいときは、NULL の位置を明示します。

-- PostgreSQL / Oracle（標準SQL）
SELECT id, points FROM members ORDER BY points DESC NULLS LAST;
-- MySQL（NULLS FIRST/LAST 構文がないため、IS NULL の真偽で先に並べる）
SELECT id, points FROM members ORDER BY points IS NULL, points DESC;

どちらも結果は 1(100), 3(50), 2(NULL), 4(NULL) でした。ORDER BY points IS NULL は NULL でない行が 0、NULL の行が 1 になるので、NULL が後ろに行きます。

落とし穴9：UNIQUE制約でもNULLは複数入る

標準SQLでは NULL 同士は「等しい」と判定されないため、UNIQUE 列に NULL を何行でも入れられます。

CREATE TABLE u1 (id int, code varchar(10) UNIQUE);
INSERT INTO u1 VALUES (1, NULL), (2, NULL);   -- PostgreSQL・MySQL とも成功（2行入る）

PostgreSQL 15 以降は NULLS NOT DISTINCT で、NULL も1つまでに制限できます。

CREATE TABLE u2 (id int, code varchar(10) UNIQUE NULLS NOT DISTINCT);
INSERT INTO u2 VALUES (1, NULL);
INSERT INTO u2 VALUES (2, NULL);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "u2_code_key"
DETAIL:  Key (code)=(null) already exists.

落とし穴10：空文字 '' と NULL は別物（Oracleは同じ）

PostgreSQL・MySQL・SQL Server では ''（長さ0の文字列）と NULL は別の値です。

SELECT '' IS NULL AS empty_is_null, LENGTH('') AS len_empty, LENGTH(NULL) AS len_null;

 empty_is_null | len_empty | len_null
---------------+-----------+----------
 f             |         0 |   (null)

MySQL も 0 | 0 | NULL でした。このため WHERE email IS NULL では高橋さん（''）は見つからず、未登録をまとめて探すには email IS NULL OR email = '' が必要です（実行すると id 2, 3, 4 が返りました）。

Oracle は '' を NULL として扱います。 Oracle から移行するときは、'' を入れていた箇所が NULL として動くことを前提に条件を見直します。

落とし穴11：文字列連結でNULLが混ざると全体がNULL

|| による連結は、1つでも NULL があると結果全体が NULL になります。 関数によって扱いが違うので表で確認します。

SELECT id, name || '(' || email || ')' AS pipe,        -- PostgreSQL
           CONCAT(name, '(', email, ')') AS concat,
           CONCAT_WS(',', name, email) AS concat_ws
FROM members ORDER BY id;

（id=4 は id=2 と同じ結果。PostgreSQL は上のSQLそのまま、MySQL は CONCAT と CONCAT_WS を実行）

• PostgreSQL の CONCAT は NULL を無視し、MySQL の CONCAT は NULL を返します。 同じ関数名でも挙動が違います。
• CONCAT_WS は両DBとも NULL を飛ばします。 区切り付きの連結に便利です。
• MySQL の || は既定で論理 OR です。 SELECT 'a' || 'b' は 0 になり、警告 '|| as a synonym for OR' is deprecated が出ました（sql_mode に PIPES_AS_CONCAT を入れると連結になります）。

どのDBでも確実にするなら、COALESCE(email, '') で先に置き換えてから連結します。

落とし穴12：NULL 同士を「等しい」として比較したい

a = b は両方 NULL のとき UNKNOWN なので、JOIN や変更検知で NULL 同士が一致しません。 標準SQLの IS NOT DISTINCT FROM を使います。

-- メールが同じ会員のペア（NULL同士も同じとみなす）
SELECT m1.id, m2.id
FROM members m1 JOIN members m2
  ON m1.email IS NOT DISTINCT FROM m2.email AND m1.id < m2.id;

 id | id
----+----
  2 |  4

= で結合すると0件でした。MySQL は IS NOT DISTINCT FROM に対応しておらず（8.0.46 で ERROR 1064）、代わりに NULL 安全等価演算子 <=> を使います。

SELECT NULL <=> NULL AS a, 1 <=> NULL AS b, NULL = NULL AS c;   -- MySQL

+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 0 | NULL |
+---+---+------+

NVL・IFNULL・ISNULL・COALESCE・NULLIF のDB別対応表

迷ったら、どのDBでも使える COALESCE と NULLIF を選べば移植しやすくなります。

MySQL の IFNULL(email, '(未登録)') と ISNULL(email) は、id=2・4 でそれぞれ (未登録)・1 を返しました。

COALESCE の型に注意（PostgreSQL）

COALESCE の引数は同じ型にそろえる必要があります。 PostgreSQL では数値列に文字を渡すとエラーになります。

SELECT COALESCE(points, 'なし') FROM members;

ERROR:  invalid input syntax for type integer: "なし"

SELECT COALESCE(points::text, 'なし') AS p FROM members ORDER BY id;   -- 100, なし, 50, なし

MySQL は型を自動で変換し、COALESCE(points, 'なし') がそのまま 100, なし, 50, なし を返しました。移植性を考えるなら、明示的にキャストしておくと安全です。

コピペで使えるNULL対策SQL集

よく使う形をまとめました。 いずれも上のテーブルで PostgreSQL と MySQL の両方で実行した形です（|| を除く）。

-- 1. NULLを既定値に置き換えて表示
SELECT id, COALESCE(email, '(未登録)') AS email, COALESCE(points, 0) AS points FROM members;

-- 2. 空文字もNULLとみなして表示（id 2,3,4 が (未登録) になる）
SELECT id, COALESCE(NULLIF(email, ''), '(未登録)') AS email_disp FROM members;

-- 3. 0除算を避ける（分母が0ならNULL）
SELECT 100 / NULLIF(0, 0) AS safe_div;

-- 4. 構成比（合計が0やNULLでもエラーにならない）
SELECT id, points, 100.0 * points / NULLIF(SUM(points) OVER (), 0) AS pct FROM members;

-- 5. 集計が0件でも0を返す
SELECT COALESCE(SUM(points), 0) AS total FROM members WHERE dept_id = 30;

-- 6. 「〜以外」にNULLも含める
SELECT id FROM members WHERE email <> 'sato@example.com' OR email IS NULL;

-- 7. 相手がいない行を探す
SELECT d.id FROM depts d WHERE NOT EXISTS (SELECT 1 FROM members m WHERE m.dept_id = d.id);

-- 8. CASE で NULL を明示的に分類
SELECT id, CASE WHEN points IS NULL THEN 'unknown'
                WHEN points > 60   THEN 'high'
                ELSE 'low' END AS lvl
FROM members;

0除算の挙動もDBで違います。 PostgreSQL の SELECT 10 / 0 は ERROR: division by zero で止まり、MySQL は NULL を返して警告 1365 Division by 0 を出しました（既定の sql_mode の SELECT の場合）。NULLIF(分母, 0) を使えば、どちらでも NULL にそろいます。構成比（4）は PostgreSQL で 66.6666666666666667 / (null) / 33.3333333333333333 / (null)、MySQL で 66.66667 / NULL / 33.33333 / NULL でした。

GREATEST(1, NULL, 3) も違いがあり、PostgreSQL は NULL を無視して 3、MySQL は NULL を返しました。

NULLを増やさない設計の指針

NULL の扱いで迷う場面は、設計の段階でかなり減らせます。

レビュー用チェックリスト

よくある質問

Q. COALESCE と IFNULL・NVL・ISNULL はどれを使えばよいですか？ A. COALESCE をおすすめします。標準SQLなのでどのDBでも動き、3つ以上の引数も渡せます。IFNULL は MySQL、NVL は Oracle、ISNULL は SQL Server 固有で、MySQL の ISNULL(a) は判定関数なので意味が違います。

Q. NOT IN と NOT EXISTS はどちらを使うべきですか？ A. サブクエリの列に NULL が入る可能性があるなら NOT EXISTS です。NOT IN は NULL が1つあるだけで0件になります。列が NOT NULL なら結果は同じです。

Q. NULL と空文字、どちらで「未入力」を表すべきですか？ A. どちらか1つに決めることが大切です。多くの場合は NULL に統一し、入力時に NULLIF(trim(x), '') で空文字を NULL に変えます。Oracle では空文字が NULL になるので、移植を考えるなら NULL に統一しておくと扱いがそろいます。

Q. WHERE col IS NULL にインデックスは効きますか？ A. PostgreSQL・MySQL の B-tree インデックスは NULL も索引に含めるので、IS NULL の検索にも使えます。Oracle の単一列 B-tree は全列 NULL の行を索引に入れない点が違います。

まとめ

• NULLは「不明」。= ではなく IS NULL、NULL同士の一致は IS NOT DISTINCT FROM（MySQLは <=>） で比べます。
• 比較の結果は TRUE・FALSE・UNKNOWN の3値。WHERE は TRUE だけを残すので、<> や NOT IN で行が消えます。
• NOT IN はサブクエリにNULLがあると0件。NOT EXISTS に書き換えるのが基本です。
• COUNT(*) と COUNT(列)、AVG の分母、全NULLの SUM は NULL を押さえ、COALESCE(SUM(...), 0) を定番にします。
• ソート位置・CONCAT・GREATEST・0除算はDBで挙動が違うので、NULLS LAST・COALESCE・NULLIF で明示します。
• 置き換えは移植しやすい COALESCE と NULLIF を使い、NVL・IFNULL・ISNULL はDB固有と覚えます。
• 設計では NOT NULL を既定にし、NULLの意味を1つに決めると、落とし穴の多くを避けられます。

株式会社RJCでは、SQLの書き方やテーブル設計の工夫を、プロジェクトの中で仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。