コマンド道場

NULL を扱う関数（COALESCE / NULLIF）の使い方

NULL だけが別の値に置き換わる

shain社員

busho_id
NULL
NULL
1
2
2
2
…

COALESCE で埋めた列を足す埋めた結果

busho_idhokan
NULL-1
NULL-1
11
22
22
22
…

COALESCE(busho_id, -1) AS hokan

  • 青い列＝ 式から作った列。元の表には無い列です
  • 社員 18 行 → 埋めた結果 18 行（行数は変わりません。減らすのは WHERE の仕事です）

先頭 2 行が所属未設定の社員です。そこだけ -1 に変わり、値が入っている行はそのままです。行数は 18 行のまま変わりません。

COALESCE は NULL を別の値に置き換え、NULLIF は特定の値を NULL にします。 向きが逆の 2 つです。どちらも行を減らさず、値だけを差し替えます。⚠️ 埋めれば安心ではありません。 集計の答えが変わるので、埋めてよい場面かどうかを先に決めてください。

未確認の製品があります そのまま使える: PostgreSQL / MySQL / MariaDB / SQLite / SQL Server / Oracle Database

未確認: IBM Db2

製品ごとの対応表（根拠つき）を見る

このページの実行結果と図は、すべて本物のデータベースに同じ SQL を流して得たものです。手で書いた「たぶんこうなる」ではありません。

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

結論: COALESCE(a, b, c, …) は左から見て、最初に NULL でないものを返します。

引数は 2 つでなくても構いません。「第 1 希望が無ければ第 2 希望、それも無ければ既定値」という順で並べられます。全部 NULL なら結果も NULL です。

⚠️ 返る型は引数から決まります。 数値の列に文字列の既定値を混ぜると、製品によってはエラーになります。既定値は同じ型で用意してください。

⚠️ 見た目を整えるための置き換えは表示のときだけにしてください。次の節から見るように、置き換えたまま集計すると答えが変わります。

1 行ずつ、何をしているか
  1. SELECT s.name,社員の名前
  2. COALESCE(b.busho_name, '所属なし')★NULL なら右の値を使う
  3. AS busho作った列に名前を付ける
  4. FROM shain s全社員が対象
  5. LEFT JOIN busho b所属が無い人も残す
  6. ON b.busho_id = s.busho_idつながらない行は NULL になる
  7. ORDER BY s.shain_id;結果の並べ方

外部結合で相手がいない行は NULL になります。その NULL を COALESCE が拾って「所属なし」に置き換えています。

最初の NULL でない値が返る
SELECT COALESCE(NULL, NULL, 3, 4) AS r;
r
3

（1 行）

3 が返ります。左から見て最初に見つかった NULL でない値で、そのあとの 4 は見られません。

所属が無い人に既定の表示を与える
SELECT s.name,
       COALESCE(b.busho_name, '所属なし') AS busho
FROM shain s
  LEFT JOIN busho b ON b.busho_id = s.busho_id
ORDER BY s.shain_id;
namebusho
鈴木経営
佐藤開発
田中開発
中村開発
小林開発
加藤基盤
吉田基盤
山田営業
高橋営業
伊藤営業
松本営業
井上マーケ
木村マーケ
清水管理
斎藤管理
山本管理
渡辺所属なし
大野所属なし

（18 行）

18 行です。末尾の渡辺と大野が「所属なし」になっています。行が減っていないことも確かめてください。

02NULLIF は「この値なら NULL にする」

結論: NULLIF(a, b) は a と b が等しいときだけ NULL を返します。 等しくなければ a をそのまま返します。

COALESCE と向きが逆です。あちらは NULL を消し、こちらはわざと NULL を作ります。

いちばんの使いどころはゼロ除算を避けることです。割る数を NULLIF(x, 0) にしておくと、0 のときだけ分母が NULL になり、エラーではなく NULL が返ります。

結果を 0 として扱いたいなら、さらに COALESCE で包みます。2 つを組み合わせるのが定番の形です。

⚠️ 「空文字を NULL として扱いたい」場面でも使えます（NULLIF(col, '')）。ただし空文字と NULL を同じものとして扱う製品もあるので、そこは対応表で確かめてください。

指定した値だけ NULL になる
SELECT name, salary, NULLIF(salary, 530000) AS r
FROM shain
WHERE salary IN (530000, 520000)
ORDER BY shain_id;
namesalaryr
田中520000520000
中村530000NULL
伊藤530000NULL

（3 行）

中村と伊藤（530000）だけが NULL になり、田中（520000）はそのまま残ります。

そのまま割るとエラー
SELECT 100 / 0 AS r;
ERROR: division by zero

「division by zero」で落ちます。1 行でも 0 が混じっていれば、問い合わせ全体が失敗します。

NULLIF で避けて、COALESCE で既定値にする
SELECT 100 / NULLIF(0, 0) AS wari,
       COALESCE(100 / NULLIF(0, 0), 0) AS kitei;
warikitei
NULL0

