COALESCE は、リスト内で最初に NULL でない値を返します。SQL で欠けている値を代替値に置き換えるための方法です。このページの例は PostgreSQL、MySQL、SQLite、SQL Server で動作します。
COALESCE の使い方
COALESCE には任意の数の値を渡せます。左から順に値をチェックし、最初に NULL でない値を返します。すべて NULL の場合は NULL を返します。
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
実際のクエリでは、値は通常カラムで、最後の手段としてリテラルを末尾に置きます。
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
知っておくべきルール
- 最初の NULL でない値で処理が止まります。値が見つかると、それ以降の引数は評価されません。値が入っていることが最も多いものを先頭に置きましょう。
- すべての引数は互換性のある型である必要があります。数値とテキストを混在させると PostgreSQL や SQL Server ではエラーになるため、先にキャストしてください。
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
空文字列は NULL ではありません。COALESCE は
''をそのまま返します。空文字列も欠損値として扱うには、カラムをNULLIF(column, '')で囲みます。
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
別の構文
2 引数の簡略版もよく見かけます。MySQL と SQLite の IFNULL、SQL Server の ISNULL です。
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
COALESCE を使うことをおすすめします。任意の数の引数を受け取れるうえ、SQL Server の ISNULL は結果に最初の引数の型を適用するため、代替値が切り詰められることがあります。
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
CASE 式でも同じことができますが、記述が長くなります。COALESCE(nickname, name) は CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END の省略形です。より高度な処理が必要な場合は SQL CASE 文ガイドをご覧ください。
COALESCE はどんなときに使うべき？
NULL によって結果が誤ったり、見づらくなったり、空になったりする場合は COALESCE を使いましょう。ほとんどの用途は次の 4 つのいずれかに当てはまります。
別のカラムで代替する
必要な値が複数のカラムのどれかに入っている場合、COALESCE は最初に値が入っているものを選びます。ニックネームかフルネーム、携帯電話か固定電話、配送先住所か請求先住所などです。
すべての値が欠けている場合にデフォルト値を使う
リストの末尾にリテラルを置けば、結果が NULL になることはありません。空欄よりも「Address Unknown」や 0.00 の方が読みやすいレポートや画面に最適です。
計算値で欠損を埋める
代替値はカラムや定数である必要はありません。他のカラムから組み立てたユーザー名やメールアドレスなどの式も使えるため、インポートしたデータのクリーンアップにも便利です。
計算から NULL を排除する
NULL を含む算術演算の結果は NULL になるため、割引やボーナスが欠けているだけで合計全体が消えてしまいます。COALESCE(discount, 0) を使えば、欠損値を計算に使える数値に変換できます。
COALESCE の実例
登録されている価格を表示する
商品にはセール価格や通常価格があり、（発売前は）どちらもない場合もあります。順番にチェックし、デフォルトは 0 にします。
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
連絡に最適な電話番号を選ぶ
連絡先には最大 3 つの番号があります。最初に利用できる番号を表示し、番号がない場合はその旨を表示します。
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
仮のメールアドレスを生成する
メールアドレスが登録されていない従業員には名前からアドレスを生成し、名簿のすべての行に値が入るようにします。
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
MySQL と SQL Server では、|| の代わりに CONCAT() で文字列を連結してください。
割引が任意の場合に注文合計を計算する
COALESCE を使わないと、割引のない明細が 1 行あるだけで注文合計全体が正しくなくなります。SUM は NULL の行を完全にスキップするためです。
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
COALESCE の関連資料
このブログの記事:
-
MySQL COALESCE: COALESCE と MySQL の
IFNULLの比較 - SQL CASE 文ガイド: NULL チェックを超えた条件ロジック
- SQL 不等号ガイド: NULL との比較が一致しない理由
- SQL GROUP BY ガイド: COALESCE とよく組み合わせる集計関数
- SQL SUBSTRING ガイド: NULL の可能性があるカラムからのテキスト抽出
公式ドキュメント:
Beekeeper Studioは無料のオープンソースのデータベースGUIです
これまで使った中で最高のSQLクエリ＆エディタツールです。データベースの管理に必要なものがすべて揃っています。 - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studioは高速で直感的、そして使いやすいツールです。多数のデータベースに対応し、Windows、Mac、Linuxで快適に動作します。
Beekeeper Studio 利用者の声
「Beekeeper Studio は以前の SQL ワークフローを完全に置き換えてくれました。高速で直感的、データベース作業がまた楽しくなりました。」
「多くのデータベース GUI を試しましたが、Beekeeper は機能とシンプルさのバランスが完璧です。とにかくちゃんと動きます。」