PostgreSQL：NULLと条件分岐の復習

今回はNULLと条件分岐を復習します。COALESCE・NULLIF・CASEを使い、取得したデータの意味を整理します。

所要時間は15分ほどです。
それでは、さっそく始めていきましょう！

復習する記述

NULLは値がないことを表し、ゼロや空文字列とは異なります。

記述	役割
IS NULL	NULLかどうかを判定する
IS NOT NULL	NULLではないかを判定する
COALESCE	最初のNULLではない値を返す
NULLIF	2つの値が等しければNULL、それ以外は最初の値を返す
CASE	条件によって返す値を変える

今回はmobility.poisを使います。1行が1施設を表し、poi_idは施設ID、poi_nameは施設名、closed_dateは閉業日を表す日付型の列です。

演習①：NULLの代わりに文字列を表示する

施設ID1・39・41について、閉業日がNULLなら「日付なし」、日付があればその日付を文字列で表示する課題です。

最初はCASEで書き始め、ENDを書かずにASを置いたため、構文エラーになりました。並び順の列名にも入力ミスがありました。課題の指定はCOALESCEと::TEXTだったため、説明を受けて次のSQLに修正しました。

SELECT
    poi_id,
    poi_name,
    closed_date,
    COALESCE(closed_date::TEXT, '日付なし') AS closed_date_label
FROM mobility.pois
WHERE poi_id IN (1, 39, 41)
ORDER BY poi_id;

修正後の実行結果は次のとおりです。

poi_id	closed_date	closed_date_label
1	NULL（画面では空欄）	日付なし
39	2020-02-24	2020-02-24
41	2023-09-26	2023-09-26

元の結果にはpoi_nameも表示されています。この表では日付に関する列を抜粋しています。

COALESCEは何を左から確認するのか

ここで「最初のNULLではない値を返すのに、なぜNULLの場合の表示を指定できるのか」という疑問がありました。

COALESCEが確認するのは、列に並んだ別々の行ではなく、その行について括弧内に指定した候補です。

COALESCE(closed_date::TEXT, '日付なし')

最初の候補は閉業日を文字列にした値、次の候補は文字列の「日付なし」です。閉業日がNULLなら、最初の候補もNULLなので次へ進みます。「日付なし」はNULLではない文字列なので、その文字列が返ります。

日付があれば最初の候補が返ります。::TEXTは日付を文字列型に変換し、代わりに表示する文字列と型をそろえるために使っています。

また、閉業日がNULLというだけで営業中とは断定できません。この確認には「できない」と回答しました。表示用のラベルと、施設の営業状態は別の情報です。

演習②：NULLIFとCOALESCEを組み合わせる

次の式の結果と理由を考えました。

COALESCE(NULLIF('Tokyo', ''), '未登録')

回答は「Tokyo。2つの要素が同じならNULLを返し、一緒でなければ最初の要素を返す」でした。

内側ではTokyoという文字列と空文字列が異なるため、NULLIFはTokyoを返します。外側のCOALESCEも、その値がNULLではないためTokyoを返します。

これは式を読んで説明した演習で、追加の実行画像を確認したものではありません。

演習③：CASEで日付を分類する

次はCASEを使って、同じ3施設を「日付なし」「2023年より前」「2023年以降」に分けました。

SELECT
    poi_id,
    closed_date,
    CASE
        WHEN closed_date IS NULL THEN '日付なし'
        WHEN closed_date < DATE '2023-01-01' THEN '2023年より前'
        ELSE '2023年以降'
    END AS closed_date_group
FROM mobility.pois
WHERE poi_id IN (1, 39, 41)
ORDER BY poi_id;

実行結果は次の3行でした。

poi_id	closed_date	closed_date_group
1	NULL（画面では空欄）	日付なし
39	2020-02-24	2023年より前
41	2023-09-26	2023年以降

今回はENDも含めて書けました。CASEは最初にTRUEとなったWHENの値を返します。NULLを最初に分けることで、日付のない行を期間の分類に混ぜずに表示できました。

最後の確認：NOTを付ければNULLも取得されるか

次の条件について考えました。

WHERE NOT (closed_date = DATE '2023-09-26')

回答は「取得されない。閉業日が2023年9月26日ではなく、かつNULLではない行が取得される。NULLは比較できないから」という趣旨でした。

より正確には、NULLと日付の等値比較の結果はUNKNOWN（不明）です。エラーになるという意味ではありません。

closed_date	等値比較の結果	NOTを付けた結果	取得されるか
2023-09-26	TRUE	FALSE	されない
指定日以外の日付	FALSE	TRUE	される
NULL	UNKNOWN	UNKNOWN	されない

WHEREで取得されるのはTRUEの行だけです。UNKNOWNはNOTを付けてもUNKNOWNなので、NULLの行は取得されません。この演習では条件の意味を回答し、新しい取得結果は確認していません。

学習記録

最初のSQLではCASEのENDと列名の修正が必要でした。また、COALESCEが行ごとに括弧内の候補を確認することを説明し直しました。

その後は、COALESCEのSQLを修正して実行し、NULLIFの結果を理由付きで回答できました。CASEの分類も実行でき、最後にNULLがNOT付きの条件でも取得されないことを確認しました。

入力途中のSQLを取り消す方法も復習しました。Control + Cで入力を取り消せます。これは保存済みのデータを削除する操作ではありません。

まとめ

NULLの判定、代わりの表示、条件ごとの分類は、それぞれの式の役割を分けて考えます。式の結果と保存済みの値も区別します。

今回のSELECTは結果を表示する操作であり、施設データの更新は行っていません。