（1 行）

左は NULL、右は 0 です。分母が 0 のときだけ NULL になり、エラーにはなりません。それを COALESCE が 0 に置き換えています。

03⚠️ よくある間違い:NULL は = で比べられない

結論: = NULL は決して真になりません。 NULL かどうかを調べるには IS NULL を使います。

NULL は「値が無い」という状態で、特定の値ではありません。「無い」と「無い」が等しいかは決められないので、結果は真でも偽でもない unknown になります。WHERE は真の行だけを残すので、1 行も返りません。

エラーにならないのが厄介です。0 行返ってきたとき、条件が厳しすぎたのか、書き方が間違っているのかは見分けが付きません。

⚠️ ただし集合演算と DISTINCT では NULL 同士が「同じ」扱いになります。比較の = とは別のルールで動いているので、混同しないでください。

= NULL では見つからない
SELECT count(*) AS n FROM shain WHERE busho_id = NULL;
n
0

（1 行）

0 です。所属未設定の社員は 2 人いるのに、1 人も数えられていません。エラーは出ません。

IS NULL なら数えられる
SELECT count(*) AS n FROM shain WHERE busho_id IS NULL;
n
2

（1 行）

2 です。渡辺と大野の 2 人が正しく数えられました。

04⚠️ よくある間違い:埋めると集計の答えが変わる

結論: 集約関数は NULL を無視します。 COALESCE で埋めてから集計すると、分母が変わって答えが変わります。

count(*) は行を数えるので 18 です。count(列) はその列が NULL でない行だけを数えるので 16 になります。平均も同じで、NULL の行は分母にも分子にも入りません。

ここで「見た目を整えるつもりで」0 を埋めると、0 という値が 2 つ増えたことになり、平均が下がります。表示のための置き換えと、計算のための置き換えはまったく別の判断です。

迷ったら埋めないでください。 NULL のまま集計するのが既定の挙動で、たいていはそれが正しい答えです。

NULL は集計から外れている
SELECT count(*) AS zen_gyou,
       count(busho_id) AS atai_ari,
       round(avg(busho_id), 4) AS heikin
FROM shain;
zen_gyouatai_ariheikin
18163.5625

（1 行）

18 行のうち値があるのは 16 行で、平均もその 16 行だけで計算されています。差の 2 が所属未設定の人数です。

0 で埋めると平均が変わる
SELECT round(avg(busho_id), 4) AS sonomama,
       round(avg(COALESCE(busho_id, 0)), 4) AS zero_ume
FROM shain;
sonomamazero_ume
3.56253.1667

（1 行）

3.5625 が 3.1667 に下がりました。0 が 2 つ足されて分母が 18 になったためです。どちらが正しいかは、埋めてよい場面かどうかで決まります。

05⚠️ よくある間違い:連結すると NULL が伝染する

結論: 文字列の連結に NULL が 1 つ混じると、結果全体が NULL になります。

一部が欠けているだけなのに、組み立てた文字列がまるごと消えます。名前も住所も揃っているのに 1 項目 NULL だったせいで、行全体が空白に見える、という形で表に出ます。

防ぎ方は単純で、連結に入れる前に COALESCE で埋めることです。

⚠️ 製品によっては連結時に NULL を空文字として扱うものもあります。「手元では出た」を根拠にしないでください。連結する値が NULL になりうるなら、明示的に埋めるのが安全です。

1 つ NULL があるだけで全体が消える
SELECT s.name,
       s.name || ' / ' || b.busho_name AS r
FROM shain s
  LEFT JOIN busho b ON b.busho_id = s.busho_id
WHERE s.busho_id IS NULL
ORDER BY s.shain_id;
namer
渡辺NULL
大野NULL

（2 行）

名前は入っているのに、連結した結果は 2 行とも NULL です。部署名が NULL だったせいで全体が NULL になりました。

⚠️ この書き方は SQL Server では使えません（|| による文字列連結）。製品ごとの対応表

連結する前に埋める
SELECT s.name,
       s.name || ' / ' || COALESCE(b.busho_name, '所属なし') AS r
FROM shain s
  LEFT JOIN busho b ON b.busho_id = s.busho_id
WHERE s.busho_id IS NULL
ORDER BY s.shain_id;
namer
渡辺渡辺 / 所属なし
大野大野 / 所属なし

（2 行）

「渡辺 / 所属なし」と出るようになりました。連結の中で COALESCE を使えば、欠けている項目だけを補えます。

⚠️ この書き方は SQL Server では使えません（|| による文字列連結）。製品ごとの対応表

自分で打ってみる

このページの例と同じデータが入った本物のデータベースを、この場（あなたのブラウザの中）で起動できます。打った SQL がサーバーへ送られることはありません。

押すと初回だけ約 9MB を読み込みます（2 回目以降はキャッシュされます）。

解いてみる

読んだだけでは書けるようになりません。実行結果で採点します。

ここで出てくる関数を引く

関連するトピック

根拠（一次情報）