PostgreSQL COALESCE 関数の使い方【徹底解説】

PostgreSQL COALESCE 関数の使い方【徹底解説】

1. はじめに：なぜ NULL の扱いが重要なのか

リレーショナルデータベースにおいて、データの「存在しない」状態や「不明」な状態を表すために NULL という特別な値が使用されます。NULL は数値の 0 や空文字列 ('') とは全く異なる概念です。NULL は「値そのものが無い」ことを意味します。

例えば、顧客の電話番号を管理するテーブルで、電話番号が登録されていない顧客がいる場合、その顧客の電話番号カラムは NULL になります。これは電話番号が 0 や空文字列であることとは異なります。

しかし、この NULL の扱いは多くの開発者やデータベース利用者にとって混乱の元となり、様々な問題を引き起こします。

• 計算結果への影響: NULL を含む算術演算の結果は NULL になります (5 + NULL は NULL)。
• 比較結果への影響: NULL と他の値を比較する (= や < など) と、結果は真 (True) でも偽 (False) でもなく、「不明 (Unknown)」となります。WHERE 句では「不明」は偽として扱われるため、意図しない行が結果から漏れることがあります。
• 集計関数への影響: AVG, SUM, COUNT(column_name) といった集計関数は、デフォルトでは NULL を無視します。これは便利な場合もありますが、期待する合計値や平均値が得られない原因となることもあります。
• 表示の問題: データベースから取得したデータをアプリケーションやレポートに表示する際、NULL がそのまま表示されると見栄えが悪かったり、ユーザーに誤解を与えたりする可能性があります。例えば、未入力の金額が NULL と表示されるよりは 0 と表示された方が自然な場合があります。

これらの NULL に起因する問題を解決し、より柔軟かつ安全にデータを扱うために、PostgreSQLはいくつかの便利な関数や構文を提供しています。その中でも最も頻繁に使用され、非常に強力なツールとなるのが COALESCE 関数です。

本記事では、PostgreSQLの COALESCE 関数に焦点を当て、その基本的な使い方から、様々な応用例、関連する機能との比較、パフォーマンスに関する考慮事項、そして実践的なヒントまでを網羅的に解説します。この記事を読めば、あなたは COALESCE 関数を自在に使いこなし、NULL の問題に煩わされることなく、より堅牢でユーザーフレンドリーなシステムを構築できるようになるでしょう。

2. NULL とは何か：データベースにおける特別な値

COALESCE 関数を深く理解するためには、まずデータベースにおける NULL の性質を正しく理解することが不可欠です。前述の通り、NULL は「値がない」状態を表す特別なマーカーです。これは、0 という数値や '' という空文字列とは明確に区別されます。

• NULL vs 0: 数値型カラムで NULL は「値が登録されていない」ことを意味します。0 は「ゼロという値が登録されている」ことを意味します。例えば、商品の在庫数が NULL の場合は「在庫データ自体が無い（確認中など）」、0 の場合は「在庫がゼロである」という明確な違いがあります。
• NULL vs '' (空文字列): 文字列型カラムで NULL は「文字列データが登録されていない」ことを意味します。'' は「長さゼロの文字列が登録されている」ことを意味します。例えば、メールアドレスが NULL の場合は「メールアドレスが登録されていない」、'' の場合は「空のメールアドレスが登録されている」となります。多くのアプリケーションでは空文字列を許容しないように設計しますが、データベースの観点ではこれらは異なる値です。

NULL を含む演算と比較

NULL の厄介な性質は、演算と比較の結果にあります。

• 算術演算: NULL を含むほとんどの算術演算の結果は NULL になります。
  sql
SELECT 10 + NULL; -- 結果: NULL
SELECT 5 * (NULL + 2); -- 結果: NULL
• 文字列連結: 文字列連結演算子 || を使う場合、PostgreSQLではデフォルトで NULL と他の文字列を連結すると NULL になります。（ただし、一部のデータベースシステムでは NULL が無視されて連結される場合もあります。PostgreSQLでは明確に NULL になります。）
  sql
SELECT 'Hello' || NULL || ' World'; -- 結果: NULL
SELECT 'User: ' || NULL; -- 結果: NULL
• 比較: NULL と他の値を比較する際に = や < などの標準的な比較演算子を使うと、結果は真 (True) でも偽 (False) でもなく、「不明 (Unknown)」となります。
  sql
SELECT NULL = 1; -- 結果: NULL (不明)
SELECT NULL <> 1; -- 結果: NULL (不明)
SELECT NULL = NULL; -- 結果: NULL (不明) - ここが重要！ NULL はそれ自身とも等しくないと判定されます。
SELECT 5 > NULL; -- 結果: NULL (不明)
  この「不明」という結果は、特に WHERE 句で問題を引き起こします。WHERE 句は条件が真 (True) となる行のみを選択します。「不明」は偽 (False) と同様に扱われるため、WHERE column = NULL という条件では どの行も 選択されません。また、WHERE column <> NULL という条件でも どの行も 選択されません。
  NULL であるかどうかの判定には、専用の演算子 IS NULL または IS NOT NULL を使用する必要があります。
  sql
SELECT column_name FROM your_table WHERE column_name IS NULL; -- column_name が NULL の行を選択
SELECT column_name FROM your_table WHERE column_name IS NOT NULL; -- column_name が NULL でない行を選択
  また、PostgreSQLでは IS DISTINCT FROM および IS NOT DISTINCT FROM という演算子も提供しており、これらは NULL を含む値の等価性比較を真または偽で判定できます。
  sql
SELECT NULL IS DISTINCT FROM NULL; -- 結果: False (NULL は NULL と「区別できない」)
SELECT 1 IS DISTINCT FROM NULL; -- 結果: True (1 は NULL と「区別できる」)
SELECT NULL IS NOT DISTINCT FROM NULL; -- 結果: True (NULL は NULL と「区別できないわけではない」＝区別できない)
SELECT 1 IS NOT DISTINCT FROM NULL; -- 結果: False (1 は NULL と「区別できないわけではない」＝区別できる)
IS NOT DISTINCT FROM は、a = b OR (a IS NULL AND b IS NULL) とほぼ等価で、特に NULL を含みうるカラムを比較する際に便利です。

