【Oracle】NULL処理関数完全ガイド｜NVL・NVL2・COALESCE・NULLIF・LNNVL の違いと使い分け・NOT IN の落とし穴まで解説

-- WHERE 条件が UNKNOWN になると行が返らない
SELECT * FROM employees WHERE manager_id = NULL;     -- 0行（NULL=NULLはUNKNOWN）
SELECT * FROM employees WHERE manager_id IS NULL;    -- NULL行が返る（正しい書き方）

-- NULL を含む算術演算
SELECT 100 + NULL  FROM dual;   -- NULL
SELECT 100 - NULL  FROM dual;   -- NULL
SELECT 100 * NULL  FROM dual;   -- NULL
SELECT 100 / NULL  FROM dual;   -- NULL

-- 文字列連結の Oracle 独自挙動（他 RDBMS と異なる）
SELECT 'Hello' || NULL || ' World' FROM dual;   -- 'Hello World'（NULLが無視される）
-- ※ PostgreSQL/MySQL では NULL を含む連結は NULL になる

-- 基本: NULL を 0 に置換
SELECT employee_id,
       NVL(commission_pct, 0) AS commission
FROM employees;

-- 文字列の NULL を別の文字列に置換
SELECT employee_id,
       NVL(TO_CHAR(manager_id), '（トップ）') AS manager
FROM employees;

-- NVL を使った計算（NULL が混在しても正しく集計できる）
SELECT employee_id,
       salary + NVL(commission_pct, 0) * salary AS total_income
FROM employees;
-- ※ commission_pct が NULL の場合、NVL なしだと total_income が NULL になる

-- WHERE 句での使用（NULL を含む列に条件をかける場合）
SELECT * FROM products
WHERE NVL(discontinued, 'N') = 'N';  -- discontinued が NULL のものも含める

-- 基本: commission_pct が NULL かどうかで表示を切り替える
SELECT employee_id,
       NVL2(commission_pct,
            'コミッションあり',    -- NULL でない場合
            'コミッションなし')    -- NULL の場合
       AS commission_status
FROM employees;

-- NVL2 と NVL の比較
-- NVL:  NULL → default、NULL 以外 → 元の値（そのまま）
-- NVL2: NULL → null_val、NULL 以外 → not_null_val（変換も可能）

-- 数値変換例: NULL なら 0、NULL でなければ 1
SELECT employee_id,
       NVL2(manager_id, 1, 0) AS has_manager
FROM employees;

-- CASE WHEN と同等（NVL2 の方が簡潔）
SELECT employee_id,
       CASE WHEN commission_pct IS NOT NULL THEN 'あり'
            ELSE 'なし'
       END AS commission_status
FROM employees;

-- 優先順位のある NULL 処理: preferred_name → nickname → last_name の順で最初の非NULLを使う
SELECT employee_id,
       COALESCE(preferred_name, nickname, last_name) AS display_name
FROM employees;

-- NVL との比較（引数2個のときは同じ動作）
SELECT NVL(commission_pct, 0)      AS nvl_result,
       COALESCE(commission_pct, 0) AS coalesce_result
FROM employees;
-- 結果は同じだが COALESCE は SQL 標準なので移植性が高い

-- 複数テーブルの列をフォールバック的に参照
SELECT o.order_id,
       COALESCE(o.delivery_address, c.default_address, '住所未登録') AS ship_to
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- COALESCE は最初の非NULLが見つかった時点で残りの評価を打ち切る（短絡評価）
-- 重い処理のデフォルト値を後ろに置くと、前の値が非NULLなら実行されない

SELECT product_id,
       COALESCE(
           cached_price,                           -- まずキャッシュ価格を確認
           (SELECT price FROM price_master         -- なければマスターから取得（重い）
            WHERE product_id = p.product_id),
           0                                       -- それもなければ 0
       ) AS effective_price
FROM products p;

-- NVL は両方必ず評価するため、上記のようなパターンは COALESCE が有利

-- ゼロ除算を回避する（NULLIF で分母が 0 のとき NULL にする）
SELECT department_id,
       total_sales / NULLIF(headcount, 0) AS avg_sales_per_person
FROM dept_summary;
-- headcount が 0 なら NULLIF(headcount, 0) = NULL → 割り算が NULL になる（ORA-01476 を回避）

-- ダミー値（-1 や 'N/A'）を NULL に変換して正規化
SELECT product_id,
       NULLIF(price, -1)      AS price,      -- -1 は未設定のダミー値 → NULL に変換
       NULLIF(category, 'N/A') AS category   -- 'N/A' → NULL に変換
FROM raw_import;

-- NULLIF と DECODE の比較
-- NULLIF(v, x) ≡ CASE WHEN v = x THEN NULL ELSE v END
-- DECODE(v, x, NULL, v) と同義だが NULLIF の方が意図が明確

