PostgreSQL COALESCE完全ガイド – NULL値を賢く処理する方法

データベース・SQL

「SQLでNULL値が出てきて計算がおかしくなる…」

「NULLの時だけデフォルト値を表示したい」

「複数のカラムから最初の有効な値を取得したい」

「CASE文を使うと複雑になりすぎる」

そんな悩みを解決するのがCOALESCE関数です！

COALESCEは「NULL値を別の値に置き換える」という、シンプルだけど超強力な関数。これをマスターすれば、NULL値に悩まされることがなくなります。

この記事では、COALESCEの基本から応用まで、実践的な例を交えながら分かりやすく解説していきます！

スポンサーリンク

COALESCEとは？基本を理解しよう

COALESCEの基本構文

COALESCE(値1, 値2, 値3, ...)

動作原理： 左から順番に評価して、最初にNULLではない値を返す

簡単な例で理解する

-- 基本例
SELECT COALESCE(NULL, 'デフォルト値');
-- 結果: 'デフォルト値'

SELECT COALESCE('実際の値', 'デフォルト値');
-- 結果: '実際の値'

SELECT COALESCE(NULL, NULL, NULL, '最後の値');
-- 結果: '最後の値'

-- 全部NULLの場合
SELECT COALESCE(NULL, NULL, NULL);
-- 結果: NULL

なぜCOALESCEが必要？

NULL値の問題点：

-- NULL値との計算は全てNULLになる
SELECT 100 + NULL;  -- 結果: NULL
SELECT 'Hello' || NULL;  -- 結果: NULL

-- COALESCEで解決
SELECT 100 + COALESCE(NULL, 0);  -- 結果: 100
SELECT 'Hello' || COALESCE(NULL, '');  -- 結果: 'Hello'

実践！よく使うCOALESCEパターン

パターン1：デフォルト値の設定

-- ユーザーテーブルの例
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nickname VARCHAR(100),
    email VARCHAR(200),
    phone VARCHAR(20)
);

-- ニックネームがない場合は本名を表示
SELECT 
    name,
    COALESCE(nickname, name) AS display_name
FROM users;

-- 連絡先の優先順位（メール → 電話 → 「連絡先なし」）
SELECT 
    name,
    COALESCE(email, phone, '連絡先なし') AS contact
FROM users;

パターン2：計算でのNULL対策

-- 売上テーブル
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    discount DECIMAL(10,2),
    quantity INTEGER
);

-- 割引がNULLの場合は0として計算
SELECT 
    product_name,
    price,
    COALESCE(discount, 0) AS discount,
    price - COALESCE(discount, 0) AS final_price,
    (price - COALESCE(discount, 0)) * quantity AS total
FROM sales;

-- 平均計算でNULLを除外
SELECT 
    AVG(COALESCE(discount, 0)) AS avg_discount
FROM sales;

パターン3：文字列結合でのNULL処理

-- 住所テーブル
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    prefecture VARCHAR(50),
    city VARCHAR(50),
    town VARCHAR(50),
    building VARCHAR(100)
);

-- NULL値を空文字に変換して結合
SELECT 
    COALESCE(prefecture, '') || 
    COALESCE(city, '') || 
    COALESCE(town, '') || 
    COALESCE(building, '') AS full_address
FROM addresses;

-- より見やすく整形
SELECT 
    CONCAT_WS(' ',
        COALESCE(prefecture, ''),
        COALESCE(city, ''),
        COALESCE(town, ''),
        COALESCE(building, '')
    ) AS formatted_address
FROM addresses;

COALESCEの応用テクニック

複数テーブルの結合でのNULL処理

-- 商品と在庫の結合
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    default_price DECIMAL(10,2)
);

CREATE TABLE inventory (
    product_id INTEGER,
    current_price DECIMAL(10,2),
    stock_quantity INTEGER
);

-- LEFT JOINでNULLが発生する場合の対処
SELECT 
    p.name,
    COALESCE(i.current_price, p.default_price, 0) AS selling_price,
    COALESCE(i.stock_quantity, 0) AS stock,
    CASE 
        WHEN COALESCE(i.stock_quantity, 0) = 0 THEN '在庫なし'
        WHEN COALESCE(i.stock_quantity, 0) < 10 THEN '在庫わずか'
        ELSE '在庫あり'
    END AS stock_status