集計関数と NULL

多くの標準的な集計関数（SUM, AVG, COUNT(column_name), MAX, MIN）は、引数として渡された NULL 値を無視します。

“`sql
CREATE TEMPORARY TABLE sales (amount DECIMAL);
INSERT INTO sales VALUES (100), (200), (NULL), (300);

SELECT SUM(amount) FROM sales; — 結果: 600 (NULL が無視される)
SELECT AVG(amount) FROM sales; — 結果: 200.0000000000000000 (NULL が無視されるため、(100+200+300)/3)
SELECT COUNT(amount) FROM sales; — 結果: 3 (amount カラムの NULL でない行数をカウント)
SELECT COUNT(*) FROM sales; — 結果: 4 (テーブルの全行数をカウント)
“`

SUM や AVG の計算において、NULL を 0 として扱いたい場合など、このデフォルトの振る舞いが意図しない結果につながることがあります。

これらの NULL の性質を理解した上で、次に COALESCE 関数がどのようにこれらの問題に対処するのかを見ていきましょう。

3. COALESCE 関数とは：NULL 脱出の救世主

COALESCE 関数は、リストされた複数の引数を順番に評価し、最初に見つかった NULL でない値を返す関数です。もし全ての引数が NULL であった場合は、最終的に NULL を返します。

これは、NULL の可能性のある値に対して、代替となるデフォルト値や次の候補値を指定したい場合に非常に役立ちます。

構文

sql
COALESCE(value1, value2, value3, ...)

• value1, value2, value3, … は、評価される任意の式（カラム名、リテラル値、他の関数呼び出しなど）です。
• 引数は2つ以上必要です。

動作原理

COALESCE(expr1, expr2, expr3, ...) が呼び出されると、以下のステップで処理が行われます。

1. expr1 を評価します。
2. もし expr1 が NULL でなければ、expr1 の値を関数の結果として直ちに返し、処理を終了します。
3. もし expr1 が NULL であれば、次に expr2 を評価します。
4. もし expr2 が NULL でなければ、expr2 の値を関数の結果として直ちに返し、処理を終了します。
5. 以降、同様にリストされた引数を順番に評価していきます。
6. もしリストされた全ての引数が NULL であった場合、COALESCE 関数は NULL を返します。

重要な点は、COALESCE 関数は引数を左から右へと順番に評価し、NULL でない値を見つけた時点でそれ以降の引数は評価しないということです。これは、後の引数にコストのかかる関数呼び出しや式が含まれている場合に、パフォーマンス上の利点となることがあります。

戻り値のデータ型

COALESCE 関数の戻り値のデータ型は、引数として与えられた式のデータ型から決定されます。PostgreSQLは、引数のデータ型の中から互換性のあるデータ型を見つけ、より上位の型（精度の高い型や、より広範囲の値を表現できる型）に暗黙的に型変換しようと試みます。

例えば、数値型とテキスト型を混ぜて指定した場合、互換性がないためエラーになるか、意図しない型変換が発生する可能性があります。通常は、引数のデータ型を揃えるか、必要に応じて明示的な型キャスト (::type_name) を行うことが推奨されます。

“`sql
— 数値型とテキスト型を混ぜる例（エラーまたは警告、あるいは意図しない結果になる可能性がある）
— SELECT COALESCE(amount, ‘N/A’); — amount が数値型の場合、通常エラー

— データ型を揃えるか、キャストする
SELECT COALESCE(amount::TEXT, ‘N/A’); — amount が数値型でもテキスト型に変換して ‘N/A’ と組み合わせる
“`

PostgreSQLの型解決システムは複雑ですが、基本的には引数のデータ型の中で最も具体的な型（特定の値型）があればそれを優先し、なければより一般的な型（数値型全体、文字列型全体など）を考慮します。互換性のない型が混在する場合はエラーとなります。安全のため、特に異なるカテゴリの型を扱う場合は型キャストを検討しましょう。

4. COALESCE 関数の基本的な使い方

COALESCE 関数の最も一般的で基本的な使い方は、NULL 値を特定のデフォルト値に置き換えることです。これは、単一のカラムに対しても、複数のカラムに対しても適用できます。

例1: 単一カラムの NULL をデフォルト値に置き換える

商品の価格リストがあるとします。一部の商品にはまだ価格が設定されておらず、価格カラムが NULL になっています。これらの NULL を表示する際に、0 や「価格未定」といった文字列に置き換えたい場合に使用できます。

“`sql
CREATE TEMPORARY TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, ‘Laptop’, 1200.00),
(2, ‘Keyboard’, 75.50),
(3, ‘Mouse’, NULL), — 価格未定
(4, ‘Monitor’, 300.00),
(5, ‘Webcam’, NULL); — 価格未定
“`

NULL を 0 に置き換える:

価格を数値として扱いたい場合（例えば計算に含める場合など）、NULL を 0 に置き換えるのが一般的です。

sql
SELECT
product_name,
COALESCE(price, 0.00) AS display_price
FROM products;

product_name	display_price
Laptop	1200.00
Keyboard	75.50
Mouse	0.00
Monitor	300.00
Webcam	0.00

この例では、price が NULL の場合に 0.00 が返されます。0.00 は DECIMAL 型のリテラルとして扱われるため、戻り値の型は DECIMAL になります。

NULL を特定の文字列に置き換える:

表示目的で、NULL を「価格未定」といった文字列に置き換えたい場合です。この場合、元の数値型カラムを文字列型にキャストする必要があります。

sql
SELECT
product_name,
COALESCE(price::TEXT, '価格未定') AS display_price
FROM products;

product_name	display_price
Laptop	1200.00
Keyboard	75.50
Mouse	価格未定
Monitor	300.00
Webcam	価格未定

ここでは price::TEXT とすることで、price カラムの値をテキスト型に明示的にキャストしています。これにより、DECIMAL 型の値と '価格未定' というテキスト型リテラルが混在しても、全体としてテキスト型の結果が得られます。

