【SQL】NULLを含むカラムでの条件分岐・フィルターの書き方｜三値論理・COALESCE・NULLIF・JOIN完全ガイド

-- NULLを比較演算子で比較してもヒットしない
SELECT * FROM users WHERE email = NULL;   -- 0件
SELECT * FROM users WHERE email != NULL;  -- 0件

-- NULLの判定には IS NULL / IS NOT NULL を使う
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- scoreがNULLの行は UNKNOWN AND TRUE = UNKNOWN → 除外される
SELECT * FROM students
WHERE score >= 80 AND grade = 'A';

-- scoreがNULLでもORで救える（UNKNOWN OR TRUE = TRUE）
SELECT * FROM students
WHERE score >= 80 OR score IS NULL;

-- NOT (score < 60) はNULLをヒットさせない
-- NOT UNKNOWN = UNKNOWN のため
SELECT * FROM students
WHERE NOT (score < 60);  -- NULLのscoreは除外される

-- NULLを含む行を拾いたい場合は明示的に OR IS NULL
SELECT * FROM students
WHERE NOT (score < 60) OR score IS NULL;

-- productsテーブル
-- id: 1, 2, 3, 4, 5

-- deleted_idsテーブルにNULLが含まれる場合
-- id: 2, 4, NULL

-- 「2と4以外のproductを取得したい」つもりが...
SELECT * FROM products
WHERE id NOT IN (SELECT id FROM deleted_ids);
-- → 結果: 0件 !! NULLが含まれるためすべてUNKNOWNになる

-- 内部的には以下の条件に展開される:
-- id NOT IN (2, 4, NULL)
-- = id <> 2 AND id <> 4 AND id <> NULL
-- = id <> 2 AND id <> 4 AND UNKNOWN
-- = UNKNOWN → 全行除外

-- NOT EXISTS はNULLの影響を受けない
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM deleted_ids d
    WHERE d.id = p.id
);

-- または WHERE NULLを除外した上でNOT IN
SELECT * FROM products
WHERE id NOT IN (
    SELECT id FROM deleted_ids
    WHERE id IS NOT NULL  -- NULLを除外してからNOT IN
);

-- 最初の非NULLを返す（全DBMS対応）
SELECT
    name,
    COALESCE(mobile_phone, home_phone, '連絡先未登録') AS contact
FROM customers;

-- WHERE句でCOALESCEを使う
-- discount_priceがNULLのときはlist_priceで比較
SELECT * FROM products
WHERE COALESCE(discount_price, list_price) < 5000;

-- MySQL: IFNULL
SELECT name, IFNULL(score, 0) AS score FROM students;

-- Oracle: NVL
SELECT name, NVL(score, 0) AS score FROM students;

-- Oracle: NVL2(col, NULLでない場合の値, NULLの場合の値)
SELECT name, NVL2(score, '採点済み', '未採点') AS status FROM students;

-- SQL Server: ISNULL
SELECT name, ISNULL(score, 0) AS score FROM students;

-- ゼロ除算の防止（NULLIF で分母がゼロの場合はNULLに）
SELECT
    department,
    total_sales,
    ROUND(total_sales / NULLIF(target_sales, 0) * 100, 1) AS achievement_rate
FROM sales_summary;

-- 空文字をNULLとして扱う
SELECT
    name,
    NULLIF(remarks, '') AS remarks  -- 空文字→NULL
FROM customers;

-- COALESCE + NULLIF の組み合わせ
-- 空文字もNULLも「未入力」として表示
SELECT
    name,
    COALESCE(NULLIF(remarks, ''), '未入力') AS remarks
FROM customers;

-- 基本：NULLの有無で表示を切り替える
SELECT
    user_id,
    name,
    CASE
        WHEN email IS NULL THEN 'メール未登録'
        WHEN email LIKE '%@gmail.com' THEN 'Gmail'
        ELSE 'その他メール'
    END AS email_status
FROM users;

-- NG: CASE WHEN email = NULL ← 絶対にヒットしない
SELECT
    CASE WHEN email = NULL THEN '未登録'  -- 常にFALSE扱い
         ELSE email
    END
FROM users;  -- BAD: IS NULLを使うこと

-- 必須項目の入力状況を分類
SELECT
    user_id,
    CASE
        WHEN email IS NOT NULL AND phone IS NOT NULL THEN '完全登録'
        WHEN email IS NOT NULL AND phone IS NULL     THEN 'メールのみ'
        WHEN email IS NULL     AND phone IS NOT NULL THEN '電話のみ'
        ELSE                                              '未登録'
    END AS registration_status
FROM users;

-- ELSE を省略するとNULLが返る
SELECT
    CASE WHEN score >= 80 THEN '合格'
         WHEN score >= 60 THEN '補講'
         -- ELSE を書かなかった場合: NULL が返る
    END AS result
