PostgreSQL COALESCE関数をWHERE句で使う完全ガイド｜NULL値処理の実践テクニック

「WHERE句でNULL値も含めて検索したい」 「NULLの場合はデフォルト値で比較したい」 「複数のカラムから最初の非NULL値で絞り込みたい」

こんな悩みを抱えていませんか？

PostgreSQLのCOALESCE関数は、NULL値を扱う最強の味方です。 特にWHERE句で使うことで、NULLを含む複雑な条件検索が簡潔に書けるようになります。

この記事では、COALESCE関数の基本から、WHERE句での実践的な使い方、パフォーマンスの考慮まで、実例たっぷりで解説します。 もうNULL値で悩むことはありません！

---

COALESCE関数の基本を理解する

COALESCE関数とは？

COALESCE関数は、引数の中から最初のNULLでない値を返す関数です。

基本構文：

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

動作の仕組み：

-- 基本的な例
SELECT COALESCE(NULL, NULL, 'Hello', 'World');
-- 結果: 'Hello' （最初の非NULL値）

-- 実用例
SELECT COALESCE(phone_number, mobile_number, '電話番号なし') AS 連絡先
FROM users;

NULLとの比較の問題

なぜCOALESCEが必要？

-- これは期待通りに動作しない！
SELECT * FROM users WHERE status = NULL;  -- 結果: 0行

-- NULLの比較にはIS NULLが必要
SELECT * FROM users WHERE status IS NULL;  -- 正しい

-- でも、NULLと値の両方を扱いたい時は？
-- COALESCEの出番！
SELECT * FROM users WHERE COALESCE(status, 'inactive') = 'inactive';

---

WHERE句でCOALESCEを使う基本パターン

パターン1：NULLをデフォルト値として扱う

-- 例：削除日がNULLまたは未来の日付のレコードを取得
SELECT * FROM products
WHERE COALESCE(deleted_at, '9999-12-31') > CURRENT_DATE;

-- 実際のテーブル例
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    deleted_at TIMESTAMP
);

-- NULLを0として扱って比較
SELECT * FROM products
WHERE COALESCE(price, 0) >= 100;

パターン2：複数カラムの優先順位検索

-- 優先度の高い連絡先から検索
SELECT * FROM customers
WHERE COALESCE(mobile_phone, home_phone, office_phone) LIKE '090%';

-- より実践的な例
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    mobile_phone VARCHAR(20),
    home_phone VARCHAR(20),
    office_phone VARCHAR(20)
);

-- いずれかの電話番号が特定の市外局番
SELECT 
    name,
    COALESCE(mobile_phone, home_phone, office_phone) AS primary_contact
FROM customers
WHERE COALESCE(mobile_phone, home_phone, office_phone) LIKE '03-%';

パターン3：条件付きNULL処理

-- 在庫がNULLの場合は0として、10個以上の商品を検索
SELECT * FROM inventory
WHERE COALESCE(stock_quantity, 0) >= 10;

-- 期限切れチェック（NULL = 無期限）
SELECT * FROM subscriptions
WHERE COALESCE(expires_at, '9999-12-31'::DATE) < CURRENT_DATE;

---

実践的な使用例

例1：検索フィルターの実装

-- 検索パラメータがNULLの場合は全件表示
CREATE OR REPLACE FUNCTION search_products(
    p_category_id INTEGER DEFAULT NULL,
    p_min_price DECIMAL DEFAULT NULL,
    p_max_price DECIMAL DEFAULT NULL
)
RETURNS TABLE(
    id INTEGER,
    name VARCHAR,
    price DECIMAL,
    category_id INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.name,
        p.price,
        p.category_id
    FROM products p
    WHERE 
        COALESCE(p_category_id, p.category_id) = p.category_id
        AND p.price >= COALESCE(p_min_price, 0)
        AND p.price <= COALESCE(p_max_price, 999999);
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM search_products(NULL, 100, 500);  -- 100-500円の全カテゴリ
SELECT * FROM search_products(1, NULL, NULL);   -- カテゴリ1の全商品

例2：階層的なデフォルト値

-- ユーザー設定 → グループ設定 → システムデフォルトの優先順位
CREATE TABLE user_settings (
    user_id INTEGER,
    setting_name VARCHAR(50),
    user_value VARCHAR(100),
    group_value VARCHAR(100),
    system_value VARCHAR(100)
);

-- 有効な設定値を取得
SELECT 
    user_id,
    setting_name,
    COALESCE(user_value, group_value, system_value) AS effective_value
FROM user_settings
WHERE COALESCE(user_value, group_value, system_value) = 'enabled';

例3：日付範囲の柔軟な検索

-- イベントテーブル
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    start_date DATE NOT NULL,
    end_date DATE  -- NULLの場合は1日イベント
);