例2: 複数のカラムの中から最初の NULL でない値を選択する

顧客の連絡先情報として、自宅電話、携帯電話、会社電話の3つのカラムがあるテーブルを考えます。顧客に連絡する際に、これらのうち最初に登録されている（NULL でない）電話番号を使用したいとします。

“`sql
CREATE TEMPORARY TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
home_phone VARCHAR(20),
mobile_phone VARCHAR(20),
work_phone VARCHAR(20)
);

INSERT INTO customers (customer_id, name, home_phone, mobile_phone, work_phone) VALUES
(101, ‘Alice’, ’03-1111-2222′, ‘090-1111-2222’, NULL),
(102, ‘Bob’, NULL, ‘090-3333-4444′, ’03-3333-4444’),
(103, ‘Charlie’, NULL, NULL, ’03-5555-6666′),
(104, ‘David’, NULL, NULL, NULL), — どの電話番号も登録されていない
(105, ‘Eve’, ’06-7777-8888′, NULL, NULL);
“`

顧客の優先連絡先番号を取得するには、COALESCE を使用して、home_phone、mobile_phone、work_phone の順に評価し、最初に見つかった NULL でない値を返させます。もし全てのカラムが NULL であれば、「連絡先なし」といったデフォルト文字列を返すようにします。

sql
SELECT
name,
COALESCE(home_phone, mobile_phone, work_phone, '連絡先なし') AS primary_contact
FROM customers;

name	primary_contact
Alice	03-1111-2222
Bob	090-3333-4444
Charlie	03-5555-6666
David	連絡先なし
Eve	06-7777-8888

この例では、COALESCE 関数が引数を左から順番にチェックしていることがよくわかります。Aliceはhome_phoneがNULLでないため、それが返されます。Bobはhome_phoneがNULLなのでmobile_phoneがチェックされ、それがNULLでないため返されます。Charlieはhome_phoneもmobile_phoneもNULLなのでwork_phoneがチェックされ、それが返されます。Davidは全ての電話番号カラムがNULLなので、最後の引数である'連絡先なし'が返されます。

このように、複数の代替候補がある場合にも COALESCE は非常に効果的です。

5. COALESCE 関数の応用例

COALESCE 関数の使い道は、単に NULL をデフォルト値に置き換えるだけにとどまりません。様々な状況で柔軟に応用することで、クエリの可読性を高めたり、複雑なロジックを簡潔に表現したり、計算や表示を適切に制御したりすることができます。

5.1. 表示目的での利用

データベースの値は内部的な表現であり、ユーザーへの表示には加工が必要な場合があります。特に NULL はそのまま表示すると不親切です。

前述の「価格未定」の例も表示目的の利用でしたが、ここでは別の例を見てみましょう。商品の説明文やオプション情報が NULL である場合、空ではなく「説明なし」や「オプションなし」と表示したい場合です。

“`sql
CREATE TEMPORARY TABLE products_ext (
product_id INT REFERENCES products(product_id),
description TEXT,
options TEXT
);

INSERT INTO products_ext (product_id, description, options) VALUES
(1, ‘高性能ラップトップPC’, ‘カラー: シルバー, ブラック’),
(2, ‘静音メカニカルキーボード’, NULL), — オプション情報なし
(3, ‘エルゴノミクスデザインマウス’, ‘カラー: ブラック’),
(4, ‘高解像度4Kモニター’, ‘サイズ: 27インチ’),
(5, ‘フルHDウェブカメラ’, NULL); — 説明もオプションもなし

— products テーブルと結合して情報を取得し、NULL を代替文字列で表示
SELECT
p.product_name,
COALESCE(pe.description, ‘説明なし’) AS product_description,
COALESCE(pe.options, ‘オプション情報なし’) AS product_options
FROM products p
LEFT JOIN products_ext pe ON p.product_id = pe.product_id;
“`

product_name	product_description	product_options
Laptop	高性能ラップトップPC	カラー: シルバー, ブラック
Keyboard	説明なし	オプション情報なし
Mouse	エルゴノミクスデザインマウス	カラー: ブラック
Monitor	高解像度4Kモニター	サイズ: 27インチ
Webcam	説明なし	オプション情報なし

このように、複数のカラムに対して個別に COALESCE を適用することで、表示上の NULL を排除し、ユーザーにとって分かりやすい出力を作成できます。

5.2. 計算での利用

NULL を含む数値計算は、結果が NULL になるため注意が必要です。COALESCE を使えば、NULL を計算に参加させたいデフォルト値（通常は 0）に置き換えることで、計算を続行させることができます。

売上テーブルがあり、単価 (unit_price) と数量 (quantity)、そして割引率 (discount_rate) のカラムがあるとします。割引率が適用されない場合は NULL になっているとします。各項目の純売上高 (net_sales) を計算したい場合、割引率が NULL なら割引なし（つまり割引率 0）として計算する必要があります。

“`sql
CREATE TEMPORARY TABLE order_items (
order_item_id INT PRIMARY KEY,
product_id INT,
unit_price DECIMAL(10, 2),
quantity INT,
discount_rate DECIMAL(5, 2) — 割引率 (例: 0.10 は 10% 割引)
);

INSERT INTO order_items (order_item_id, product_id, unit_price, quantity, discount_rate) VALUES
(1, 1, 1200.00, 1, NULL), — ラップトップ、割引なし
(2, 2, 75.50, 2, 0.05), — キーボード、5%割引
(3, 4, 300.00, 1, NULL), — モニター、割引なし
(4, 1, 1200.00, 1, 0.10); — ラップトップ、10%割引

— 各項目の純売上高を計算
— 純売上高 = 単価 * 数量 * (1 – 割引率)
— 割引率が NULL の場合は 0 として計算 (1 – 0 = 1、つまり割引なし)
SELECT
order_item_id,
product_id,
unit_price,
quantity,
discount_rate,
unit_price * quantity * (1 – COALESCE(discount_rate, 0.00)) AS net_sales
FROM order_items;
“`

