COALESCE(a, b, c) は、引数のうち NULL でない最初のものを返します。COALESCE(nickname, name) なら、ニックネームがあればニックネームを、なければ名前を表示します。NULLIF(a, b) はその逆で、a が b と等しいときに NULL を返します。CASE WHEN condition THEN value ... ELSE value END は条件に応じて値を選びます。
Ben と Chen にはニックネームがないので、フルネームが表示されます。Ben は電話番号も no phone になります。
NULL をデフォルト値に置き換える
COALESCE は PostgreSQL 版の IFNULL、ISNULL、NVL です。NULL を含む計算はすべて NULL になるので、計算の前に NULL を 0 に変えるときに使います。0 行に対する集約に値を与えるのにも使います。
注文 2 は、COALESCE なしだと NULL、ありだと 80 になります。行がないときの合計は NULL ではなく 0 になります。
すべての引数は 1 つの型に解決できなければなりません。integer の列に COALESCE(quantity, 'none') を使うと、PostgreSQL が 'none' を integer として読もうとするので、"invalid input syntax for type integer" というエラーになります。先に列をキャストしてください: COALESCE(quantity::text, 'none')。キャストは CAST のページ で扱います。
NULLIF で値を NULL に変える
NULLIF(a, b) は a = b なら NULL を、そうでなければ a を返します。ゼロ除算を防いだり、空文字列を NULL に変えて COALESCE で置き換えられるようにしたりできます。
spring のクリック率は 5 です。summer は "division by zero" のエラーではなく NULL になり、空の owner は unassigned になります。
CASE WHEN で値を選ぶ
この形は条件を順に調べ、最初に一致したものを返します。どれにも一致しない行は ELSE の値になり、ELSE がなければ NULL になります。
ORDER BY の中の CASE が売り切れの商品を最後に回すので、mug が最後になります。1 つの式を値と比べる短い形もあります: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END。この形では NULL を判定できません。NULL = NULL は真にならないためです。その場合は完全な形で WHEN x IS NULL を使ってください。
条件ごとに数える
集約関数の中で CASE を使うと、1 回の走査でカテゴリごとに数えたり合計したりできます。PostgreSQL には FILTER 句もあり、同じことをより直接的に書けます。
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
どちらも支払い済みの注文だけを数えます。count() は、ほかの行で CASE が返す NULL を飛ばすためです。
GREATEST と LEAST は NULL を無視する
GREATEST(a, b, ...) と LEAST(a, b, ...) は、引数の最大値と最小値を返します。PostgreSQL では NULL の引数を飛ばし、すべての引数が NULL のときだけ NULL を返すので、GREATEST(1, NULL, 3) は 3 です。MySQL はどれか 1 つでも NULL なら NULL を返すので、クエリを移植するときに注意が必要です。詳しいルールは PostgreSQL の条件式のドキュメント を参照してください。
よくある質問
PostgreSQL で IFNULL、ISNULL、NVL にあたるものは何ですか?
COALESCE(value, fallback) です。PostgreSQL には IFNULL (MySQL)、ISNULL (SQL Server)、NVL (Oracle) の関数はありません。COALESCE は標準 SQL で、3 つ以上の引数も取れます。
PostgreSQL で NULL を 0 に置き換えるには?
列を囲みます: COALESCE(discount, 0)。行がないときの合計は NULL を返すので、COALESCE(SUM(amount), 0) と書きます。
COALESCE はすべての引数を評価しますか?
しません。引数を順に評価し、NULL でない最初の引数で止まるので、その行ではそれ以降の引数は計算されません。
WHERE 句で CASE を使えますか?
使えます。CASE 式は、WHERE、ORDER BY、集約関数の中など、値を書ける場所ならどこにでも書けます。ただ WHERE では、普通の AND/OR の条件のほうがたいてい読みやすいです。