COALESCEで0や空文字も置き換えられますか？

COALESCEが読み飛ばすのはNULLだけです。0や空文字はそのまま採用されます。空文字も未設定として扱いたい場合は、CASE WHEN nickname IS NULL OR nickname = '' THEN ... ENDのように、その条件を明示します。

候補がすべてNULLの場合は？

結果もNULLになります。必ず表示値が欲しい場合は、最後に0や'名前なし'のようなNULLでない既定値を置きます。

COALESCEを使うと元のデータも変わりますか？

このガイドのSELECTは取得する結果の値を変えるだけで、表のデータは更新しません。元のNULLと0を後から区別したい場合は、元の列も一緒に表示します。

← SQLガイド

SQLのNULLとCOALESCEとは？0・空文字との違いと平均への影響

NULLは、値がない、または分からないことを表します。COALESCEを使うと、NULLの代わりに表示する値を選べます。社員5人の賞与と名前を使い、未入力を補うと結果や平均がどう変わるかを見ていきましょう。

例で使う表

employeesの1行が社員1人分です。nameは氏名、nicknameは表示用の名前、bonusは賞与（万円）です。社員ID4は氏名も表示名も未入力です。NULLは未入力、0は数値のゼロ、''（空文字）は文字が0個の文字列を表します。表の空文字は見分けやすいように注記します。結果表の行の並びは一例で、ORDER BYなしでは保証されません。

employees：社員5人の名前と賞与
id（社員ID）	name（氏名）	nickname（表示名）	bonus（賞与・万円）
1	青木	アオ	120
2	北野	NULL	0
3	中央	''（空文字）	NULL
4	NULL	NULL	240
5	恵比寿	エビ	NULL

NULL・COALESCEの基本構文

sql

SELECT id, bonus, COALESCE(bonus, 0) AS displayed_bonus
FROM employees;

IS NULLで未入力を探す：NULLは0とは違う

NULLは値がない、または分からないことを表します。この表のbonusは賞与（万円）です。0は「賞与0万円と分かっている」、NULLは「金額が未入力」です。WHERE bonus IS NULLで未入力の社員を選ぶと、中央さんと恵比寿さんが残ります。0万円の北野さんは入りません。

sql

SELECT *
FROM employees
WHERE bonus IS NULL;

実行結果
id	name	nickname	bonus
3	中央	''（空文字）	NULL
5	恵比寿	エビ	NULL

IS NOT NULLなら0も入力済みに含まれる

入力済みの賞与を持つ社員はIS NOT NULLで選べます。120万円の青木さん、0万円の北野さん、240万円の社員ID4が残ります。金額が0かどうかではなく、NULLかどうかの判定です。NULLを探すときに= NULL、除くときに<> NULLとは書きません。

sql

SELECT *
FROM employees
WHERE bonus IS NOT NULL;

実行結果
id	name	nickname	bonus
1	青木	アオ	120
2	北野	NULL	0
4	NULL	NULL	240

空文字はNULLではない

空文字は文字が0個の文字列で、SQLではシングルクォート2つの''と書きます。PostgreSQLではNULLと別の値です。nickname = ''で検索すると中央さんだけが残り、nicknameがNULLの北野さんと社員ID4は入りません。表の「''（空文字）」は空文字の表示用の表記です。

sql

SELECT *
FROM employees
WHERE nickname = '';

実行結果
id	name	nickname	bonus
3	中央	''（空文字）	NULL

COALESCEで未入力の賞与だけを0表示にする

COALESCE(bonus, 0)は、bonusがNULLなら0、そうでなければbonusを返します。中央さんと恵比寿さんの表示値が0になり、もともと0の北野さんも0のままです。元のbonus列を書き換える処理ではありません。元の値と並べると、置き換えた箇所を確認できます。

sql

SELECT id, bonus, COALESCE(bonus, 0) AS displayed_bonus
FROM employees;

実行結果
id	bonus	displayed_bonus
1	120	120
2	0	0
3	NULL	0
4	240	240
5	NULL	0

複数候補から最初のNULLでない値を選ぶ

表示名をnickname、name、最後に「名前なし」の順で選びます。青木さんはnicknameのアオ、北野さんはnameの北野です。両方NULLの社員ID4は名前なしになります。中央さんの空文字はNULLではないので、そのまま採用されます。左から順に確認するため、候補の並び順が優先順位です。

sql

SELECT id, nickname, name,
  COALESCE(nickname, name, '名前なし') AS display_name
FROM employees;

実行結果
id	nickname	name	display_name
1	アオ	青木	アオ
2	NULL	北野	北野
3	''（空文字）	中央	''（空文字）
4	NULL	NULL	名前なし
5	エビ	恵比寿	エビ

AVGはNULLを除いて平均を計算する

まずCOALESCEを使わずに賞与の平均を求めます。入力されている値は120、0、240の3件なので、(120 + 0 + 240) ÷ 3で120万円です。0は計算に含めますが、NULLの2件は合計にも件数にも含めません。

sql

SELECT AVG(bonus) AS average_bonus
FROM employees;

実行結果
average_bonus
120

AVGの内側でCOALESCEを使うと平均が変わる

未入力を0万円として5人全員の平均を求める場合は、AVGの内側にCOALESCEを書きます。120、0、0、240、0の5件となり、360 ÷ 5で72万円です。これは表示を整えるだけではなく、平均の意味を変えています。不明な賞与を0と見なしてよい場合にだけ使います。

sql

SELECT AVG(COALESCE(bonus, 0)) AS average_bonus
FROM employees;

実行結果
average_bonus
72

AVGの外側なら平均結果がNULLのときだけ補う

COALESCE(AVG(bonus), 0)は、先にNULLを除いた平均を計算し、その結果がNULLのときだけ0にします。この表ではAVG(bonus)が120なので、結果は120のままです。内側に置いた例の72とは違います。対象の賞与がすべてNULL、または対象行が0件なら、平均がNULLになるため0を返します。

sql

SELECT COALESCE(AVG(bonus), 0) AS average_bonus
FROM employees;

実行結果
average_bonus
120

実際に1問解いてみる

IS NULL

賞与（bonus）が未入力の社員を取得してください。0万円と入力済みの社員は含めません。全列を取得し、行順は問いません。

⌘ / Ctrl + Enter

テーブル

読み込み中…

実行結果

ヒント

未入力かどうかはIS NULLで判定します。

解説・解答例

中央さんと恵比寿さんのbonusはNULLです。北野さんは数値の0が入力されているので対象外です。

sql

SELECT *
FROM employees
WHERE bonus IS NULL;

NULL・COALESCEの本格演習へ →

NULL・COALESCEについてよくある質問

COALESCEで0や空文字も置き換えられますか？: COALESCEが読み飛ばすのはNULLだけです。0や空文字はそのまま採用されます。空文字も未設定として扱いたい場合は、CASE WHEN nickname IS NULL OR nickname = '' THEN ... ENDのように、その条件を明示します。
候補がすべてNULLの場合は？: 結果もNULLになります。必ず表示値が欲しい場合は、最後に0や'名前なし'のようなNULLでない既定値を置きます。
COALESCEを使うと元のデータも変わりますか？: このガイドのSELECTは取得する結果の値を変えるだけで、表のデータは更新しません。元のNULLと0を後から区別したい場合は、元の列も一緒に表示します。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。

PostgreSQL公式：比較とNULL判定 — 比較とNULL判定の仕様
PostgreSQL公式：COALESCE — COALESCEの仕様
PostgreSQL公式：集約関数 — 集約関数の仕様