-- 特定期間と重なるイベントを検索
SELECT * FROM events
WHERE 
    start_date <= '2024-12-31'
    AND COALESCE(end_date, start_date) >= '2024-01-01';

-- 本日開催中のイベント
SELECT * FROM events
WHERE 
    start_date <= CURRENT_DATE
    AND COALESCE(end_date, start_date) >= CURRENT_DATE;

---

複雑な条件での活用

CASE文との組み合わせ

-- 複雑な条件分岐
SELECT * FROM orders
WHERE COALESCE(
    CASE 
        WHEN status = 'urgent' THEN priority
        WHEN status = 'normal' THEN priority + 10
        ELSE priority + 20
    END,
    99  -- priority がNULLの場合
) <= 30;

サブクエリとの組み合わせ

-- 最新の更新日時で絞り込み
SELECT * FROM articles
WHERE COALESCE(
    updated_at,
    created_at,
    (SELECT MIN(created_at) FROM articles)  -- 最古の日付をデフォルトに
) >= CURRENT_DATE - INTERVAL '7 days';

JOIN条件での使用

-- NULL値を考慮したJOIN
SELECT 
    o.*,
    c.name AS customer_name
FROM orders o
LEFT JOIN customers c 
    ON COALESCE(o.customer_id, o.guest_customer_id) = c.id
WHERE COALESCE(o.customer_id, o.guest_customer_id) IS NOT NULL;

---

パフォーマンスの考慮

インデックスへの影響

問題：COALESCEを使うとインデックスが効かない場合がある

-- インデックスが効かない例
CREATE INDEX idx_status ON users(status);

-- これはインデックスを使えない
SELECT * FROM users WHERE COALESCE(status, 'inactive') = 'inactive';

-- 解決策1：部分インデックス
CREATE INDEX idx_status_with_null ON users(status) 
WHERE status IS NOT NULL;

CREATE INDEX idx_status_null ON users(id) 
WHERE status IS NULL;

-- 解決策2：条件を分割
SELECT * FROM users 
WHERE status = 'inactive' 
   OR status IS NULL;

式インデックスの活用

-- COALESCE式にインデックスを作成
CREATE INDEX idx_coalesce_status 
ON users(COALESCE(status, 'inactive'));

-- このクエリはインデックスを使える
SELECT * FROM users 
WHERE COALESCE(status, 'inactive') = 'inactive';

パフォーマンス比較

-- パフォーマンステスト用データ
CREATE TABLE performance_test (
    id SERIAL PRIMARY KEY,
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER
);

-- 100万行のテストデータ
INSERT INTO performance_test (col1, col2, col3)
SELECT 
    CASE WHEN random() > 0.3 THEN floor(random() * 1000) ELSE NULL END,
    CASE WHEN random() > 0.3 THEN floor(random() * 1000) ELSE NULL END,
    floor(random() * 1000)
FROM generate_series(1, 1000000);

-- インデックス作成
CREATE INDEX idx_coalesce_cols 
ON performance_test(COALESCE(col1, col2, col3));

-- パフォーマンス確認
EXPLAIN ANALYZE
SELECT * FROM performance_test
WHERE COALESCE(col1, col2, col3) = 500;

---

他のNULL処理関数との比較

COALESCE vs NULLIF

-- NULLIF: 特定の値をNULLに変換
SELECT NULLIF(status, '');  -- 空文字をNULLに

-- 組み合わせて使用
SELECT * FROM users
WHERE COALESCE(NULLIF(status, ''), 'active') = 'active';

COALESCE vs CASE

-- COALESCEで書ける場合
SELECT COALESCE(col1, col2, col3, 'default');

-- CASE文での同等の処理（冗長）
SELECT 
    CASE 
        WHEN col1 IS NOT NULL THEN col1
        WHEN col2 IS NOT NULL THEN col2
        WHEN col3 IS NOT NULL THEN col3
        ELSE 'default'
    END;

-- ただし、CASEの方が柔軟
SELECT 
    CASE 
        WHEN col1 IS NOT NULL AND col1 > 0 THEN col1
        WHEN col2 IS NOT NULL THEN col2 * 2
        ELSE 0
    END;

COALESCE vs IS DISTINCT FROM

-- NULL安全な比較
SELECT * FROM users
WHERE status IS NOT DISTINCT FROM NULL;

-- COALESCEを使った場合
SELECT * FROM users
WHERE COALESCE(status, 'null_marker') = 'null_marker';

---

よくある間違いと注意点

間違い1：COALESCEの評価順序

-- 間違い：すべての引数が評価される
SELECT COALESCE(
    expensive_function1(),  -- 実行される
    expensive_function2(),  -- 実行される
    'default'
);