-- 更新時に特定の値を NULL にリセット
UPDATE employees
SET manager_id = NULLIF(p_manager_id, 0)   -- 0 が入力された場合は NULL（トップ扱い）
WHERE employee_id = p_emp_id;

-- LNNVL なしの書き方（冗長）
SELECT * FROM employees
WHERE department_id != 10
   OR department_id IS NULL;   -- department_id が NULL の行も含めたい

-- LNNVL を使った書き方（簡潔）
SELECT * FROM employees
WHERE LNNVL(department_id = 10);
-- department_id = 10 が FALSE（10 以外）または UNKNOWN（NULL）の行を返す

-- 実務パターン: バッチ処理で「処理済み（is_processed=1）以外」を取得
SELECT * FROM work_queue
WHERE LNNVL(is_processed = 1);
-- is_processed が 0 または NULL（未設定）の行をすべて取得

-- サンプルデータ
CREATE TABLE depts (dept_id NUMBER);
INSERT INTO depts VALUES (10);
INSERT INTO depts VALUES (20);
INSERT INTO depts VALUES (NULL);   -- NULL が1件含まれている

-- IN は問題なく動作する
SELECT * FROM employees
WHERE department_id IN (SELECT dept_id FROM depts);
-- → department_id が 10 または 20 の行が返る（NULLはスキップ）

-- NOT IN はサブクエリに NULL が1件でもあると全件が除外される
SELECT * FROM employees
WHERE department_id NOT IN (SELECT dept_id FROM depts);
-- → 0件（！）
-- 理由: NOT IN (10, 20, NULL) は
--       department_id != 10 AND department_id != 20 AND department_id != NULL
--       最後の "department_id != NULL" が UNKNOWN になるため
--       全体が UNKNOWN → WHERE 条件を満たさない → 全件除外

-- 対処法1: WHERE 句で NULL を除外する
SELECT * FROM employees
WHERE department_id NOT IN (
    SELECT dept_id FROM depts WHERE dept_id IS NOT NULL
);

-- 対処法2: NOT EXISTS を使う（NULL に対して安全）
SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM depts d
    WHERE d.dept_id = e.department_id   -- NULL = NULL は FALSE なのでマッチしない
);
-- NOT EXISTS は NULL を自動的に安全に処理するため推奨

-- テストデータ（salary に NULL が含まれる）
-- emp_id: 1, salary: 300000
-- emp_id: 2, salary: 400000
-- emp_id: 3, salary: NULL    ← 給与未設定

SELECT COUNT(*)           AS total_rows,     -- 3（NULL も含めて行数カウント）
       COUNT(salary)      AS salary_count,   -- 2（NULL を除いた件数）
       SUM(salary)        AS total_salary,   -- 700000（NULL を無視）
       AVG(salary)        AS avg_salary,     -- 350000（700000 / 2 ← NULL を除いた件数で割る）
       AVG(NVL(salary,0)) AS avg_with_zero   -- 233333（700000 / 3 ← 全員で割りたい場合）
FROM employees;
-- ポイント: AVG(salary) は NULL を除いた行の平均になる点に注意
-- 「全員の平均」が必要なら NVL(salary, 0) でゼロ補完してから AVG

-- GROUP BY での NULL: Oracle では NULL もひとつのグループとして扱われる
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
ORDER BY department_id NULLS LAST;  -- NULL グループを最後に

-- DECODE は NULL == NULL を TRUE として扱う（通常の = とは異なる）
SELECT employee_id,
       DECODE(manager_id,
              NULL, 'トップ管理職',      -- manager_id が NULL の場合
              manager_id)               -- NULL でない場合はそのまま
       AS manager_label
FROM employees;

-- CASE WHEN では IS NULL が必要（通常のルール）
SELECT employee_id,
       CASE WHEN manager_id IS NULL THEN 'トップ管理職'
            ELSE TO_CHAR(manager_id)
       END AS manager_label
FROM employees;

-- どちらも同じ結果を返す。CASE WHEN の方が意図が明確でSQL標準準拠のため推奨

-- デフォルト: Oracle では昇順（ASC）で NULL は最後、降順（DESC）で NULL は最初
SELECT employee_id, salary
FROM employees
ORDER BY salary ASC;   -- NULL が最後（Oracleのデフォルト）

-- 明示的に制御
SELECT employee_id, salary
FROM employees
ORDER BY salary ASC  NULLS FIRST;   -- NULL を先頭に
ORDER BY salary ASC  NULLS LAST;    -- NULL を末尾に（昇順デフォルトと同じ）
ORDER BY salary DESC NULLS FIRST;   -- 降順でも NULL を先頭に（降順デフォルトと同じ）
ORDER BY salary DESC NULLS LAST;    -- 降順で NULL を末尾に

-- 分析関数でも NULLS FIRST / NULLS LAST が使える
SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC NULLS LAST) AS salary_rank
FROM employees;