order_item_id	product_id	unit_price	quantity	discount_rate	net_sales
1	1	1200.00	1	[NULL]	1200.0000
2	2	75.50	2	0.05	143.4500
3	4	300.00	1	[NULL]	300.0000
4	1	1200.00	1	0.10	1080.0000

この例では、COALESCE(discount_rate, 0.00) によって、discount_rate が NULL の場合は 0.00 が使用されます。これにより (1 - COALESCE(discount_rate, 0.00)) の部分は (1 - 0.00) = 1 となり、割引が適用されずに単価 * 数量がそのまま計算されます。もし COALESCE を使わずに 1 - discount_rate とした場合、discount_rate が NULL の行の net_sales は NULL になってしまいます。

5.3. データ挿入/更新時の利用

データの挿入 (INSERT) や更新 (UPDATE) の際に、入力値が NULL の場合に特定のデフォルト値を設定したい場合があります。アプリケーション側でこのロジックを実装することもできますが、データベース側で COALESCE を使用することで、より簡潔に記述できます。

例えば、新しい注文項目を挿入する際に、割引率が指定されなければデフォルトで 0 を設定したい場合です。

“`sql
— 新しい注文項目を挿入（割引率を指定しない）
INSERT INTO order_items (order_item_id, product_id, unit_price, quantity, discount_rate)
VALUES (5, 3, 50.00, 3, NULL);

— 挿入時に discount_rate が NULL の場合は 0 を設定する
— ここでは INSERT 文自体で COALESCE を使うのではなく、
— あらかじめ NULL になる可能性がある値を COALESCE で変換して挿入することが多い。
— 例: アプリケーションから送られてきた値が NULL の場合
— INSERT INTO order_items (…, discount_rate) VALUES (…, COALESCE(:app_discount_rate, 0.00));
— あるいは、UPDATE 文で NULL を更新する場合:
UPDATE order_items
SET discount_rate = COALESCE(discount_rate, 0.00)
WHERE discount_rate IS NULL; — 現在 NULL である行のみを更新

— 更新後の確認
SELECT * FROM order_items WHERE order_item_id = 5;
“`

order_item_id	product_id	unit_price	quantity	discount_rate
5	3	50.00	3	0.00

UPDATE 文の例では、discount_rate カラムの値自体を、その値が NULL なら 0.00 に、NULL でなければ元の値 (discount_rate) に置き換えています。WHERE discount_rate IS NULL を追加することで、既に値が入っている行は更新されず、効率的です。

INSERT 時の例としては、入力パラメータが NULL の場合に備えて COALESCE を使うというシナリオが一般的です。アプリケーションからの入力値をバインドする際に COALESCE(:input_value, default_value) とすることで、NULL 入力でもデータベースにデフォルト値が格納されます。

5.4. JOIN 条件での利用 (限定的)

NULL と他の値を比較すると結果が「不明」になるため、通常の = 演算子を使った JOIN 条件では NULL はマッチしません。例えば、tableA.col = tableB.col という JOIN 条件では、どちらかのカラムが NULL であればその行は JOIN されません。

場合によっては、「NULL 同士はマッチさせたい」という要件があるかもしれません。このようなケースでは、COALESCE を使って NULL を特定の値に置き換えてから比較する方法が考えられますが、これは推奨されないアプローチです。なぜなら、代替値として選択した値（例えば -1 や '__NULL__' といったマーカー値）が、実際のデータとしてカラムに存在する可能性があり、意図しないマッチングを引き起こす可能性があるからです。

より適切な方法は、前述した IS NOT DISTINCT FROM 演算子を使うことです。これは NULL 同士を等しいものとして扱い、NULL と非NULL を等しくないものとして扱います。

“`sql
CREATE TEMPORARY TABLE table_a (id INT, value VARCHAR(10));
CREATE TEMPORARY TABLE table_b (id INT, value VARCHAR(10));

INSERT INTO table_a VALUES (1, ‘A’), (2, NULL), (3, ‘C’);
INSERT INTO table_b VALUES (1, ‘A’), (4, NULL), (5, ‘C’);

— 通常の JOIN (= を使用) – NULL 同士はマッチしない
SELECT a., b.
FROM table_a a
JOIN table_b b ON a.value = b.value;
— 結果: (1, ‘A’, 1, ‘A’), (3, ‘C’, 5, ‘C’) – (2, NULL) と (4, NULL) はマッチしない

— COALESCE を使用した JOIN (非推奨) – NULL を代替値に置き換えて比較
— SELECT a., b.
— FROM table_a a
— JOIN table_b b ON COALESCE(a.value, ‘NULL‘) = COALESCE(b.value, ‘NULL‘);
— もし ‘A’ が代替値なら (1, ‘A’) と (4, NULL) (代替値’A’) がマッチしてしまう可能性がある

— IS NOT DISTINCT FROM を使用した JOIN (推奨) – NULL 同士がマッチする
SELECT a., b.
FROM table_a a
JOIN table_b b ON a.value IS NOT DISTINCT FROM b.value;
— 結果: (1, ‘A’, 1, ‘A’), (2, NULL, 4, NULL), (3, ‘C’, 5, ‘C’) – NULL 同士もマッチする
“`

したがって、JOIN 条件で NULL のマッチングを含めたい場合は、COALESCE を使うのではなく、IS NOT DISTINCT FROM を使用するようにしましょう。

5.5. 条件分岐の代替

COALESCE 関数は、複数の候補値を順番に試すという性質から、単純な CASE 文の代替として使用できる場合があります。

例えば、「顧客の割引率は、まず期間限定割引があればそれを適用し、なければ通常割引率を適用し、どちらもなければ割引なし(0%)とする」といったロジックは、CASE 文でも COALESCE でも記述できます。

