-- 以降の例で使用するテーブル
CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(50)
);
 
CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    name          VARCHAR(100),
    department_id INTEGER REFERENCES departments(id),
    salary        DECIMAL(10, 2),
    hire_date     DATE
);
 
INSERT INTO departments VALUES (1, '営業'), (2, '開発'), (3, '人事');
INSERT INTO employees VALUES
    (101, '田中', 1, 450000, '2020-04-01'),
    (102, '鈴木', 2, 520000, '2019-07-15'),
    (103, '佐藤', 1, 380000, '2021-01-10'),
    (104, '高橋', NULL, 400000, '2022-03-01');  -- 部署未所属

-- 両方のテーブルに一致する行のみ返す
SELECT e.name AS employee, d.name AS department
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
 
-- 結果:
-- employee | department
-- ---------+-----------
-- 田中     | 営業
-- 鈴木     | 開発
-- 佐藤     | 営業
--
-- ※ 高橋（department_id=NULL）は除外
-- ※ 人事（社員なし）も除外

-- 複数の結合条件を使用
CREATE TABLE project_assignments (
    employee_id   INTEGER,
    department_id INTEGER,
    project_id    INTEGER,
    role          VARCHAR(50),
    start_date    DATE,
    PRIMARY KEY (employee_id, project_id)
);
 
-- 同じ部署に所属し、かつプロジェクトに配属された社員
SELECT
    e.name AS employee,
    d.name AS department,
    pa.role
FROM employees e
    INNER JOIN departments d
        ON e.department_id = d.id
    INNER JOIN project_assignments pa
        ON e.id = pa.employee_id
        AND e.department_id = pa.department_id  -- 複合結合条件
ORDER BY d.name, e.name;
 
-- 非等値結合（Non-Equi Join）の例
-- 給与が同じ部署の平均以上の社員を取得
SELECT e.name, e.salary, dept_avg.avg_salary
FROM employees e
    INNER JOIN (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    ) dept_avg
        ON e.department_id = dept_avg.department_id
        AND e.salary >= dept_avg.avg_salary;

-- 左テーブル（employees）の全行を保持
SELECT e.name AS employee, d.name AS department
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id;
 
-- 結果:
-- employee | department
-- ---------+-----------
-- 田中     | 営業
-- 鈴木     | 開発
-- 佐藤     | 営業
-- 高橋     | NULL        ← 一致なしでもNULLで表示
 
-- LEFT JOINで「一致しない行」だけ取得（ANTI JOINパターン）
SELECT e.name
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;
-- → 高橋（部署未所属の社員）

-- 実践パターン1: オプショナルなプロフィール情報の取得
SELECT
    u.id,
    u.name,
    u.email,
    p.avatar_url,
    p.bio,
    COALESCE(p.display_name, u.name) AS display_name
FROM users u
    LEFT JOIN user_profiles p ON u.id = p.user_id;
 
-- 実践パターン2: 集約と組み合わせた部署別社員数
SELECT
    d.name AS department,
    COUNT(e.id) AS employee_count,
    COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY employee_count DESC;
-- 結果: 人事部も0人として表示される
 
-- 実践パターン3: 直近の注文情報の結合
SELECT
    c.name AS customer,
    c.email,
    lo.last_order_date,
    lo.last_order_total
FROM customers c
    LEFT JOIN LATERAL (
        SELECT
            order_date AS last_order_date,
            total AS last_order_total
        FROM orders
        WHERE customer_id = c.id
        ORDER BY order_date DESC
        LIMIT 1
    ) lo ON TRUE;

-- RIGHT JOIN: 右テーブル（departments）の全行を保持
SELECT e.name AS employee, d.name AS department
FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.id;
 
-- 結果:
-- employee | department
-- ---------+-----------
-- 田中     | 営業
-- 佐藤     | 営業
-- 鈴木     | 開発
-- NULL     | 人事        ← 社員がいない部署もNULLで表示
 
-- FULL OUTER JOIN: 両方の全行を保持
SELECT e.name AS employee, d.name AS department
FROM employees e
    FULL OUTER JOIN departments d ON e.department_id = d.id;
 