FROM products p
LEFT JOIN inventory i ON p.id = i.product_id;

日付のNULL処理

-- イベントテーブル
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(200),
    scheduled_date DATE,
    actual_date DATE,
    cancelled_date DATE
);

-- 実際の日付 → 予定日 → 現在日付の優先順位
SELECT 
    event_name,
    COALESCE(actual_date, scheduled_date, CURRENT_DATE) AS event_date,
    COALESCE(
        cancelled_date::TEXT, 
        actual_date::TEXT, 
        '予定: ' || scheduled_date::TEXT
    ) AS status
FROM events;

-- 日付の差分計算
SELECT 
    event_name,
    COALESCE(actual_date, scheduled_date) AS final_date,
    CURRENT_DATE - COALESCE(actual_date, scheduled_date, CURRENT_DATE) AS days_ago
FROM events;

JSON/JSONBでのCOALESCE

-- JSON設定テーブル
CREATE TABLE user_settings (
    user_id INTEGER PRIMARY KEY,
    preferences JSONB
);

-- JSONフィールドのNULL対策
SELECT 
    user_id,
    COALESCE(preferences->>'theme', 'default') AS theme,
    COALESCE(preferences->>'language', 'ja') AS language,
    COALESCE((preferences->>'notifications')::BOOLEAN, true) AS notifications
FROM user_settings;

-- JSON全体のデフォルト値
SELECT 
    user_id,
    COALESCE(preferences, '{"theme":"default","language":"ja"}'::JSONB) AS settings
FROM user_settings;

COALESCEと他の関数の比較

COALESCE vs CASE文

-- CASE文での実装（冗長）
SELECT 
    CASE 
        WHEN column1 IS NOT NULL THEN column1
        WHEN column2 IS NOT NULL THEN column2
        WHEN column3 IS NOT NULL THEN column3
        ELSE 'デフォルト'
    END AS result
FROM table_name;

-- COALESCEなら簡潔
SELECT 
    COALESCE(column1, column2, column3, 'デフォルト') AS result
FROM table_name;

COALESCE vs NULLIF

-- NULLIFは特定の値をNULLに変換
SELECT NULLIF('', '');  -- 結果: NULL
SELECT NULLIF('value', '');  -- 結果: 'value'

-- 組み合わせて使う
SELECT 
    COALESCE(NULLIF(TRIM(comment), ''), 'コメントなし') AS comment_display
FROM reviews;
-- 空文字や空白のみの場合も「コメントなし」と表示

COALESCE vs IFNULL（MySQL互換性）

-- PostgreSQLにはIFNULLはない
-- 代わりにCOALESCEを使用

-- MySQLスタイル（動作しない）
-- SELECT IFNULL(column1, 'default');

-- PostgreSQLスタイル
SELECT COALESCE(column1, 'default');

実務でよく使うCOALESCEパターン集

ランキング表示での活用

-- スコアテーブル
CREATE TABLE scores (
    user_id INTEGER,
    game_id INTEGER,
    score INTEGER,
    bonus_score INTEGER
);

-- 総合スコアでランキング
SELECT 
    user_id,
    COALESCE(score, 0) + COALESCE(bonus_score, 0) AS total_score,
    RANK() OVER (ORDER BY COALESCE(score, 0) + COALESCE(bonus_score, 0) DESC) AS ranking
FROM scores;

集計関数との組み合わせ

-- 売上集計
CREATE TABLE monthly_sales (
    month DATE,
    region VARCHAR(50),
    sales_amount DECIMAL(12,2),
    target_amount DECIMAL(12,2)
);

-- 達成率の計算（0除算対策込み）
SELECT 
    region,
    SUM(COALESCE(sales_amount, 0)) AS total_sales,
    SUM(COALESCE(target_amount, 0)) AS total_target,
    CASE 
        WHEN SUM(COALESCE(target_amount, 0)) = 0 THEN 0
        ELSE ROUND(
            SUM(COALESCE(sales_amount, 0)) * 100.0 / 
            SUM(COALESCE(target_amount, 0)), 2
        )
    END AS achievement_rate
FROM monthly_sales
GROUP BY region;

動的なデフォルト値