“`sql
CREATE TEMPORARY TABLE customer_discounts (
customer_id INT PRIMARY KEY,
limited_time_discount DECIMAL(5, 2), — 期間限定割引率
regular_discount DECIMAL(5, 2) — 通常割引率
);

INSERT INTO customer_discounts (customer_id, limited_time_discount, regular_discount) VALUES
(201, 0.15, 0.10), — 期間限定も通常もあり
(202, NULL, 0.10), — 通常割引のみ
(203, 0.20, NULL), — 期間限定割引のみ
(204, NULL, NULL); — どちらもなし

— CASE 文で記述
SELECT
customer_id,
CASE
WHEN limited_time_discount IS NOT NULL THEN limited_time_discount
WHEN regular_discount IS NOT NULL THEN regular_discount
ELSE 0.00
END AS applied_discount_case
FROM customer_discounts;

— COALESCE 関数で記述
SELECT
customer_id,
COALESCE(limited_time_discount, regular_discount, 0.00) AS applied_discount_coalesce
FROM customer_discounts;
“`

どちらのクエリも同じ結果を返します。

customer_id	applied_discount_case	applied_discount_coalesce
201	0.15	0.15
202	0.10	0.10
203	0.20	0.20
204	0.00	0.00

この例のように、複数の値候補の中から最初の非NULL 値を選択したいというシンプルなケースでは、COALESCE 関数の方が CASE 文よりもはるかに簡潔で可読性が高くなります。ただし、より複雑な条件分岐（例えば、値の大小や他のカラムの値に基づいて判断する場合）には CASE 文が必要です。

5.6. 集計関数との組み合わせ

前述の通り、標準的な集計関数はデフォルトで NULL を無視します。しかし、集計に NULL 値を含めたい場合、例えば NULL を 0 として扱って合計や平均を計算したい場合は、集計関数の引数として COALESCE を使用します。

売上テーブルの例で、amount カラムに NULL が含まれる場合を思い出してください。

“`sql
CREATE TEMPORARY TABLE sales (amount DECIMAL);
INSERT INTO sales VALUES (100), (200), (NULL), (300);

— NULL を無視して合計と平均を計算 (デフォルトの動作)
SELECT SUM(amount) AS sum_ignore_null, AVG(amount) AS avg_ignore_null
FROM sales;
— 結果: sum_ignore_null = 600, avg_ignore_null = 200.0000…

— NULL を 0 として扱って合計と平均を計算
SELECT SUM(COALESCE(amount, 0)) AS sum_treat_null_as_zero,
AVG(COALESCE(amount, 0)) AS avg_treat_null_as_zero
FROM sales;
— 結果: sum_treat_null_as_zero = 600, avg_treat_null_as_zero = 150.0000…
— (NULL を 0 として数えるため、(100+200+0+300)/4 = 150)
“`

SUM(COALESCE(amount, 0)) では、amount が NULL の行は 0 として SUM 関数に渡されるため、合計値は変わりませんが、計算に参加する値として扱われます。

AVG(COALESCE(amount, 0)) では、amount が NULL の行は 0 として AVG 関数に渡されます。AVG は SUM を COUNT で割ったものですが、引数に式 COALESCE(amount, 0) が指定されているため、AVG はこの式の非NULL の評価結果の数をカウントします。COALESCE(amount, 0) は amount が NULL の場合でも 0 という非NULL 値を返すため、結果的に全ての行が平均の計算に含まれることになります。したがって、平均値は (100 + 200 + 0 + 300) / 4 = 150 となります。

集計に NULL を含めるべきか無視すべきかは業務要件によりますが、COALESCE を使うことでこの振る舞いを明確に制御できます。

6. COALESCE 関数と類似・関連機能の比較

PostgreSQLには COALESCE 以外にも NULL を扱うための様々な関数や構文があります。ここでは、COALESCE と混同されやすいものや、関連性の高いものを比較し、それぞれの使い分けを明確にします。

6.1. COALESCE vs CASE WHEN value IS NULL THEN default_value ELSE value END

最も基本的なCOALESCE(value, default_value) の使い方は、以下の CASE 式と全く同じ意味です。

sql
COALESCE(value, default_value)
-- と等価な CASE 式
CASE
WHEN value IS NOT NULL THEN value
ELSE default_value
END

あるいは、より一般的な CASE 式の形としては以下とも等価です。

sql
COALESCE(value1, value2, value3, ...)
-- と等価な CASE 式
CASE
WHEN value1 IS NOT NULL THEN value1
WHEN value2 IS NOT NULL THEN value2
WHEN value3 IS NOT NULL THEN value3
...
ELSE NULL -- COALESCE は全ての引数が NULL の場合に NULL を返すため、最後の ELSE は NULL に相当
END

COALESCE は、このように複数の候補値の中から最初の非NULL 値を選択するという特定のパターンに対して、CASE 式よりもはるかに短く、読みやすく記述できる「糖衣構文（Syntax Sugar）」です。

• COALESCE を使うべき場面:
  • 複数の式を左から順に評価し、最初の非NULL 値を取得したい場合。
  • 特に、NULL を特定のデフォルト値に置き換えたい場合。
• CASE を使うべき場面:
  • NULL チェック以外の複雑な条件に基づいて値を決定したい場合（例: WHEN value > 10 THEN ...）。
  • 複数の条件分岐があり、それぞれの条件がIS NULLチェックだけではない場合。

COALESCE は CASE の特定のサブセットに対する最適化された表現と考えることができます。可能な限り COALESCE を使用することで、クエリが簡潔になります。

6.2. COALESCE vs NULLIF(value1, value2)

NULLIF 関数は COALESCE とは全く逆の目的で使用されます。NULLIF(value1, value2) は、value1 と value2 が等しい場合に NULL を返し、等しくない場合に value1 を返します。

これは、「特定の値（例: 空文字列、0、無効なコードなど）を NULL として扱いたい」場合に便利です。

sql
SELECT NULLIF('', ''); -- 結果: NULL (空文字列と空文字列は等しい)
SELECT NULLIF('abc', 'def'); -- 結果: 'abc' (abc と def は等しくない)
SELECT NULLIF(10, 10); -- 結果: NULL (10 と 10 は等しい)
SELECT NULLIF(10, 0); -- 結果: 10 (10 と 0 は等しくない)
SELECT NULLIF(NULL, 10); -- 結果: NULL (最初の引数が NULL の場合、NULL を返す)

NULLIF と COALESCE を組み合わせて使用することもあります。例えば、「空文字列を NULL として扱い、さらにその NULL を『値なし』という文字列に置き換えたい」場合です。

“`sql
CREATE TEMPORARY TABLE user_input (text_value VARCHAR(50));
INSERT INTO user_input VALUES (‘Hello’), (”), (NULL), (‘World’);

— 空文字列を NULL にし、さらに NULL を ‘値なし’ に置き換える
SELECT
text_value,
COALESCE(NULLIF(text_value, ”), ‘値なし’) AS processed_value
FROM user_input;
“`

text_value	processed_value
Hello	Hello
	値なし
[NULL]	値なし
World	World

この例では、NULLIF(text_value, '') がまず text_value が空文字列 ('') なら NULL に変換し、それ以外の値ならそのまま text_value を返します。その結果に対して COALESCE を適用することで、元の NULL または NULLIF によって生成された NULL を '値なし' に置き換えています。

6.3. COALESCE vs GREATEST / LEAST

GREATEST(value1, value2, ...) は複数の値の中から最大値を、LEAST(value1, value2, ...) は最小値を返します。これらの関数も複数の引数を取り、最初の非NULL 値を返すという点では COALESCE に似ていますが、NULL の扱いに大きな違いがあります。

PostgreSQLの GREATEST および LEAST 関数は、いずれかの引数が NULL であれば、結果も NULL になります。

sql
SELECT GREATEST(10, 20, NULL, 5); -- 結果: NULL
SELECT LEAST('A', 'B', NULL, 'C'); -- 結果: NULL

これは、COALESCE が NULL でない値を優先して返すのとは対照的です。GREATEST/LEAST は、全ての引数が非NULL であることが期待されるか、あるいは NULL が存在する場合には結果も NULL であるべきという論理を表現する場合に使用します。

もし GREATEST や LEAST の計算において NULL を特定のデフォルト値（例えば数値なら 0 や非常に小さい/大きい値）として扱いたい場合は、これらの関数を COALESCE と組み合わせて使用する必要があります。

sql
-- NULL を 0 として扱って最大値を計算
SELECT GREATEST(COALESCE(value1, 0), COALESCE(value2, 0), COALESCE(value3, 0));

6.4. COALESCE vs IS NULL / IS NOT NULL

IS NULL と IS NOT NULL は、値そのものではなく、その値が NULL であるか否かを判定するための演算子です。これらは主に WHERE 句や CASE 式の条件部で使用され、真偽値を返します。一方、COALESCE は複数の候補値の中から具体的な値を返す関数です。目的が根本的に異なります。

IS NULL や IS NOT NULL は、COALESCE 関数が内部的に行っている NULL チェックの際に使用されるロジックの基礎となります。

7. パフォーマンスに関する考慮事項

COALESCE 関数自体は、PostgreSQLの内部で非常に効率的に実装されており、単純な NULL チェックと値の選択を行うだけなので、関数呼び出しによるパフォーマンスオーバーヘッドはほとんど無視できるレベルです。

しかし、COALESCE 関数をどのように使用するかによっては、クエリ全体のパフォーマンスに影響を与える可能性があります。特に注意が必要なのは、インデックスの使用です。

7.1. COALESCE を使用したカラムに対するインデックス

標準的なB-treeインデックスは、カラムの実際の値に基づいて構築されます。WHERE 句や ORDER BY 句で COALESCE(column_name, default_value) のような式を使用した場合、PostgreSQLのクエリプランナーは通常、その式全体に直接対応するインデックスを利用できません。なぜなら、インデックスは column_name の値そのものに対して構築されており、COALESCE 関数を適用した結果の値に対しては構築されていないからです。

例:
“`sql
— orders テーブルがあり、ship_date カラムに NULL が含まれる可能性があるとする
— ship_date に通常のインデックスがある
CREATE INDEX ON orders (ship_date);

— このクエリは ship_date のインデックスを使えない可能性が高い
SELECT * FROM orders WHERE COALESCE(ship_date, ‘9999-12-31’) = ‘9999-12-31’;

— こちらのクエリは ship_date のインデックスを使える可能性が高い
SELECT * FROM orders WHERE ship_date IS NULL OR ship_date = ‘9999-12-31’;
“`

COALESCE(ship_date, '9999-12-31') = '9999-12-31' という条件は、ship_date が '9999-12-31' である場合と、ship_date が NULL で COALESCE の結果が '9999-12-31' になる場合の両方をカバーします。クエリプランナーは COALESCE 関数の内部動作を完全には最適化できないため、テーブル全体をスキャンする (Sequential Scan) 選択をする可能性が高くなります。

一方、ship_date IS NULL OR ship_date = '9999-12-31' という条件は、OR の各条件に対して別々にインデックスを使用できる可能性があります。ship_date IS NULL は特別なインデックス（B-tree インデックスは NULL エントリを含むことができる）またはビットマップスキャンなどで効率的に処理でき、ship_date = '9999-12-31' は通常のインデックススキャンを利用できます。

7.2. 関数インデックス (Functional Index) の活用

もし COALESCE を使用した式が頻繁にクエリの WHERE 句や ORDER BY 句で使用され、そのパフォーマンスが問題となる場合、関数インデックスを作成することを検討できます。関数インデックスは、カラムの値そのものではなく、カラムに関数を適用した結果に対して構築されるインデックスです。

“`sql
— COALESCE(ship_date, ‘9999-12-31’) に対して関数インデックスを作成
CREATE INDEX idx_orders_coalesced_ship_date ON orders (COALESCE(ship_date, ‘9999-12-31’));

— このクエリは作成した関数インデックスを使えるようになる可能性がある
SELECT * FROM orders WHERE COALESCE(ship_date, ‘9999-12-31’) = ‘9999-12-31’;

— ORDER BY に使用する場合もインデックスが使える可能性がある
SELECT * FROM orders ORDER BY COALESCE(ship_date, ‘9999-12-31’);
“`

関数インデックスは非常に強力ですが、いくつかの注意点があります。

