【Oracle】NVL / NVL2 / COALESCE / NULLIF 完全ガイド｜NULL置換の使い分け・暗黙型変換・短絡評価・実務パターンまで解説

-- 構文: NVL(expr1, expr2)
-- expr1 が NULL なら expr2 を返す。NULL でなければ expr1 をそのまま返す

-- 数値: salary が NULL なら 0 に置き換え
SELECT employee_id, NVL(salary, 0) AS salary FROM employees;

-- 文字列: middle_name が NULL なら '(なし)' に置き換え
SELECT NVL(middle_name, '(なし)') AS middle_name FROM employees;

-- 日付: end_date が NULL なら現在日時に置き換え
SELECT NVL(end_date, SYSDATE) AS end_date FROM projects;

-- 構文: NVL2(expr1, expr_not_null, expr_null)
-- expr1 が NULL でなければ expr_not_null を返す
-- expr1 が NULL なら expr_null を返す

-- commission_pct が NULL なら '固定給'、そうでなければ '歩合あり'
SELECT employee_id,
       NVL2(commission_pct, '歩合あり', '固定給') AS pay_type
FROM employees;

-- bonus が NULL なら 0、そうでなければ bonus * 1.1（10%増し）
SELECT employee_id,
       NVL2(bonus, bonus * 1.1, 0) AS adjusted_bonus
FROM employees;

-- 構文: COALESCE(expr1, expr2, expr3, ... , default)
-- 左から順に評価し、最初の非 NULL 値を返す

-- 携帯番号 → 自宅番号 → '連絡先なし' の優先順で取得
SELECT employee_id,
       COALESCE(mobile_phone, home_phone, '連絡先なし') AS contact
FROM employees;

-- NVL のネストを COALESCE で置き換え
-- 冗長: NVL(mobile_phone, NVL(home_phone, '連絡先なし'))
-- 簡潔: COALESCE(mobile_phone, home_phone, '連絡先なし')

-- NVL: expr2 が常に評価される（サブクエリも実行される）
SELECT NVL(name, (SELECT MAX(name) FROM employees)) FROM dual;
-- → name が NULL でなくても、サブクエリは実行される

-- COALESCE: 最初の非 NULL で停止（サブクエリは実行されない）
SELECT COALESCE(name, (SELECT MAX(name) FROM employees)) FROM dual;
-- → name が NULL でなければ、サブクエリは実行されない

-- 構文: NULLIF(expr1, expr2)
-- expr1 = expr2 なら NULL を返す。異なれば expr1 をそのまま返す

-- '(不明)' という文字列を NULL に変換
SELECT NULLIF(department_name, '(不明)') AS dept_name FROM departments;

-- 0 を NULL に変換（ゼロ除算の防止に便利）
SELECT total_amount / NULLIF(quantity, 0) AS unit_price
FROM order_details;
-- quantity が 0 なら NULL を返す → ORA-01476（ゼロ除算）を防止

-- SUM は NULL 行を無視する（0 として計算されない）
-- commission_pct: 10, NULL, 20, NULL → SUM = 30（NULL を無視）
SELECT SUM(commission_pct) FROM employees;

-- NULL を 0 として合計したい場合
SELECT SUM(NVL(commission_pct, 0)) FROM employees;
-- 10 + 0 + 20 + 0 = 30（結果は同じだが意味が明確）

-- AVG で影響が出るケース（NVL の有無で結果が変わる）
-- NVL なし: AVG = (10 + 20) / 2 = 15（NULL 行を除外して平均）
SELECT AVG(commission_pct) FROM employees;

-- NVL あり: AVG = (10 + 0 + 20 + 0) / 4 = 7.5（全行で平均）
SELECT AVG(NVL(commission_pct, 0)) FROM employees;

-- COUNT(*): NULL を含む全行をカウント
SELECT COUNT(*) FROM employees;              -- 4（全行）

-- COUNT(col): NULL 以外の行のみカウント
SELECT COUNT(commission_pct) FROM employees;  -- 2（NULL を除外）

-- COUNT で NULL 行数を数える
SELECT COUNT(*) - COUNT(commission_pct) AS null_count FROM employees;

-- NULL を含む計算は結果が NULL になる
SELECT 100 + NULL AS result FROM dual;   -- NULL
SELECT 100 * NULL AS result FROM dual;   -- NULL

-- NVL で NULL を 0 に置き換えて計算
SELECT
    employee_id,
    salary + NVL(bonus, 0) + NVL(allowance, 0) AS total_pay
FROM employees;

-- ゼロ除算の防止（NULLIF）
SELECT order_amount / NULLIF(order_count, 0) AS avg_amount
FROM order_summary;

-- || 演算子: NULL が含まれると結果が NULL になることがある
-- Oracle では NULL || 'abc' = 'abc'（Oracle 独自仕様）
-- しかし明示的に NVL で処理する方が安全
SELECT
    last_name || ' ' || NVL(first_name, '') AS full_name
FROM employees;

-- COALESCE で優先順位付きの連結
SELECT
    COALESCE(nickname, first_name, 'ユーザー') || 'さん' AS display_name
FROM users;

-- commission_pct が NULL の行を 0 に更新
UPDATE employees
SET commission_pct = 0
WHERE commission_pct IS NULL;

-- NVL を使ったパターン（NULLでない値はそのまま残す）
UPDATE employees
SET commission_pct = NVL(commission_pct, 0);
-- 結果は同じだが、WHERE なしで全行を処理するため件数が多くなる

-- Oracle のデフォルト: ASC では NULL は末尾、DESC では NULL は先頭

-- NULLS FIRST / NULLS LAST で明示制御（推奨）
SELECT * FROM employees ORDER BY commission_pct ASC NULLS LAST;
SELECT * FROM employees ORDER BY commission_pct DESC NULLS LAST;

-- NVL を使ってソート順を制御する代替方法
-- NULL を最後にする（大きな値に置き換え）
SELECT * FROM employees ORDER BY NVL(commission_pct, 99999) ASC;

-- DECODE は NULL を「等しい」として扱える（= 演算子とは異なる）
SELECT DECODE(commission_pct, NULL, '固定給', '歩合あり') FROM employees;
-- これは NVL2 と同じ動作

-- NVL + DECODE の組み合わせ
SELECT NVL(
    DECODE(status, 'A', '有効', 'I', '無効'),
    '不明'
) AS status_name
FROM users;