CTE（WITH句）― クエリに名前を付けて組み立てる

導入

サブクエリを何段も入れ子にすると、カッコの中にカッコ…とだんだん読めなくなります。そこで、途中結果に名前を付けて、上から順に組み立てる書き方が CTE（Common Table Expression／共通テーブル式） です。WITH 名前 AS (...) と書き、あとの本体でその名前をテーブルのように使えます。

説明

下の例は「会員ごとの注文数」を user_orders という名前の一時的な表として定義し、本体でそれを users に LEFT JOIN しています。入れ子のサブクエリでも書けますが、CTE のほうが手順が上から読めるぶん、ぐっと分かりやすくなります。

WITH user_orders AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id
)
SELECT u.name, COALESCE(uo.cnt, 0) AS order_count
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

flowchart TB
    W["WITH user_orders AS (…)<br/>「会員ごとの注文数」に名前を付ける"] --> M["本体の SELECT で<br/>user_orders を表のように使う"]

CTE はカンマで区切って複数定義でき、後ろの CTE は前の CTE を参照できます。「集計してから、その結果をさらに絞り込む」といった多段の処理を、段階ごとに名前を付けて積み上げられるのが利点です。読みやすさは保守しやすさに直結します。複雑なクエリほど CTE の恩恵が大きくなります。

CTE は再帰（自分自身を参照する WITH RECURSIVE）も書け、組織図やカテゴリの階層をたどる処理に使えます。まずは「名前を付けて読みやすくする」基本用途を押さえましょう。

やってみよう

初期表示のクエリを実行し、全会員の注文数が（未注文の山本さんも 0 として）並ぶことを確認しましょう。WITH の中身だけを単独で実行すると、user_orders が「会員IDと注文数」の表であることも確かめられます。

演習

CTE を使って、平均価格より高い商品を求めてください。まず WITH avg_price AS (SELECT AVG(price) AS a FROM products) で平均を名前付けし、本体で products を avg_price と組み合わせて price > (SELECT a FROM avg_price) で絞ります。取り出す列は name と price。