• インデックスを作成・維持するためのコスト（ディスク容量、INSERT/UPDATE/DELETE 時のオーバーヘッド）が発生します。
• インデックスが使用されるのは、クエリの式がインデックスを作成した際の式と完全に一致する場合のみです。
• 関数インデックスは、インデックス対象の関数が IMMUTABLE または STABLE である必要があります。COALESCE は引数に依存して結果が変わる STABLE 関数なので、関数インデックスの対象にできます。

したがって、COALESCE 式を使ったクエリが頻繁に実行され、パフォーマンスがボトルネックになっている場合にのみ、関数インデックスの作成を検討するのが良いでしょう。

7.3. 複雑な式を引数に使用する場合

COALESCE 関数は引数を左から順番に評価し、NULL でない値が見つかり次第評価を停止します。この性質を利用して、パフォーマンスを意識した引数の並び順を検討できます。

例えば、3つの候補値があり、value1 は単純なカラム参照、value2 は少し複雑な計算、value3 は非常にコストのかかる関数呼び出しだとします。

“`sql
— コストのかかる関数（例としてランダム値を生成する関数を使用）
— 実際の業務では、外部サービス呼び出しや複雑な集計などが考えられます
CREATE OR REPLACE FUNCTION get_expensive_default_value() RETURNS INTEGER AS $$
BEGIN
RAISE NOTICE ‘Expensive function called!’; — 関数が呼ばれたことを確認するためのメッセージ
RETURN floor(random() * 1000)::INTEGER;
END;
$$ LANGUAGE plpgsql;

— 評価順序が結果に影響する場合
SELECT COALESCE(NULL, NULL, get_expensive_default_value()); — Expensive function が呼ばれる
SELECT COALESCE(10, NULL, get_expensive_default_value()); — Expensive function は呼ばれない
“`

もし value1 や value2 が NULL になる可能性が低く、value3 が高コストな場合、COALESCE(value1, value2, value3) のように並べることで、多くのケースで高コストな value3 の評価をスキップできます。これは COALESCE 関数の短絡評価（short-circuit evaluation）と呼ばれる特性です。

常にコストの低い式を先に、コストの高い式を後に配置することで、不要な計算を避けることができます。

8. データ型に関する注意点

COALESCE 関数の戻り値のデータ型は、引数のデータ型から決定されます。PostgreSQLは引数のデータ型を検査し、全ての引数を格納できる適切な共通のデータ型を推測しようとします。このプロセスは型解決（type resolution）と呼ばれます。

• 同じデータ型の引数: 全ての引数が同じデータ型であれば、戻り値はそのデータ型になります。
  sql
SELECT COALESCE(10, 20, 30); -- integer を返す
SELECT COALESCE('Hello', 'World'); -- text/varchar を返す
SELECT COALESCE(10.5, 20.0); -- numeric を返す
• 異なるデータ型の引数 (互換性がある場合): 互換性のある異なるデータ型が混在する場合、PostgreSQLはより一般的な型や精度の高い型に暗黙的に変換しようとします。
  sql
SELECT COALESCE(10, 20.5); -- integer と numeric/decimal -> numeric/decimal を返す
SELECT COALESCE('Text', 123::TEXT); -- text/varchar と text -> text/varchar を返す
SELECT COALESCE(NULL, 1); -- NULL (型不明) と integer -> integer を返す
SELECT COALESCE(10::SMALLINT, 20::INTEGER, 30::BIGINT); -- より上位の BIGINT を返す
• 異なるデータ型の引数 (互換性がない場合): 数値型と文字列型のように、互換性のないデータ型が混在する場合、エラーになるか、あるいは意図しない型変換が発生する可能性があります。
  sql
-- SELECT COALESCE(123, 'Not available'); -- エラー (integer と text の混在)
  このような場合は、前述の例のように明示的な型キャスト (::type_name) を使用してデータ型を揃える必要があります。
  sql
SELECT COALESCE(123::TEXT, 'Not available'); -- text を返す
SELECT COALESCE(price::TEXT, '価格未定'); -- DECIMAL を TEXT にキャスト
  どの型にキャストすべきかは、期待する最終的な結果のデータ型によります。例えば、数値を文字列に置き換えるなら文字列型 (TEXT, VARCHAR) に、日付を特定の日付や文字列に置き換えるなら日付型 (DATE, TIMESTAMP) または文字列型にキャストします。

戻り値のデータ型は、COALESCE 関数の最初の非 NULL 引数のデータ型に最も強く影響されます。しかし、後続の引数のデータ型も考慮され、PostgreSQLは引数全体で最も「一般的な」型を選択しようとします。例えば COALESCE(integer_col, decimal_col) は decimal を返すでしょう。

安全のため、特に異なるカテゴリのデータ型を COALESCE の引数に使用する場合は、戻り値のデータ型を意識し、必要であれば明示的な型キャストを行うことを強く推奨します。これにより、予期せぬ型変換エラーや不正確な結果を防ぐことができます。

9. 実践的なヒントとベストプラクティス

COALESCE 関数を効果的に使うためのいくつかのヒントとベストプラクティスを紹介します。