-- 結果:
-- employee | department
-- ---------+-----------
-- 田中     | 営業
-- 佐藤     | 営業
-- 鈴木     | 開発
-- 高橋     | NULL        ← 左のみ
-- NULL     | 人事        ← 右のみ

-- 差分検出: 2つのデータソースの比較
-- テーブルAにあってBにない、Bにあって Aにない行を検出
 
SELECT
    COALESCE(a.product_id, b.product_id) AS product_id,
    a.stock AS warehouse_a_stock,
    b.stock AS warehouse_b_stock,
    CASE
        WHEN a.product_id IS NULL THEN 'Bにのみ存在'
        WHEN b.product_id IS NULL THEN 'Aにのみ存在'
        WHEN a.stock <> b.stock  THEN '数量不一致'
        ELSE '一致'
    END AS status
FROM warehouse_a a
    FULL OUTER JOIN warehouse_b b ON a.product_id = b.product_id
WHERE a.product_id IS NULL
   OR b.product_id IS NULL
   OR a.stock <> b.stock;
 
-- データ同期の差分チェック
SELECT
    COALESCE(src.id, dst.id) AS id,
    src.updated_at AS source_updated,
    dst.updated_at AS dest_updated,
    CASE
        WHEN dst.id IS NULL THEN 'INSERT'
        WHEN src.id IS NULL THEN 'DELETE'
        WHEN src.updated_at > dst.updated_at THEN 'UPDATE'
        ELSE 'SYNC'
    END AS action_needed
FROM source_table src
    FULL OUTER JOIN destination_table dst ON src.id = dst.id
WHERE dst.id IS NULL
   OR src.id IS NULL
   OR src.updated_at > dst.updated_at;

-- MySQL: FULL OUTER JOINのエミュレーション
SELECT e.name AS employee, d.name AS department
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id
 
UNION
 
SELECT e.name AS employee, d.name AS department
FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.id;
 
-- UNION ALL + 重複排除の最適化版
SELECT e.name AS employee, d.name AS department
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id
 
UNION ALL
 
SELECT e.name AS employee, d.name AS department
FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;  -- LEFT JOINと重複しない行のみ

-- CROSS JOIN: 全組み合わせ（直積）
-- 4社員 × 3部署 = 12行
SELECT e.name, d.name
FROM employees e
    CROSS JOIN departments d;
 
-- 実用例1: カレンダーテーブルの生成
SELECT
    y.year,
    m.month
FROM generate_series(2020, 2025) AS y(year)
    CROSS JOIN generate_series(1, 12) AS m(month)
ORDER BY y.year, m.month;
 
-- 実用例2: 全商品×全店舗の在庫マトリクス
SELECT
    p.name AS product,
    s.name AS store,
    COALESCE(i.quantity, 0) AS stock
FROM products p
    CROSS JOIN stores s
    LEFT JOIN inventory i ON i.product_id = p.id AND i.store_id = s.id;
 
-- 実用例3: 時間帯ごとの売上マトリクス
WITH hours AS (
    SELECT generate_series(0, 23) AS hour
),
days AS (
    SELECT generate_series(0, 6) AS dow,
           CASE generate_series(0, 6)
               WHEN 0 THEN '日' WHEN 1 THEN '月'
               WHEN 2 THEN '火' WHEN 3 THEN '水'
               WHEN 4 THEN '木' WHEN 5 THEN '金'
               WHEN 6 THEN '土'
           END AS day_name
)
SELECT
    d.day_name,
    h.hour,
    COALESCE(s.sale_count, 0) AS sales
FROM days d
    CROSS JOIN hours h
    LEFT JOIN (
        SELECT
            EXTRACT(DOW FROM sale_time) AS dow,
            EXTRACT(HOUR FROM sale_time) AS hour,
            COUNT(*) AS sale_count
        FROM sales
        GROUP BY 1, 2
    ) s ON d.dow = s.dow AND h.hour = s.hour
ORDER BY d.dow, h.hour;

-- 各部署の給与上位3名を取得
SELECT
    d.name AS department,
    top3.name AS employee,
    top3.salary
FROM departments d
    CROSS JOIN LATERAL (
        SELECT name, salary
        FROM employees
        WHERE department_id = d.id  -- 外側テーブルの列を参照
        ORDER BY salary DESC
        LIMIT 3
    ) top3;
 
