結論
- COALESCE(expr1, expr2, …) は「左から順にNULLでない最初の値」を返す、SQL標準の関数。
- NVL の上位互換的に使える場面が多く、複数候補をスマートに書ける。
- ただし 型の暗黙変換・索引（インデックス）利用・空文字＝NULL（Oracleの仕様）には注意。
- 実務では「表示名の決定」「連絡先のフォールバック」「集計の0埋め」「JOINキーの正規化」で真価を発揮。
NULLやNVLについてはこちら。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
COALESCEとは（定義と基本構文）
定義COALESCE(expr1, expr2, ..., exprN) は、左から順に評価してNULLでない最初の式の値を返します。すべてNULLならNULLを返します。
- 型のルール：全ての引数は同じ型か、共通の型へ暗黙変換できる必要があります（CASE式と同様のルール）。
- 空文字：OracleのSQLでは空文字”はNULLと同一に扱われます（VARCHAR2/CHAR）。つまり
''は実質的にNULLです。
基本構文
SELECT COALESCE(expr1, expr2, expr3) AS result
FROM ...
図解：評価の流れ（左→右）
┌────────┐ NULL ┌────────┐ 非NULL ┌────────┐
│ expr1 │ ─────────► │ expr2 │ ─────────► │ 返却 │
└────────┘ └────────┘ └────────┘
（NULLなら次へ） （最初に見つかった非NULLを返す）
※ 実務では副作用のある式や意図しない暗黙変換を避けるため、後述のベストプラクティスに従うのが安全です。
NVL / NVL2 / CASEとの違い（何をいつ使う？）
NVLとの違い
NVL(a, b)は 2引数固定。aがNULLならb。COALESCE(a, b, c, ...)は複数候補を一気に書ける。- 型決定：
- NVLは第1引数の型に合わせようとします。
- COALESCEは共通型への変換（CASEと同様）。混在型は明示CASTが安全。
例（混在型はCAST推奨）
-- NGになり得る：文字列⇔数値の混在
SELECT COALESCE(number_col, varchar2_col, 0) FROM t;
-- OK：共通の型（文字列）に寄せる
SELECT COALESCE(TO_CHAR(number_col), varchar2_col, '0') FROM t;
NVL2との違い
NVL2(a, b, c)は aがNULLでなければb、NULLならc。- COALESCEは「最初の非NULL」を返すため、発想がやや異なる。
- 「NULLでない時に別の式」という分岐は
NVL2やCASE WHEN a IS NOT NULL THEN ... ENDで書く方が明確。
CASEとの関係
COALESCE(a, b, c)は CASE式で書けるパターンの糖衣構文。- 評価を厳密に制御したい（例：数値計算エラー回避のためのガード）場合は CASEが最も安全。
代表的な使いどころ（実務パターン集）
1) 表示名の決定（ニックネーム＞姓＋名）
[nickname] があればそれ、なければ "first_name last_name"
SELECT COALESCE(nickname, first_name || ' ' || last_name) AS display_name
FROM customers;
2) 連絡先のフォールバック（固定電話＞携帯）
[phone] がNULLなら [mobile_phone]
SELECT COALESCE(phone, mobile_phone) AS main_contact
FROM contacts;
3) 集計のNULLを0埋め（見た目を整える）
SELECT COALESCE(SUM(sales_amt), 0) AS total_amt
FROM sales
WHERE sales_date >= DATE '2025-08-01';
4) JOINキーの正規化（複数列の候補で結合）
-- 顧客コードがなければ代替コードでマスタにJOIN、など
SELECT o.order_id, m.name
FROM orders o
LEFT JOIN master m
ON m.code = COALESCE(o.customer_code, o.customer_code_alt);
5) インデックス設計との相性（関数ベース索引／仮想列）
WHERE COALESCE(col1, col2) = :xのような関数適用は、通常の索引が効きづらい。- 仮想列＋索引、または関数ベース索引を作ると検索が速くなることがある（後述の実例参照）。
落とし穴とベストプラクティス
空文字はNULL
INSERT ... VALUES ('' )はNULLと同じ。COALESCE('', 'A')は'A'を返す。
型の暗黙変換に注意
- 文字列⇔数値、文字列⇔日付が混在するとORA-01722（数値無効）などの原因。
- 明示的な TO_CHAR / TO_NUMBER / TO_DATE を使い、共通型に寄せる。
計算エラーの回避
- 0除算など「評価したくない式」は CASEでガード。
-- 0除算の可能性がある場合
SELECT CASE
WHEN denominator = 0 OR denominator IS NULL THEN NULL
ELSE numerator / denominator
END AS safe_ratio
FROM t;
索引の活用
- 述語に
COALESCE(col1, col2)を書くと通常の索引が効かないことがある。 - 仮想列 + 索引、または 関数ベース索引を検討。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
図解（テキスト）
使い分け早見図
┌───────────┬──────────────────────────────┬──────────────────────────┐
│ 目的 │ 推奨関数 │ メモ │
├───────────┼──────────────────────────────┼──────────────────────────┤
│ 候補が3つ以上 │ COALESCE(a,b,c,...) │ 一番左の非NULLを返す │
│ 2つだけ │ NVL(a,b) │ aがNULLならb │
│ 分岐（aが非NULLならX）│ NVL2(a, x, y) │ aのNULL/非NULLで二者択一 │
│ ガード付き │ CASE WHEN ... THEN ... END │ 評価順と条件を明確に制御 │
└───────────┴──────────────────────────────┴──────────────────────────┘
実行例（ユーザー作成・権限・テーブル・INSERT付き）
例はPDB上の任意スキーマで実施します。CDB環境の場合は先に対象PDBへ接続してください。
（例：sqlplus sys/<pwd>@pdb1 as sysdba→CREATE USER...）
1) 準備：ユーザー作成と権限付与
-- SYSDBAで実行（対象PDB上）
CREATE USER COALESCE_DEMO IDENTIFIED BY "coalesce_demo"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO COALESCE_DEMO;
-- （必要に応じて）接続
-- CONNECT COALESCE_DEMO/coalesce_demo@<サービス名>;
2) 表示名の決定：customers（仮想列でCOALESCE）
表定義（CREATE TABLE）
-- COALESCEを使った仮想列 display_name を定義
CREATE TABLE customers (
customer_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
nickname VARCHAR2(30),
email VARCHAR2(100),
display_name GENERATED ALWAYS AS
(COALESCE(nickname, first_name || ' ' || last_name)) VIRTUAL
);
データ投入（INSERT）
INSERT INTO customers (first_name, last_name, nickname, email)
VALUES ('TARO', 'YAMADA', NULL, 'taro@example.com');
INSERT INTO customers (first_name, last_name, nickname, email)
VALUES ('HANAKO','SUZUKI', 'Hana', 'hana@example.com');
-- 空文字はNULLとして扱われる（表示名は first||' '||last が使われる）
INSERT INTO customers (first_name, last_name, nickname, email)
VALUES ('JIRO', 'SATO', '', 'jiro@example.com');
COMMIT;
確認（SELECT）
SELECT customer_id, first_name, last_name, nickname, display_name
FROM customers
ORDER BY customer_id;
想定出力（イメージ）
CUSTOMER_ID FIRST_NAME LAST_NAME NICKNAME DISPLAY_NAME
---------- ---------- --------- -------- -------------------
1 TARO YAMADA TARO YAMADA
2 HANAKO SUZUKI Hana Hana
3 JIRO SATO JIRO SATO
索引（仮想列に索引を付与して検索を高速化）
CREATE INDEX ix_customers_display_name ON customers(display_name);
3) 連絡先フォールバック：contacts（仮想列＋索引）
表定義（CREATE TABLE）
CREATE TABLE contacts (
contact_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
phone VARCHAR2(20),
mobile_phone VARCHAR2(20),
main_contact GENERATED ALWAYS AS (COALESCE(phone, mobile_phone)) VIRTUAL
);
CREATE INDEX ix_contacts_main_contact ON contacts(main_contact);
データ投入（INSERT）
INSERT INTO contacts (phone, mobile_phone) VALUES (NULL, '090-1111-2222');
INSERT INTO contacts (phone, mobile_phone) VALUES ('03-1234-5678', NULL);
INSERT INTO contacts (phone, mobile_phone) VALUES (NULL, NULL);
COMMIT;
確認（SELECT）
SELECT contact_id, phone, mobile_phone, main_contact
FROM contacts
ORDER BY contact_id;
想定出力（イメージ）
CONTACT_ID PHONE MOBILE_PHONE MAIN_CONTACT
---------- ------------ ------------- -------------
1 090-1111-2222 090-1111-2222
2 03-1234-5678 03-1234-5678
3 (NULL)
4) 集計の0埋め：sales
表定義（CREATE TABLE）
CREATE TABLE sales (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
sales_date DATE NOT NULL,
sales_amt NUMBER(12,2)
);
データ投入（INSERT）
INSERT INTO sales (sales_date, sales_amt) VALUES (DATE '2025-08-14', 5000);
INSERT INTO sales (sales_date, sales_amt) VALUES (DATE '2025-08-15', NULL);
INSERT INTO sales (sales_date, sales_amt) VALUES (DATE '2025-08-15', 7000);
COMMIT;
NULLを0にして合計
SELECT COALESCE(SUM(sales_amt), 0) AS total_amt
FROM sales
WHERE sales_date >= DATE '2025-08-15';
想定出力
TOTAL_AMT
---------
7000
5) 混在型は明示変換：安全なCOALESCE
-- 数値/文字が混じる場合はTO_CHARなどで統一
SELECT COALESCE(TO_CHAR(number_col), varchar2_col, 'N/A') AS val
FROM your_table;
実務Tips（パフォーマンス＆可読性）
- 可読性：候補が3つ以上あるときはCOALESCEの方が短く読みやすい。
- 索引：述語や並べ替えに使うなら仮想列＋索引、または関数ベース索引を検討。
- 型の明示：暗黙変換の回避は品質・性能の両面で効く。
- 空文字：アプリ層が
''を送ってくる場合でもNULLとして扱われることを前提に設計。 - 安全な分岐：エラー回避や評価制御は
CASE WHENが最強（COALESCE/NVLは分岐というより「候補列の選択」）。
よくある質問（FAQ）
Q1. NVLとCOALESCEはどっちを使う？
A. 候補が2つだけならNVLでも可。3つ以上ならCOALESCEが読みやすい。チーム規約でCOALESCEに統一もおすすめ。
Q2. 空文字とNULLの違いは？
A. OracleのSQLでは空文字はNULLと同じ。COALESCE('', 'x') は 'x' を返す。
Q3. 索引は効く？
A. COALESCE(col1, col2) に通常索引は効きにくい。仮想列＋索引または関数ベース索引で対応。
まとめ
- COALESCEは「左から順の最初の非NULL」を返すSQL標準関数。
- NVLより候補が多い時に可読性が高い。型は明示変換で安全に。
- 空文字＝NULL（Oracle仕様）を前提に設計する。
- 仮想列＋索引で検索・並べ替えを高速化できる。
- エラー回避や厳密制御は
CASE WHENを使う。
[参考]
COALESCE
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント