SQL練習室
← SQLガイド

SQLのWITH句(CTE)とは?基本構文と複数のCTEの使い方

WITH句は、検索の途中結果に名前を付けて、同じSQL文の中で使うための構文です。この名前付きの問い合わせをCTE(共通テーブル式)と呼びます。社員5人のデータで、検索を小さな段階に分けて書く方法を学びましょう。

例で使う表

employeesの1行は社員1人分です。salaryは月給(円)、NULLは未入力を表します。すべての例で同じ5人を使います。ORDER BYのない結果の行の並びは一例で、順序は保証されません。

employees:社員5人の月給
id(社員ID)name(氏名)department(部署)salary(月給・円)
1青木営業300000
2北野開発400000
3中央営業350000
4大和開発400000
5恵比寿サポートNULL

WITH(CTE)の基本構文

sql
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文です。

sql
WITH employee_list AS (
  SELECT *
  FROM employees
)
SELECT *
FROM employee_list;
実行結果
idnamedepartmentsalary
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を絞る処理とは違います。

sql
WITH department_totals AS (
  SELECT department, SUM(salary)
  FROM employees
  GROUP BY department
)
SELECT *
FROM department_totals
WHERE sum >= 650000;
実行結果
departmentsum
営業650000
開発800000

複数のCTEをカンマで区切る

営業の社員と開発の社員を別々のCTEにまとめ、開発の平均月給より月給が低い営業社員を選びます。WITHは先頭に1回だけ書き、CTE同士をカンマで区切ります。最後のCTEと本体のSELECTの間にはカンマを置きません。開発の平均は400000円で、営業の青木さん300000円、中央さん350000円の両方が条件を満たします。本体から2つのCTEをそれぞれ参照でき、平均の比較にはサブクエリで学んだ書き方を使います。

sql
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);
実行結果
idnamedepartmentsalary
1青木営業300000
3中央営業350000

前のCTEを次のCTEから参照する

known_salaryで給与入力済みの4人を選び、次のdepartment_totalsで部署別に集計します。後ろのCTEは、前に定義したCTEをFROMに書いて使えます。サポートの恵比寿さんは集計前に除くので、この結果にはサポートの行自体がありません。前の例ではNULLの合計に条件を付けましたが、ここでは集計する前の行を選んでいます。書いた段階に沿って読むと、どこで行が変わるかを追えます。

sql
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;
実行結果
departmentsum
営業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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。