-- LATERAL vs 相関サブクエリ: LATERAL の方がSELECT句で複数列を返せる
-- 相関サブクエリはSELECT句で1値のみ
SELECT
    d.name AS department,
    latest.order_date,
    latest.total_amount,
    latest.item_count
FROM departments d
    LEFT JOIN LATERAL (
        SELECT
            o.order_date,
            o.total_amount,
            COUNT(oi.id) AS item_count
        FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.department_id = d.id
        ORDER BY o.order_date DESC
        LIMIT 1
    ) latest ON TRUE;
 
-- 時系列データの直近N件取得
SELECT
    s.sensor_id,
    s.location,
    readings.reading_time,
    readings.value
FROM sensors s
    CROSS JOIN LATERAL (
        SELECT reading_time, value
        FROM sensor_readings
        WHERE sensor_id = s.sensor_id
        ORDER BY reading_time DESC
        LIMIT 5
    ) readings
ORDER BY s.sensor_id, readings.reading_time DESC;

-- SEMI JOIN: 注文がある顧客のみ取得（EXISTS使用）
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
 
-- 同等のSEMI JOIN（IN使用）
SELECT c.id, c.name
FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders);
 
-- ANTI JOIN: 注文がない顧客のみ取得（NOT EXISTS使用）
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
 
-- ANTI JOIN: LEFT JOIN + IS NULL パターン
SELECT c.id, c.name
FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
 
-- ※ パフォーマンス比較:
-- NOT EXISTS vs LEFT JOIN + IS NULL vs NOT IN
-- 一般的に NOT EXISTS が最も安定した性能
-- NOT IN は NULLがあると意図しない結果になる危険がある

-- 社員テーブルで上司と部下の関係を表現
CREATE TABLE staff (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INTEGER REFERENCES staff(id)
);
 
-- 自己結合で上司名を取得
SELECT
    s.name AS employee,
    m.name AS manager
FROM staff s
    LEFT JOIN staff m ON s.manager_id = m.id;
 
-- 同じ部署の社員ペアを列挙（自己結合）
SELECT
    e1.name AS employee_1,
    e2.name AS employee_2
FROM employees e1
    INNER JOIN employees e2
        ON e1.department_id = e2.department_id
        AND e1.id < e2.id;  -- 重複ペア防止
 
-- 同期入社（同じ年に入社）の社員ペア
SELECT
    e1.name AS emp1,
    e2.name AS emp2,
    EXTRACT(YEAR FROM e1.hire_date) AS hire_year
FROM employees e1
    INNER JOIN employees e2
        ON EXTRACT(YEAR FROM e1.hire_date) = EXTRACT(YEAR FROM e2.hire_date)
        AND e1.id < e2.id
ORDER BY hire_year;

-- 3テーブル結合: 注文 → 注文明細 → 商品
SELECT
    o.id AS order_id,
    o.order_date,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS subtotal
FROM orders o
    INNER JOIN customers c ON o.customer_id = c.id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
WHERE o.customer_id = 42
ORDER BY o.order_date DESC, p.name;
 
-- 5テーブル結合: 完全な注文情報の取得
SELECT
    o.id AS order_id,
    c.name AS customer,
    c.email,
    p.name AS product,
    cat.name AS category,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total,
    s.company AS shipping_company,
    s.tracking_number,
    o.status
FROM orders o
    INNER JOIN customers c ON o.customer_id = c.id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    LEFT JOIN categories cat ON p.category_id = cat.id
    LEFT JOIN shipments s ON o.id = s.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY o.order_date DESC;

-- JOINのON句にフィルタ条件を含める vs WHERE句
-- 挙動の違いに注意
 
-- パターン1: ON句にフィルタ → LEFT JOINで全部署が表示される
SELECT d.name, e.name, e.salary
FROM departments d
    LEFT JOIN employees e
        ON d.id = e.department_id
        AND e.salary > 400000;  -- JOINの条件として
-- → 人事部もNULLで表示、営業部の佐藤（380000）はNULLで表示
 
-- パターン2: WHERE句にフィルタ → 該当部署のみ
SELECT d.name, e.name, e.salary
FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
WHERE e.salary > 400000;  -- 結合後のフィルタ
-- → 人事部は表示されない（WHERE句でNULL > 400000がFALSE）
 