• 引数の順序を考慮する: COALESCE は左から評価されることを利用し、最も可能性の高い非 NULL 値を最初に、より低い可能性の値を後続に配置します。また、計算コストの低い式を先に、コストの高い式を後に配置することで、パフォーマンスを向上させられる可能性があります。
• デフォルト値のデータ型を合わせる: デフォルト値としてリテラルを指定する場合、対象カラムのデータ型と互換性のある型のリテラルを使用するように心がけましょう。必要に応じて明示的な型キャストを行います。例えば、DECIMAL 型のカラムには 0.00 のようにスケールを指定した数値リテラルを使用したり、TEXT 型にキャストしたりします。
• デフォルト値の意味を明確にする: デフォルト値は、単に NULL を埋めるだけでなく、その値がビジネス上どのような意味を持つのかを考慮して選択します。例えば、金額の場合は 0 が適切かもしれませんが、数量の場合は 0 が有効な値である一方、NULL は「不明」を意味するかもしれないため、「不明」を表す別の値（例えば負の数や特別なコード）や文字列で代替する方が適切な場合もあります。
• 不必要な COALESCE の使用を避ける: NULL が許容され、そのままで問題ない計算や表示の場合には、無理に COALESCE を使用する必要はありません。例えば、COUNT(column_name) はデフォルトで NULL を無視するため、NULL を含めたくない場合は COALESCE は不要です。集計結果が NULL になることが想定される場合でも、それを適切にアプリケーション側で処理する方が自然な場合もあります。
• 関数インデックスの利用を検討する: COALESCE 式がクエリのフィルタリングやソート条件として頻繁に使用され、パフォーマンスに影響がある場合は、関数インデックスの作成を検討します。ただし、インデックスの管理コストとクエリパフォーマンスの向上効果を天秤にかける必要があります。
• 可読性を意識する: COALESCE 関数は比較的短いですが、引数の数が増えると読みにくくなることがあります。多くの引数が必要な場合は、処理の意図を明確にするためにコメントを追加したり、必要に応じて複数の COALESCE をネストしたり (COALESCE(val1, COALESCE(val2, default_val)))、CASE 文を使用したりすることも検討します。ただし、多くの場合は単一の COALESCE で事足ります。

10. よくある間違いとその回避策

COALESCE 関数を使う上で陥りやすい間違いと、その回避策を知っておくことで、問題を未然に防ぐことができます。

• 間違い1: 互換性のないデータ型を混在させる
  • 例: COALESCE(numeric_column, 'N/A')
  • 回避策: 明示的な型キャストを使用してデータ型を揃える。
    sql
SELECT COALESCE(numeric_column::TEXT, 'N/A');
• 間違い2: NULL の等価比較に COALESCE を使う (JOIN 条件など)
  • 例: ON COALESCE(a.column, default_val) = COALESCE(b.column, default_val)
  • 回避策: NULL の等価比較が必要な場合は IS NOT DISTINCT FROM を使用する。
    sql
ON a.column IS NOT DISTINCT FROM b.column
• 間違い3: 全ての NULL が同じ意味を持つと仮定する
  • データベース内の NULL は、単一の理由（例: 未入力）だけでなく、複数の異なる理由（例: 未入力、該当なし、不明、非公開など）で発生している可能性があります。COALESCE で一律に同じデフォルト値に置き換える前に、NULL が持つ意味を理解することが重要です。異なる意味を持つ NULL には、CASE 文などを使用して異なる代替値を適用することを検討します。
• 間違い4: COALESCE が全ての集計関数で NULL を計算に含めると思っている
  • SUM(COALESCE(col, 0)) は NULL を 0 として合計に含めますが、COUNT(COALESCE(col, 0)) は col が NULL であっても 0 は非NULL であるため全ての行を数えます。一方 COUNT(col) は NULL の行を無視します。AVG も引数の非NULL 値の数で割るため、AVG(COALESCE(col, 0)) と AVG(col) では結果が異なることがあります。使用する集計関数と COALESCE の組み合わせが期待通りの結果になるか確認が必要です。
• 間違い5: COALESCE が引数を全て評価すると思っている
  • COALESCE は最初の非NULL 値が見つかった時点で評価を停止します（短絡評価）。これを理解していないと、パフォーマンスに影響したり、意図しない関数呼び出し（または呼び出しのスキップ）が発生する可能性があります。パフォーマンスや副作用のある関数を引数に指定する場合は、評価順序を意識して配置します。

11. まとめ：COALESCE 関数を使いこなそう

本記事では、PostgreSQLの COALESCE 関数について、NULL の基本的な理解から始まり、関数の構文、動作原理、そして様々な応用例、関連機能との比較、パフォーマンスやデータ型に関する注意点までを詳しく解説しました。

COALESCE 関数は、複数の候補値の中から最初に見つかった非 NULL 値を選択するためのシンプルながら非常に強力なツールです。特に、データベースから取得した NULL 値をアプリケーションやレポートで扱いやすいように、特定のデフォルト値に置き換える用途で広く利用されています。

また、計算において NULL をデフォルト値として含めたい場合や、複数のカラムにわたる候補値から最適なものを選択したい場合など、より複雑なシナリオにも対応できます。CASE 文のような条件分岐の代替として、クエリの可読性を向上させる効果も期待できます。

一方で、COALESCE を使用した式に対するインデックスの扱いや、異なるデータ型が混在する場合の型解決など、注意すべき点もいくつかあります。これらの点に留意し、必要に応じて関数インデックスや明示的な型キャストを適切に使用することで、パフォーマンスと正確性を両立させることができます。

NULL はデータベースの世界では避けられない存在であり、その扱いはデータ処理の品質に直結します。COALESCE 関数を正しく理解し、適切に使いこなすことは、PostgreSQLを使った開発において非常に重要なスキルです。

ぜひ、この記事で学んだ知識を活かして、あなたのクエリやアプリケーションにおける NULL の問題を解決し、より堅牢で使いやすいシステムを構築してください。

12. 参考資料

• PostgreSQL 16ドキュメント: 9.16. Conditional Expressions (条件式) – COALESCE
  https://www.postgresql.jp/document/16/html/functions-conditional.html#FUNCTIONS-COALESCE
• PostgreSQL 16ドキュメント: 9.2. Value Expressions (値式) – NULLIF
  https://www.postgresql.jp/document/16/html/functions-comparison.html#FUNCTIONS-NULLIF
• PostgreSQL 16ドキュメント: 9.15. Aggregate Functions (集計関数)
  https://www.postgresql.jp/document/16/html/functions-aggregate.html
• PostgreSQL 16ドキュメント: 11.7. Indexes on Expressions (式に対するインデックス)
  https://www.postgresql.jp/document/16/html/indexes-expressional.html

---

この記事は、PostgreSQLの COALESCE 関数について、網羅的かつ詳細な情報を提供することを目的としています。約5000語の要件を満たすため、各セクションを深掘りし、具体的なSQLコード例と解説を豊富に含めて記述しました。