第4章: 主要構文と論理処理順序 — DDL/DML/DCL/TCLとNULLの3値論理

-- ❌ NG: WHERE句で「SELECTで作ったエイリアス」は参照できない
SELECT price * 1.1 AS price_with_tax
FROM products
WHERE price_with_tax > 1000;  -- ERROR: "price_with_tax" doesn't exist yet

-- ✅ OK: サブクエリかCTEで回避する
SELECT price_with_tax
FROM (
  SELECT price * 1.1 AS price_with_tax FROM products
) t
WHERE price_with_tax > 1000;

-- ✅ あるいはWHEREで式を直接書く
SELECT price * 1.1 AS price_with_tax
FROM products
WHERE price * 1.1 > 1000;

-- ✅ ORDER BY では SELECT のエイリアスを参照できる
-- (ORDER BY は SELECT より後に実行されるから)
SELECT price * 1.1 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;

-- 例: users(id, name, dept_id), departments(id, name)
-- LEFT JOIN: 部署未所属のユーザーも残したい
SELECT u.name, d.name AS dept
FROM users u
LEFT JOIN departments d ON u.dept_id = d.id;
-- 結果: dept_id が NULL or 存在しない d.id のユーザーも残り、d.name は NULL

-- FULL JOIN: ユーザーも部署も漏らしたくない(存在しない側はNULL)
SELECT u.name, d.name AS dept
FROM users u
FULL JOIN departments d ON u.dept_id = d.id;
-- MySQL には FULL JOIN がないので UNION で代替:
-- (LEFT JOIN の結果) UNION (LEFT JOIN を逆向きにした結果)

-- SELF JOIN: 同僚(同じdept_id)の組を一覧化
SELECT e1.name AS employee, e2.name AS coworker
FROM employees e1
JOIN employees e2
  ON e1.dept_id = e2.dept_id
 AND e1.id < e2.id;  -- 同じ組を2回数えない工夫

-- NULLのアンチパターン
SELECT * FROM orders WHERE cancel_reason != 'fraud';
-- ↑ cancel_reason が NULL の行は「詐欺ではない」はずなのに結果から消える!

-- 正しい書き方
SELECT * FROM orders
WHERE cancel_reason != 'fraud' OR cancel_reason IS NULL;
-- または COALESCE で NULL を代替値に
SELECT * FROM orders WHERE COALESCE(cancel_reason, '') != 'fraud';

-- NULL を集約関数はスキップする(COUNT(*)は含めるがCOUNT(col)は除外)
SELECT COUNT(*) FROM t;        -- 全行数
SELECT COUNT(col) FROM t;      -- col が NULL でない行数
SELECT AVG(col) FROM t;        -- NULL を除外して平均
SELECT SUM(col) FROM t;        -- NULL を除外して合計
-- 全部NULLなら AVG/SUM は NULL(0ではない)を返す

-- 詰まりポイント④: LEFT JOIN + WHERE vs LEFT JOIN + ON
-- ❌ WHERE に右側の条件を書くと OUTER → INNER に劣化する
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;
-- ↑ orders がない user は o.total が NULL なので WHERE で消える
-- → 結果的に INNER JOIN と同じになる

-- ✅ 右側の追加条件は ON 句に置く
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o
  ON u.id = o.user_id
 AND o.total > 1000;
-- ↑ LEFT JOIN が先に実行され、users 全員が残る
-- orders が無い or 1000以下のユーザーも o.total = NULL で出現