-- この違いはOUTER JOINで特に重要
-- INNER JOINでは結果は同じになる

-- PostgreSQL: EXPLAIN ANALYZEで実行計画を確認
EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
 
-- 出力例（Nested Loop の場合）:
-- Nested Loop  (cost=0.28..16.34 rows=3 width=64)
--   -> Seq Scan on employees e  (cost=0.00..1.04 rows=4 width=36)
--   -> Index Scan using departments_pkey on departments d
--        (cost=0.14..0.16 rows=1 width=36)
--        Index Cond: (id = e.department_id)
 
-- Hash Join を強制（テスト用、本番では非推奨）
SET enable_nestloop = off;
SET enable_mergejoin = off;
 
EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
 
-- 出力例（Hash Join の場合）:
-- Hash Join  (cost=1.07..2.15 rows=3 width=64)
--   Hash Cond: (e.department_id = d.id)
--   -> Seq Scan on employees e  (cost=0.00..1.04 rows=4 width=36)
--   -> Hash  (cost=1.03..1.03 rows=3 width=36)
--         -> Seq Scan on departments d  (cost=0.00..1.03 rows=3 width=36)
 
-- 設定を元に戻す
RESET enable_nestloop;
RESET enable_mergejoin;

-- Oracle: 旧式のOUTER JOIN構文（(+)記法）
-- 非推奨だがレガシーコードで頻出
SELECT e.name, d.name
FROM employees e, departments d
WHERE e.department_id = d.id(+);  -- LEFT JOIN相当
-- → 標準のLEFT JOIN構文を使うべき
 
-- SQL Server: CROSS APPLY / OUTER APPLY
-- LATERALに相当
SELECT d.name, top3.name, top3.salary
FROM departments d
CROSS APPLY (
    SELECT TOP 3 name, salary
    FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
) top3;
 
-- MySQL: STRAIGHT_JOIN（結合順序の強制）
SELECT STRAIGHT_JOIN e.name, d.name
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
-- → オプティマイザの結合順序選択を無効化

-- [NG] 全データをJOINしてからフィルタ
SELECT e.name, d.name, o.total
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    INNER JOIN orders o ON e.id = o.employee_id
WHERE o.order_date >= '2024-01-01'
  AND d.name = '営業';
 
-- [OK] サブクエリで先に絞り込み
SELECT e.name, d.name, o.total
FROM (
    SELECT * FROM employees WHERE department_id = 1
) e
    INNER JOIN departments d ON e.department_id = d.id
    INNER JOIN (
        SELECT * FROM orders WHERE order_date >= '2024-01-01'
    ) o ON e.id = o.employee_id;
-- ※ 実際にはオプティマイザが同等の最適化を行う場合が多い
-- ※ EXPLAIN ANALYZEで確認して判断すること
 
-- インデックスの作成
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_orders_employee_id ON orders(employee_id);
CREATE INDEX idx_orders_date ON orders(order_date);
 
-- 統計情報の更新
ANALYZE employees;
ANALYZE orders;
ANALYZE departments;

-- NULL同士は等しくならない（NULL = NULL → UNKNOWN → FALSE扱い）
SELECT * FROM table_a a
    INNER JOIN table_b b ON a.nullable_col = b.nullable_col;
-- → 両方がNULLの行は結合されない
 
-- NULLも含めて結合したい場合
SELECT * FROM table_a a
    INNER JOIN table_b b
        ON a.nullable_col = b.nullable_col
        OR (a.nullable_col IS NULL AND b.nullable_col IS NULL);
 
-- もしくは COALESCE を使用
SELECT * FROM table_a a
    INNER JOIN table_b b
        ON COALESCE(a.nullable_col, -1) = COALESCE(b.nullable_col, -1);
-- ※ センチネル値（-1）が実データに存在しないことを確認

-- 1:Nの関係でJOINすると行が増幅する
-- orders(1) : order_items(N) で注文テーブルの行が増える
 
-- [NG] 集約とJOINの組み合わせで二重カウント
SELECT
    d.name,
    SUM(o.total) AS dept_total  -- 重複して加算される！
FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    INNER JOIN orders o ON e.id = o.employee_id
    INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY d.name;
 
-- [OK] サブクエリで先に集約してからJOIN
SELECT
    d.name,
    order_totals.dept_total
FROM departments d
    INNER JOIN (
        SELECT e.department_id, SUM(o.total) AS dept_total
        FROM employees e
            INNER JOIN orders o ON e.id = o.employee_id
        GROUP BY e.department_id
    ) order_totals ON d.id = order_totals.department_id;

-- 多対多関係: 学生 ←→ 中間テーブル ←→ コース
CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR(100));
CREATE TABLE courses (id INTEGER PRIMARY KEY, title VARCHAR(100));
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id  INTEGER REFERENCES courses(id),
    enrolled_at DATE,
    PRIMARY KEY (student_id, course_id)
);
 
-- 全学生の履修コース一覧
SELECT
    s.name AS student,
    STRING_AGG(c.title, ', ' ORDER BY c.title) AS courses,
    COUNT(c.id) AS course_count
FROM students s
    LEFT JOIN enrollments e ON s.id = e.student_id
    LEFT JOIN courses c ON e.course_id = c.id
GROUP BY s.id, s.name
ORDER BY s.name;
 
-- 同じコースを履修している学生ペア
SELECT DISTINCT
    s1.name AS student1,
    s2.name AS student2,
    c.title AS shared_course
FROM enrollments e1
    INNER JOIN enrollments e2
        ON e1.course_id = e2.course_id
        AND e1.student_id < e2.student_id
    INNER JOIN students s1 ON e1.student_id = s1.id
    INNER JOIN students s2 ON e2.student_id = s2.id
    INNER JOIN courses c ON e1.course_id = c.id;

-- 非等値結合: 日付範囲で結合する
-- 為替レートテーブル（日次レートが不定期に更新される）
CREATE TABLE exchange_rates (
    currency VARCHAR(3),
    rate DECIMAL(10, 4),
    effective_date DATE
);
 
-- 各注文に対して、注文日時点の為替レートを適用
SELECT
    o.id AS order_id,
    o.order_date,
    o.amount_usd,
    er.rate,
    o.amount_usd * er.rate AS amount_jpy
FROM orders o
    INNER JOIN LATERAL (
        SELECT rate
        FROM exchange_rates
        WHERE currency = 'JPY'
          AND effective_date <= o.order_date
        ORDER BY effective_date DESC
        LIMIT 1
    ) er ON TRUE;

-- リスク: JOINで本来見えないデータが見えてしまう
-- ユーザーAは自分の注文のみ閲覧可能だが、
-- JOINで他のユーザーの情報が漏洩する可能性
 
-- [NG] テナント分離が不十分
SELECT o.*, c.name, c.email
FROM orders o
    INNER JOIN customers c ON o.customer_id = c.id;
-- → 他テナントの顧客情報も取得可能
 
-- [OK] 行レベルセキュリティ（RLS）またはWHERE句で制限
SELECT o.*, c.name, c.email
FROM orders o
    INNER JOIN customers c ON o.customer_id = c.id
WHERE o.tenant_id = current_setting('app.current_tenant')::INTEGER;
 
-- PostgreSQL: 行レベルセキュリティ
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

-- [NG] 動的JOINのテーブル名を文字列連結で構築
-- ユーザー入力: "employees; DROP TABLE users; --"
 
-- [OK] ホワイトリストでテーブル名を検証
-- アプリケーション側:
-- allowed_tables = {'employees', 'departments', 'projects'}
-- if table_name not in allowed_tables:
--     raise ValueError("Invalid table name")

-- NG: 旧式のカンマ結合（暗黙的CROSS JOIN + WHERE）
SELECT e.name, d.name
FROM employees e, departments d
WHERE e.department_id = d.id;
 
-- 問題点:
-- 1. WHERE句を忘れるとCROSS JOINになる
-- 2. 結合条件とフィルタ条件が混在して読みにくい
-- 3. OUTER JOINが表現できない
-- 4. テーブルが増えると結合条件の漏れが検出困難
 
-- OK: 明示的なJOIN構文
SELECT e.name, d.name
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;