-- 正しい：最初の非NULLで停止
SELECT COALESCE(
    simple_value,
    expensive_function(),  -- simple_valueがNULLの時のみ実行
    'default'
);

間違い2：型の不一致

-- エラーになる例
SELECT COALESCE(123, 'text');  -- 型が異なる

-- 解決策：型を揃える
SELECT COALESCE(123::TEXT, 'text');
SELECT COALESCE(123, 'text'::INTEGER);  -- これはエラー

間違い3：空文字とNULLの混同

-- 空文字はNULLではない
SELECT COALESCE('', 'default');  -- 結果: '' (空文字)

-- 空文字もデフォルト値にしたい場合
SELECT COALESCE(NULLIF(column_name, ''), 'default');

---

実用的なユーティリティ関数

汎用的な検索関数

-- NULL許容の範囲検索関数
CREATE OR REPLACE FUNCTION in_range(
    value ANYELEMENT,
    min_val ANYELEMENT,
    max_val ANYELEMENT
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN value >= COALESCE(min_val, value) 
       AND value <= COALESCE(max_val, value);
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM products
WHERE in_range(price, 100, 500);  -- 100-500の範囲
SELECT * FROM products
WHERE in_range(price, 100, NULL);  -- 100以上
SELECT * FROM products
WHERE in_range(price, NULL, 500);  -- 500以下

動的WHERE句の構築

-- 動的検索クエリ
CREATE OR REPLACE FUNCTION dynamic_search(
    filters JSONB
) RETURNS TABLE(id INTEGER, name VARCHAR, price DECIMAL) AS $$
DECLARE
    sql_query TEXT;
BEGIN
    sql_query := 'SELECT id, name, price FROM products WHERE 1=1';
    
    -- カテゴリフィルタ
    IF filters->>'category_id' IS NOT NULL THEN
        sql_query := sql_query || ' AND category_id = ' || (filters->>'category_id')::INTEGER;
    END IF;
    
    -- 価格フィルタ
    sql_query := sql_query || ' AND price >= ' || COALESCE((filters->>'min_price')::DECIMAL, 0);
    sql_query := sql_query || ' AND price <= ' || COALESCE((filters->>'max_price')::DECIMAL, 999999);
    
    RETURN QUERY EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

---

よくある質問（FAQ）

Q1：COALESCEとIFNULLの違いは？

A：IFNULLはMySQLの関数です

-- PostgreSQLではCOALESCEを使用
SELECT COALESCE(col1, 'default');

-- MySQLのIFNULL相当
-- PostgreSQLでIFNULLを使いたい場合は自作
CREATE OR REPLACE FUNCTION IFNULL(val1 ANYELEMENT, val2 ANYELEMENT)
RETURNS ANYELEMENT AS $$
    SELECT COALESCE(val1, val2);
$$ LANGUAGE SQL;

Q2：COALESCEで3つ以上の値を扱える？

A：はい、無制限に扱えます

SELECT COALESCE(col1, col2, col3, col4, col5, 'default');

Q3：パフォーマンスが悪い時の対策は？

A：以下の方法を試してください

-- 1. 式インデックスを作成
CREATE INDEX idx_expr ON table_name(COALESCE(col1, col2));

-- 2. 条件を分割
WHERE col1 = value OR (col1 IS NULL AND col2 = value)

-- 3. マテリアライズドビューを使用
CREATE MATERIALIZED VIEW mv_with_defaults AS
SELECT *, COALESCE(col1, col2, 'default') AS effective_value
FROM table_name;

---

まとめ：COALESCEでNULL値を制する

PostgreSQLのCOALESCE関数をWHERE句で使う方法について、重要なポイントをまとめます：

基本の使い方：

-- NULL値をデフォルト値として扱う
WHERE COALESCE(column, 'default') = 'value'

-- 複数カラムから優先順位で選択
WHERE COALESCE(col1, col2, col3) = 'value'

パフォーマンス対策：

1. 🔍 式インデックスの作成
2. 🔄 条件分割での書き換え
3. 📊 マテリアライズドビューの活用

ベストプラクティス：

• 型の一致を確認
• 空文字とNULLを区別
• インデックス戦略を考慮
• 適切な場面で使用

よく使うパターン：

-- 検索フィルター
WHERE column >= COALESCE(min_param, column)

-- 削除フラグ
WHERE COALESCE(deleted_at, '9999-12-31') > CURRENT_DATE

-- 優先順位検索
WHERE COALESCE(primary, secondary, default) LIKE pattern

COALESCE関数を使いこなすことで、NULL値を含む複雑な検索条件もシンプルに記述できます。 この記事で学んだテクニックを活用して、より柔軟で保守性の高いSQLを書きましょう！