SQLのNULLと三値論理：IS NULL・COALESCEの使い分け

SQLを書いていると、NULLが絡んだとたんにクエリが期待どおりに動かないという経験は多いはずです。NULLは「値がない」のではなく「不明」を意味するため、通常の比較演算子が使えず、集計やJOINにも独特の影響を与えます。この記事では、三値論理の基礎からIS NULL・COALESCE・NULLIF・IFNULLの具体的な使い分けまで、MySQL 8.0の実例を交えて整理します。

このシナリオで考える

以降の例では、電話番号や部署がNULLになり得る社員テーブルを使います。まずテーブルとサンプルデータを準備します。

CREATE TABLE employees (
  id           INT PRIMARY KEY,
  name         VARCHAR(50)    NOT NULL,
  department   VARCHAR(50),
  phone_number VARCHAR(20),
  salary       DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, '田中 太郎', '営業部', '090-1111-2222', 350000),
(2, '鈴木 花子', '開発部', NULL,            420000),
(3, '佐藤 健一', NULL,     '090-3333-4444', 280000),
(4, '山田 優子', '営業部', NULL,            310000),
(5, '伊藤 誠',   NULL,     NULL,            NULL);

department・phone_number・salary がNULLになるデータを用意しました。以降のSQL例はすべてこのテーブルで動きます。

NULLと三値論理：比較演算子が使えない理由

SQLの条件評価は「TRUE / FALSE / UNKNOWN」の三値論理（three-valued logic）で動きます。NULLが絡む比較は必ずUNKNOWNになり、WHERE句でUNKNOWNの行は除外されます。

-- NG：= NULL は常にUNKNOWNになり、1行も返らない
SELECT * FROM employees WHERE department = NULL;
-- → 0件（期待どおりに動かない！）

-- OK：IS NULL で判定する
SELECT * FROM employees WHERE department IS NULL;
-- → 佐藤 健一、伊藤 誠 の2行が返る

比較演算子 = や <> はNULLに対してUNKNOWNを返すため、結果として行が除外されます。NULLどうしを比較しても同様です。

式	評価結果
NULL = NULL	UNKNOWN
NULL <> NULL	UNKNOWN
NULL IS NULL	TRUE
NULL IS NOT NULL	FALSE
NULL = 0	UNKNOWN
NULL = ''	UNKNOWN

「NULLはどんな値とも等しくない」ではなく「比較できない（不明）」という点が重要です。WHERE句に書けるのはTRUEになる行だけなので、UNKNOWNの行は常に除外されます。

IS NULL / IS NOT NULL の使い方

NULLかどうかの判定には IS NULL を、NULLでないことの確認には IS NOT NULL を使います。

-- 電話番号が未登録の社員を取得
SELECT name, department
FROM employees
WHERE phone_number IS NULL;

+----------+------------+
| name     | department |
+----------+------------+
| 鈴木 花子 | 開発部     |
| 山田 優子 | 営業部     |
| 伊藤 誠   | NULL       |
+----------+------------+

MySQL 8.0 ではNULL安全等価演算子（<=>）も使えます。col <=> NULL は col IS NULL と同義で、NULLどうしの比較でもTRUEを返します。ON句で使うと便利な場面があります。

-- IS NULL と同じ結果になる
SELECT * FROM employees WHERE phone_number <=> NULL;

COALESCE・IFNULL・NULLIFの使い分け

NULLを別の値に置き換えたり、特定の条件でNULLを作り出したりする関数が3つあります。それぞれ役割が異なるため、表で整理してから実例を見ていきます。

関数	引数	返す値	主な用途
COALESCE(v1, v2, …)	可変長	最初のNULLでない値	NULLを任意の値で置換（汎用）
IFNULL(expr, alt)	2つ	NULL→alt、それ以外→expr	COALESCEの2引数版（MySQL固有）
NULLIF(e1, e2)	2つ	e1=e2→NULL、それ以外→e1	特定値をNULLに変換

COALESCE — 最初のNULLでない値を返す

COALESCE は引数を先頭から走査し、最初にNULLでない値を返します。すべてNULLのときはNULLを返します。引数の数に制限がなく、複数のフォールバックを連鎖させられます。

-- phone_number がNULLなら '未登録' を返す
SELECT name, COALESCE(phone_number, '未登録') AS phone
FROM employees;

+----------+---------------+
| name     | phone         |
+----------+---------------+
| 田中 太郎 | 090-1111-2222 |
| 鈴木 花子 | 未登録        |
| 佐藤 健一 | 090-3333-4444 |
| 山田 優子 | 未登録        |
| 伊藤 誠   | 未登録        |
+----------+---------------+

-- 電話番号 → 部署 → 'N/A' の優先順でフォールバック
SELECT name, COALESCE(phone_number, department, 'N/A') AS contact
FROM employees;

IFNULL — MySQL固有の2引数版

IFNULL(expr, alt) は expr がNULLなら alt を返します。COALESCE(expr, alt) と同義ですが、MySQL・MariaDB固有の関数です。PostgreSQLなど他のDBMSに移植する可能性があるコードでは COALESCE を使うほうが安全です。

SELECT name, IFNULL(department, '所属なし') AS dept
FROM employees;

NULLIF — 特定の値をNULLに変換する

NULLIF(e1, e2) は2つの値が等しければNULLを、違えば e1 を返します。ゼロ除算の回避など「特定の値をNULLに変えてから処理したい」場面で使います。

-- salary が 0 の行でゼロ除算が起きないように NULLIF で防ぐ
SELECT name, 1000000 / NULLIF(salary, 0) AS ratio
FROM employees;

集計関数とJOINでのNULLの挙動

集計関数はNULLを無視する

COUNT・SUM・AVG などの集計関数はNULLを無視します。例外は COUNT(*) で、NULLを含む行も数えます。この違いを意識しないと件数の不一致で混乱します。

SELECT
  COUNT(*)      AS total_rows,   -- 全行（NULL含む）
  COUNT(salary) AS salary_count, -- salary が非NULLの行のみ
  AVG(salary)   AS avg_salary,   -- NULL除外の4件で平均
  SUM(salary)   AS sum_salary
FROM employees;

+------------+--------------+------------+-------------+
| total_rows | salary_count | avg_salary | sum_salary  |
+------------+--------------+------------+-------------+
|          5 |            4 |  340000.00 |  1360000.00 |
+------------+--------------+------------+-------------+

伊藤 誠の salary はNULLなので除外され、AVGは4件分の平均になります。集計結果に0ではなくNULLが返ってきたときは、対象列がすべてNULLになっていないか確認しましょう。

LEFT JOINとNULLの関係

LEFT JOIN を使うと、右テーブルに対応する行がないとき、右側のカラムはすべてNULLになります。「社員がいない部署」をCOUNTしたい場合は、LEFT JOINと COUNT(e.id) を組み合わせます。

CREATE TABLE departments (
  dept_name VARCHAR(50) PRIMARY KEY,
  budget    INT
);
INSERT INTO departments VALUES
('営業部', 5000000),
('開発部', 8000000),
('総務部', 3000000);

-- 社員がいない総務部もカウントに含める
SELECT d.dept_name, COUNT(e.id) AS member_count
FROM departments d
LEFT JOIN employees e ON d.dept_name = e.department
GROUP BY d.dept_name;

+-----------+--------------+
| dept_name | member_count |
+-----------+--------------+
| 営業部    |            2 |
| 開発部    |            1 |
| 総務部    |            0 |
+-----------+--------------+

COUNT(*) を使うと「結合で発生したNULL行」も1件として数えてしまうため、COUNT(e.id) のように特定の列を指定するのがポイントです。

よくある落とし穴 / 注意点

• NOT IN とNULLの組み合わせ: サブクエリの結果に1件でもNULLが含まれると、NOT INの全行がUNKNOWNになり0件を返します。NOT EXISTSを使うか、サブクエリ側に WHERE col IS NOT NULL を追加しましょう。
• NULLの連鎖: 文字列連結でNULLが混じると結果全体がNULLになります（CONCAT('hello', NULL) → NULL）。COALESCEで前処理するか、NULLをスキップする CONCAT_WS を使います。
• ORDER BY とNULLの順序: デフォルトではNULLは最小値扱いのため、ASCなら先頭、DESCなら末尾に並びます。順序を制御するには ORDER BY (column IS NULL), column ASC のように書きます。
• UNIQUE制約とNULL: NULLはどのNULLとも等しくないため、UNIQUE制約があるカラムでも複数のNULLを挿入できます。論理削除の deleted_at にUNIQUEを使う設計では想定外の重複を許してしまう場合があります。

まとめ

NULLは「不明な値」として三値論理の中に位置づけられ、通常の比較演算子では扱えません。判定には IS NULL / IS NOT NULL を使い、置換にはCOALESCE・IFNULL、特定値をNULLに変換するにはNULLIFを使います。集計関数がNULLを無視すること、LEFT JOINでNULLが生じることを理解しておくと、クエリの挙動をより正確に読み解けます。

参考リンク

• MySQL公式リファレンス: Problems with NULL Values
• MySQL公式リファレンス: COALESCE()
• MySQL公式リファレンス: NULLIF()

アイキャッチ画像: Photo by Fernando Hernandez on Unsplash