SQLのWITH句(CTE)とは?基本構文と複数のCTEの使い方
WITH句は、検索の途中結果に名前を付けて、同じSQL文の中で使うための構文です。この名前付きの問い合わせをCTE(共通テーブル式)と呼びます。社員5人のデータで、検索を小さな段階に分けて書く方法を学びましょう。
例で使う表
employeesの1行は社員1人分です。salaryは月給(円)、NULLは未入力を表します。すべての例で同じ5人を使います。ORDER BYのない結果の行の並びは一例で、順序は保証されません。
| id(社員ID) | name(氏名) | department(部署) | salary(月給・円) |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 2 | 北野 | 開発 | 400000 |
| 3 | 中央 | 営業 | 350000 |
| 4 | 大和 | 開発 | 400000 |
| 5 | 恵比寿 | サポート | NULL |
WITH(CTE)の基本構文
WITH employee_list AS (
SELECT *
FROM employees
)
SELECT *
FROM employee_list;WITHとASで検索結果に名前を付ける
WITH employee_list AS (...)で、カッコ内の検索結果をemployee_listという名前で使えるようにします。後ろのSELECTは、その名前をFROMに書いて読み取ります。ここでは全社員をそのまま取り出すので、元の5行・4列が返ります。ASの後ろにはSELECT全体をカッコで囲んで書きます。CTEだけでは結果を表示できず、後ろのSELECTまでが1つのSQL文です。
WITH employee_list AS (
SELECT *
FROM employees
)
SELECT *
FROM employee_list;| id | name | department | salary |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 2 | 北野 | 開発 | 400000 |
| 3 | 中央 | 営業 | 350000 |
| 4 | 大和 | 開発 | 400000 |
| 5 | 恵比寿 | サポート | NULL |
集計したCTEを外側のWHEREで絞り込む
部署ごとの合計をdepartment_totalsにまとめてから、合計65万円以上の部署を選びます。営業は300000 + 350000で650000円、開発は800000円なので残ります。サポートの合計はNULLのため、この条件を満たしません。CTEの結果にはSUM(salary)の既定列名sumがあり、外側では通常の列としてWHEREで参照できます。元の社員ごとのsalaryを絞る処理とは違います。
WITH department_totals AS (
SELECT department, SUM(salary)
FROM employees
GROUP BY department
)
SELECT *
FROM department_totals
WHERE sum >= 650000;| department | sum |
|---|---|
| 営業 | 650000 |
| 開発 | 800000 |
複数のCTEをカンマで区切る
営業の社員と開発の社員を別々のCTEにまとめ、開発の平均月給より月給が低い営業社員を選びます。WITHは先頭に1回だけ書き、CTE同士をカンマで区切ります。最後のCTEと本体のSELECTの間にはカンマを置きません。開発の平均は400000円で、営業の青木さん300000円、中央さん350000円の両方が条件を満たします。本体から2つのCTEをそれぞれ参照でき、平均の比較にはサブクエリで学んだ書き方を使います。
WITH sales_staff AS (
SELECT *
FROM employees
WHERE department = '営業'
), development_staff AS (
SELECT *
FROM employees
WHERE department = '開発'
)
SELECT *
FROM sales_staff
WHERE salary < (SELECT AVG(salary) FROM development_staff);| id | name | department | salary |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 3 | 中央 | 営業 | 350000 |
前のCTEを次のCTEから参照する
known_salaryで給与入力済みの4人を選び、次のdepartment_totalsで部署別に集計します。後ろのCTEは、前に定義したCTEをFROMに書いて使えます。サポートの恵比寿さんは集計前に除くので、この結果にはサポートの行自体がありません。前の例ではNULLの合計に条件を付けましたが、ここでは集計する前の行を選んでいます。書いた段階に沿って読むと、どこで行が変わるかを追えます。
WITH known_salary AS (
SELECT *
FROM employees
WHERE salary IS NOT NULL
), department_totals AS (
SELECT department, SUM(salary)
FROM known_salary
GROUP BY department
)
SELECT *
FROM department_totals;| department | sum |
|---|---|
| 営業 | 650000 |
| 開発 | 800000 |
WITH(CTE)でよくある間違い
CTEと本体をセミコロンで分けない
WITHの閉じカッコの直後にセミコロンを置くと、後ろのSELECTにつながりません。セミコロンは本体SELECTの末尾に置きます。CTE名が使えるのはその1文の中だけで、次のSQL文からは参照できません。
実際に1問解いてみる
WITH・AS
employeesの全行・全列をemployee_listというCTEにまとめ、そのCTEから全行・全列を取得してください。行順は問いません。
関連構文
WITH(CTE)についてよくある質問
- WITHとFROMのサブクエリの違いは?
- どちらも途中の検索結果を使えます。FROMのサブクエリは使う位置に書き、WITHは先頭で名前を付けてから参照します。段階が多い検索や、同じ途中結果を複数箇所から参照する検索ではWITHで意図を整理できます。
- WITHはテーブルを作成しますか?
- 永続的なテーブルは作成しません。CTE名はそのSQL文の中だけで使えます。別のSQL文から使う表を作成するCREATE TABLEとは役割が違います。
- WITH RECURSIVEも必要ですか?
- このページのように前のCTEを後ろから参照するだけなら不要です。CTEが自分自身の結果を参照し、階層などをたどる再帰的な問い合わせではWITH RECURSIVEを使います。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:WITHとSELECT — CTEの基本構文、複数CTEと前のCTEの参照
- PostgreSQL公式:CTEの実体化 — CTEの最適化と実体化