-- 設定テーブルからデフォルト値を取得
WITH defaults AS (
    SELECT 
        'pending' AS default_status,
        0 AS default_priority
)
SELECT 
    t.id,
    COALESCE(t.status, d.default_status) AS status,
    COALESCE(t.priority, d.default_priority) AS priority
FROM tasks t
CROSS JOIN defaults d;

パフォーマンスの考慮点

インデックスとCOALESCE

-- インデックスが効かない例
CREATE INDEX idx_nullable_column ON table_name(nullable_column);

-- このクエリではインデックスが使われない可能性
SELECT * FROM table_name 
WHERE COALESCE(nullable_column, 'default') = 'value';

-- 改善案：部分インデックス
CREATE INDEX idx_not_null ON table_name(nullable_column) 
WHERE nullable_column IS NOT NULL;

-- または関数インデックス
CREATE INDEX idx_coalesce ON table_name(COALESCE(nullable_column, 'default'));

大量データでの注意点

-- 非効率な例（全行でCOALESCE実行）
SELECT 
    COALESCE(large_text_column, 'デフォルトの長いテキスト...') AS text
FROM large_table;

-- 効率的な例（必要な行だけ処理）
SELECT 
    CASE 
        WHEN large_text_column IS NULL THEN 'デフォルトの長いテキスト...'
        ELSE large_text_column
    END AS text
FROM large_table
WHERE condition = true;  -- 条件で絞り込み

よくあるミスと対処法

データ型の不一致

-- エラーになる例
SELECT COALESCE(integer_column, 'デフォルト');  -- 型が違う

-- 正しい例
SELECT COALESCE(integer_column::TEXT, 'デフォルト');
-- または
SELECT COALESCE(integer_column, 0);

空文字とNULLの混在

-- 空文字はNULLではない
SELECT COALESCE('', 'デフォルト');  -- 結果: '' (空文字)

-- 空文字もデフォルト値にしたい場合
SELECT 
    CASE 
        WHEN column_name IS NULL OR column_name = '' THEN 'デフォルト'
        ELSE column_name
    END AS result
-- または
SELECT COALESCE(NULLIF(column_name, ''), 'デフォルト');

配列やJSONでの落とし穴

-- 空配列はNULLではない
SELECT COALESCE(ARRAY[]::INTEGER[], ARRAY[1,2,3]);  -- 結果: {} (空配列)

-- 配列要素数で判定
SELECT 
    CASE 
        WHEN array_length(array_column, 1) IS NULL THEN ARRAY[1,2,3]
        ELSE array_column
    END AS result;

実践演習問題

問題1：社員情報の整形

-- テーブル作成
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    middle_name VARCHAR(50),
    preferred_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

-- 解答例：表示名の優先順位を実装
SELECT 
    id,
    COALESCE(
        preferred_name,
        CONCAT_WS(' ', first_name, middle_name, last_name),
        'Unknown Employee'
    ) AS display_name,
    COALESCE(department, '未配属') AS dept,
    COALESCE(salary, 0) AS salary
FROM employees;

問題2：在庫管理システム

-- 複数倉庫の在庫を統合
SELECT 
    product_id,
    product_name,
    COALESCE(warehouse_a_stock, 0) +
    COALESCE(warehouse_b_stock, 0) +
    COALESCE(warehouse_c_stock, 0) AS total_stock,
    COALESCE(
        minimum_stock,
        CASE 
            WHEN product_category = 'essential' THEN 100
            ELSE 10
        END
    ) AS required_minimum
FROM inventory_master;

まとめ – COALESCEでNULL値マスターになろう！

COALESCEの使い方について、基本から応用まで解説してきました。

重要ポイント：

  • COALESCEは最初の非NULL値を返す
  • デフォルト値の設定に最適
  • 計算や文字列結合でのNULL対策に必須
  • CASE文より簡潔に書ける
  • パフォーマンスに注意が必要な場合もある

使いどころ：

  • レポート作成でのNULL値表示
  • 計算処理でのエラー回避
  • データ移行での欠損値補完
  • API応答のデフォルト値設定
  • ユーザー設定のフォールバック

COALESCEをマスターすれば、NULL値に悩まされることなく、クリーンで堅牢なSQLを書けるようになります。

ぜひ実際のプロジェクトで活用してみてください！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました