WITH（共通テーブル式）の使い方
問い合わせに名前をつける
書き方
WITH 名前 AS (SELECT …) SELECT … FROM 名前; WITH RECURSIVE …（再帰）
長い問い合わせを段階に分けて書けます。複数書くときは , で区切ります。
よく使うオプション
| オプション | 意味 |
|---|---|
WITH 名前 AS (SELECT …) | 問い合わせに名前をつけ、続く SELECT で表として使う |
WITH a AS (…), b AS (…) | いくつも書くときは , で区切る（後のものは前のものを使える） |
WITH RECURSIVE | 自分自身を使う再帰の問い合わせ（組織図・階層など） |
例
WITH paid AS ( SELECT customer_id, SUM(total) AS amount FROM orders WHERE status = 'paid' GROUP BY customer_id ) SELECT c.name, p.amount FROM paid p JOIN customers c ON c.id = p.customer_id ORDER BY p.amount DESC;
客ごとの支払い額を paid という名前で先に作り、客の名前をつけて並べます。長い問い合わせを段階に分けて読めます。
WITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg FROM employees GROUP BY dept_id),
high AS (SELECT e.* FROM employees e JOIN dept_avg a ON a.dept_id = e.dept_id WHERE e.salary > a.avg)
SELECT COUNT(*) FROM high;部署の平均より給与の高い社員の数です。2 つ目の high が 1 つ目の dept_avg を使っています。
WITH RECURSIVE chain AS ( SELECT id, name, manager_id FROM employees WHERE id = 7 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN chain c ON e.id = c.manager_id ) SELECT name FROM chain;
社員 7 から上司・その上司…と、いちばん上までたどって出します。
よくある間違い・注意
- WITH でつけた名前は、その 1 つの文の中だけで使えます。次の文では使えません。
- WITH RECURSIVE の 2 つ目の SELECT が終わらない形（ぐるっと回るつながり）だと、いつまでも止まりません。
実際に打って練習する
SQL コースの Lv34「名前をつけて組み立てる WITH」で覚えます。ブラウザの中のシミュレーターで、本物と同じように打って確かめられます。
Lv1〜3 は、登録不要・無料で遊べます（それより先はブロンズ以上のプランで遊べます）。