FROM students;

-- NULLにしたくない場合はELSEを明示
SELECT
    CASE WHEN score >= 80 THEN '合格'
         WHEN score >= 60 THEN '補講'
         ELSE '不合格'
    END AS result
FROM students;

-- 注文のないcustomerを取得（ANTI JOINパターン）
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;  -- JOINに失敗→NULLの行のみ

-- 右テーブルの非NULL行のみ（INNER JOINと同等）
SELECT c.customer_id, c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL;

-- ON句のフィルター: LEFT JOINの結合条件として使う
-- → customerは全件残り、直近30日の注文がない場合はo.*がNULL
SELECT c.customer_id, c.name, o.order_date
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL 30 DAY;

-- WHERE句のフィルター: JOINの後でフィルター
-- → 直近30日に注文していないcustomerは除外される（INNER JOIN相当）
SELECT c.customer_id, c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL 30 DAY;

-- NULLのdepartmentは「NULLグループ」として集計される
SELECT
    department,           -- NULLのdepartmentも1行として出る
    COUNT(*) AS total,    -- NULLのdepartmentの人数
    COUNT(score) AS scored_count,  -- NULLのscoreは除外
    AVG(score) AS avg_score        -- NULLのscoreは除外して平均
FROM employees
GROUP BY department;

-- NULLグループを除外したい場合はWHEREで先にフィルタ
SELECT department, COUNT(*)
FROM employees
WHERE department IS NOT NULL
GROUP BY department;

-- 平均スコアがNULL（全員スコア未入力）のグループを除外
SELECT department, AVG(score) AS avg_score
FROM employees
GROUP BY department
HAVING AVG(score) IS NOT NULL;

-- 「未入力0件のグループ」のみを抽出するパターン
SELECT department, COUNT(*) AS total, COUNT(score) AS scored
FROM employees
GROUP BY department
HAVING COUNT(*) = COUNT(score);  -- 全員スコアあり

-- センサーデータ（NULLは計測失敗）
-- Oracle: LAG ... IGNORE NULLS
SELECT
    measured_at,
    temperature,
    LAST_VALUE(temperature IGNORE NULLS)
        OVER (ORDER BY measured_at
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS filled_temperature
FROM sensor_data
ORDER BY measured_at;

-- PostgreSQL には IGNORE NULLS がないため CTE で代替
WITH filled AS (
    SELECT
        measured_at,
        temperature,
        COUNT(temperature) OVER (ORDER BY measured_at) AS grp
    FROM sensor_data
)
SELECT
    measured_at,
    temperature,
    FIRST_VALUE(temperature) OVER (PARTITION BY grp ORDER BY measured_at)
        AS filled_temperature
FROM filled
ORDER BY measured_at;

SELECT
    COUNT(*)                          AS total_users,
    COUNT(email)                      AS email_filled,
    COUNT(phone)                      AS phone_filled,
    COUNT(CASE WHEN email IS NULL AND phone IS NULL THEN 1 END)
                                      AS both_missing,
    ROUND(100.0 * COUNT(email) / COUNT(*), 1)
                                      AS email_fill_rate
FROM users;

-- 空文字とNULLが混在するカラムを一括クレンジング
UPDATE users
SET
    phone   = NULLIF(TRIM(phone), ''),
    address = NULLIF(TRIM(address), '')
WHERE phone = '' OR address = '';

-- 表示時に空文字・NULLを両方「未登録」として扱う
SELECT
    name,
    COALESCE(NULLIF(TRIM(phone), ''), '未登録') AS phone
FROM users;

-- 「年齢20歳以上」のユーザーを取得（NULLは除外）
SELECT * FROM users WHERE age >= 20;

-- 「年齢20歳以上 または 年齢不明」を取得
SELECT * FROM users WHERE age >= 20 OR age IS NULL;

-- 「年齢18〜65歳の範囲外 または 不明」を取得
SELECT * FROM users
WHERE NOT (age BETWEEN 18 AND 65) OR age IS NULL;

-- 売上サマリーで売上がなかった月をゼロ表示
SELECT
    m.month_str,
    COALESCE(s.total_sales, 0) AS total_sales,
    COALESCE(s.order_count, 0) AS order_count
FROM (
    -- 全月マスタ
    SELECT DATE_FORMAT(DATE_ADD('2024-01-01', INTERVAL n MONTH), '%Y-%m')
           AS month_str
    FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
          UNION SELECT 10 UNION SELECT 11) nums
) m
LEFT JOIN (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_str,
           SUM(amount)  AS total_sales,
           COUNT(*)     AS order_count
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) s ON m.month_str = s.month_str
ORDER BY m.month_str;