WITH — 長いクエリを名前で分解する

入れ子のサブクエリは読みにくい

サブクエリを重ねると、 内側から読まないと意味が分からない クエリになります。

SELECT name, total FROM (
  SELECT c.name, SUM(x.amount) AS total FROM (
    SELECT o.customer_id, p.price * o.quantity AS amount
    FROM orders o JOIN products p ON o.product_id = p.id
  ) x JOIN customers c ON c.id = x.customer_id
  GROUP BY c.name
) ORDER BY total DESC;

処理の順番は 内側 → 外側 なのに、 文字は 外側 → 内側 に並んでいます。 これを

上から順に読める形 に直すのが WITH です。

WITH の書き方

WITH 名前 AS (
  SELECT ...
)
SELECT ... FROM 名前;

共通テーブル式 (CTE / Common Table Expression) と呼びます。 名前を付けた

一時的な表を先に作り、 あとの SELECT からふつうの表のように使えます。

さっきのクエリを書き直すとこうなります。

WITH order_amount AS (
  SELECT o.customer_id, p.price * o.quantity AS amount
  FROM orders o
  JOIN products p ON o.product_id = p.id
)
SELECT c.name, SUM(a.amount) AS total
FROM order_amount a
JOIN customers c ON c.id = a.customer_id
GROUP BY c.name
ORDER BY total DESC;

「まず注文ごとの金額を出す」 → 「それを顧客ごとに合計する」 と、

考えた順にそのまま書けています。

CTE は複数つなげられる

カンマで区切ると いくつでも並べられ、 後のものから前のものを参照できます。

WITH sold AS (
  SELECT product_id, SUM(quantity) AS qty
  FROM orders GROUP BY product_id
),
named AS (
  SELECT p.name, s.qty
  FROM sold s JOIN products p ON p.id = s.product_id
)
SELECT name, qty FROM named ORDER BY qty DESC, name;

処理を 1 段ずつ名前付きで積み上げられるので、 途中の段だけ差し替えて

確かめる といったこともできます。 デバッグのときは

末尾を SELECT * FROM sold; に変えれば その段の中身だけ見られます。

同じ結果を 2 回使う

サブクエリだと 2 回書く必要があるものも、 CTE なら 1 回で済みます。

WITH cat_avg AS (
  SELECT category, AVG(price) AS avg_price
  FROM products GROUP BY category
)
SELECT p.name, p.category, p.price
FROM products p
JOIN cat_avg a ON p.category = a.category
WHERE p.price > a.avg_price;

「カテゴリごとの平均を出して、 その平均を超える商品を探す」。 相関サブクエリで

書くと 行ごとに平均を計算し直しますが、 CTE なら 平均は 1 回だけです。

よくあるつまずき

- WITH のあとにカンマではなくセミコロンを打つ — CTE を並べる区切りはカンマ、

文の終わりだけがセミコロンです。

- CTE の中で ORDER BY しても意味がないことがある — 順番が必要なのは

最終的な SELECT です。 CTE 内の並べ替えは 保証されません。

- 名前が既存のテーブルと衝突する — CTE 名が優先されるので、 意図せず

実テーブルではなく CTE を見てしまいます。 分かりやすい別名を付けます。

実務での使いどころ

集計を何段も重ねるレポート、 条件の異なる複数の母集団を突き合わせる分析、

ウィンドウ関数で付けた順位をさらに絞り込む処理 — CTE を使うと

「読める SQL」 になります。 レビューを通しやすくなるのも実利です。