SQL サブクエリ — NOT EXISTS・相関サブクエリの基礎

SELECT
  o.order_id,
  o.user_id,
  o.amount,
  (SELECT ROUND(AVG(o2.amount))    -- 相関SQ: そのユーザーの平均を返す
   FROM   orders o2
   WHERE  o2.user_id = o.user_id    -- 外側 o.user_id を参照（相関条件）
  ) AS user_avg
FROM   orders o
WHERE  o.amount > (               -- 各ユーザーの平均と比較して絞り込む
  SELECT AVG(o2.amount)
  FROM   orders o2
  WHERE  o2.user_id = o.user_id   -- WHERE でも同じ相関SQを使用
)
ORDER BY o.user_id, o.amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders o                  → 全行を1行ずつ処理
  2. WHERE o.amount > (相関SQ)        → 各行に対しサブクエリを実行
  3. SELECT ... user_avg            → 相関SQで各ユーザー平均を付与
  4. ORDER BY user_id, amount DESC  → 並び替え
  */

SELECT
  p.product_id,
  p.name,
  p.category,
  p.price
FROM   products p
INNER JOIN (                       -- JOIN の右辺に派生テーブルを配置
  SELECT
    category,
    MAX(price) AS max_price       -- カテゴリごとの最高価格を集計
  FROM   products
  GROUP BY category
) AS cat_max                      -- 派生テーブルには必ずエイリアスを付ける
  ON  p.category = cat_max.category  -- カテゴリが一致
  AND p.price    = cat_max.max_price  -- かつ価格がMAX値と一致
ORDER BY p.price DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. 派生テーブルを評価
  2. FROM products p         → productsを全件読み込む
  3. INNER JOIN ... ON ...   → category一致かつprice=MAX値の行のみ結合
  4. SELECT p.product_id...  → 必要列を選択
  5. ORDER BY p.price DESC   → price降順に並び替え
  */

-- ■ サブクエリ版（派生テーブル）
SELECT
  status,
  order_cnt,
  total_amount
FROM (
  SELECT
    status,
    COUNT(*)    AS order_cnt,   -- 件数を集計
    SUM(amount) AS total_amount -- 合計金額を集計
  FROM   orders
  GROUP BY status
) AS summary                    -- 派生テーブルに必ずエイリアスを付ける
WHERE  order_cnt >= 2           -- 集計後の件数で絞り込む
ORDER BY total_amount DESC;

-- ■ CTE版（WITH句）— 上と全く同じ結果を返す
WITH summary AS (               -- CTEの定義（名前: summary）
  SELECT
    status,
    COUNT(*)    AS order_cnt,
    SUM(amount) AS total_amount
  FROM   orders
  GROUP BY status
)
SELECT                           -- CTE名で参照（普通のテーブルと同様）
  status,
  order_cnt,
  total_amount
FROM   summary
WHERE  order_cnt >= 2
ORDER BY total_amount DESC;

/*
  実行順序（いずれも同じ論理評価順）:
  1. 集計クエリ（SQまたはCTE）を評価          → status ごとに集計
  2. FROM summary                → 集計結果を参照
  3. WHERE order_cnt のしきい値       → 条件で絞る
  4. SELECT ...                  → 3列を選択
  5. ORDER BY total_amount DESC  → 合計降順
  */

SELECT
  u.name,
  o.order_id,
  o.amount,
  o.ordered_at,
  (SELECT COUNT(*)                    -- 相関スカラーSQ: 全注文件数を付与
   FROM   orders o3
   WHERE  o3.user_id = u.user_id
  ) AS total_orders
FROM   users u
INNER JOIN orders o
  ON  u.user_id = o.user_id
WHERE  u.user_id IN (              -- ① WHERE IN + HAVING: 高額ユーザーを特定
  SELECT user_id
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
  HAVING SUM(amount) >= 10000     -- completedの合計が10000以上のuser_idリスト
)
  AND o.ordered_at = (            -- ② 相関スカラーSQ: 最新注文日と一致する行のみ
    SELECT MAX(o2.ordered_at)
    FROM   orders o2
    WHERE  o2.user_id = u.user_id
  )
ORDER BY total_orders DESC, o.amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. IN サブクエリを評価                                → HAVING付き集計でリストを生成
  2. FROM users INNER JOIN orders               → user_id で結合
  3. WHERE user_id IN (...)                     → 該当ユーザーに絞る
  4. AND o.ordered_at = (相関スカラーSQ)              → 各ユーザーの最新注文に絞る
  5. SELECT (相関スカラーSQ)                          → 全注文件数を付与
  6. ORDER BY total_orders DESC, o.amount DESC  → 件数→金額降順
  */