-- NG: ループ内でJOINすべきクエリを個別実行
-- アプリケーション側の疑似コード:
-- for dept in get_all_departments():
--     employees = query("SELECT * FROM employees WHERE dept_id = ?", dept.id)
--     # 部署数 N に対して N+1 回のクエリ
 
-- OK: JOINで1回のクエリに
SELECT d.name AS department, e.name AS employee
FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
ORDER BY d.name, e.name;

-- NG: 使わないテーブルもJOINしている
SELECT e.name, e.salary
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    INNER JOIN locations l ON d.location_id = l.id  -- 使ってない！
WHERE d.name = '営業';
 
-- OK: 必要なテーブルのみJOIN
SELECT e.name, e.salary
FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = '営業';
 
-- もしくはEXISTSで軽量化
SELECT e.name, e.salary
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = e.department_id AND d.name = '営業'
);

-- NG: JOINで行が増えたのをDISTINCTで無理やり解消
SELECT DISTINCT e.name, e.department_id
FROM employees e
    INNER JOIN orders o ON e.id = o.employee_id;
-- → JOINの設計が間違っている可能性が高い
 
-- OK: EXISTSを使用してSEMI JOINに
SELECT e.name, e.department_id
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.employee_id = e.id
);

-- テーブル定義
CREATE TABLE authors (id INT PRIMARY KEY, name VARCHAR(100), country VARCHAR(50));
CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(200), author_id INT, published_year INT);
CREATE TABLE reviews (id INT PRIMARY KEY, book_id INT, rating INT, reviewer_name VARCHAR(100));

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), registered_at DATE);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, order_date DATE, status VARCHAR(20));
CREATE TABLE order_items (id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT, unit_price DECIMAL);
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), category VARCHAR(50));

CREATE TABLE employees_v2 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees_v2(id),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

# 設計判断の記録テンプレート
class ArchitectureDecisionRecord:
    """ADR (Architecture Decision Record) の作成"""
 
    def __init__(self, title: str):
        self.title = title
        self.context = ""
        self.decision = ""
        self.consequences = []
        self.alternatives = []
 
    def set_context(self, context: str):
        """背景と課題の記述"""
        self.context = context
        return self
 
    def set_decision(self, decision: str):
        """決定内容の記述"""
        self.decision = decision
        return self
 
    def add_consequence(self, consequence: str, positive: bool = True):
        """結果の追加"""
        self.consequences.append({
            'description': consequence,
            'type': 'positive' if positive else 'negative'
        })
        return self
 
    def add_alternative(self, name: str, reason_rejected: str):
        """却下した代替案の追加"""
        self.alternatives.append({
            'name': name,
            'reason_rejected': reason_rejected
        })
        return self
 
    def to_markdown(self) -> str:
        """Markdown形式で出力"""
        md = f"# ADR: {self.title}\n\n"
        md += f"## 背景\n{self.context}\n\n"
        md += f"## 決定\n{self.decision}\n\n"
        md += "## 結果\n"
        for c in self.consequences:
            icon = "✅" if c['type'] == 'positive' else "⚠️"
            md += f"- {icon} {c['description']}\n"
        md += "\n## 却下した代替案\n"
        for a in self.alternatives:
            md += f"- **{a['name']}**: {a['reason_rejected']}\n"
        return md

-- 方法1: LATERAL JOIN（PostgreSQL 9.3+, MySQL 8.0.14+）
SELECT c.*, latest_order.*
FROM customers c
    LEFT JOIN LATERAL (
        SELECT order_date, total
        FROM orders WHERE customer_id = c.id
        ORDER BY order_date DESC LIMIT 1
    ) latest_order ON TRUE;
 
-- 方法2: ROW_NUMBER() + CTE
WITH ranked AS (
    SELECT o.*, ROW_NUMBER() OVER (
        PARTITION BY customer_id ORDER BY order_date DESC
    ) AS rn
    FROM orders o
)
SELECT c.*, r.order_date, r.total
FROM customers c
    LEFT JOIN ranked r ON c.id = r.customer_id AND r.rn = 1;
 
-- 方法3: 相関サブクエリ（非推奨、大量データで遅い）
SELECT c.*,
    (SELECT MAX(order_date) FROM orders WHERE customer_id = c.id)
FROM customers c;