SQLのwith句についてまとめています。
SQLのwithをSELECT文で使うサンプル
with句を使うと、入り組んだサブクエリを単純なクエリに分解して、スッキリ記述できるというメリットがあります。
【関連記事】
▶SQLのWITH句で可読性アップ！再帰共通表式でサクッとSQL文を書く
with句はクエリ実行中のみ有効な一時テーブルを作ります。サブクエリ結果に名前をつけて、メインクエリで参照することができます。なお、with句を使った副問合せの定義は、共通テーブル式（CTE）と呼ばれます。
特にサブクエリを同時に２つ以上使用するような複雑なクエリで威力を発揮します。
各DBMSのSELECT文でwith句を使う例を紹介します。
Oracleのwithの構文
WITH query_name ([c_alias [, c_alias]...]) AS (subquery) [search__clause] [cycle_clause] [, query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...
query_nameは、副問合せブロックにつける名前です。副問合せが複数存在する場合に、query_nameを指定して参照するんですね。副問合せの名前はインライン・ビューまたは一時表とし扱うことが可能です。
以下は、部署のコストをdept_costs、部署のコストの平均をavg_costと副問合せに名前をつけ、平均よりもコストが多い部署名を抽出する例です。
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;
DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 304500
Shipping 156400
SQL Serverのwithの構文
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
参考）WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft Docs
SQL Serverはwith句で複数の副問合せの指定が可能です。副問合せごとに名前（common_table_expression）をつけ、カラム名の指定も可能です。
以下は、販売員（SalesPersonID）ごとの年間の販売注文数の合計を出力する例です。
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
MySQLのwithの構文
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
共通テーブル式（副問合せブロック）には、名前がつけられます。上記の構文ではcte_nameです。複数の共通テーブル式の定義が可能で、別の共通テーブル式の参照が可能です。
以下は、table1とtable2のselect文で定義した２つの共通テーブル式cte1とcte2を結合する例です。
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
PostgreSQLのwithの構文
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
参考）Postgresql公式ドキュメント：WITH問い合わせ（共通テーブル式）
上記はPostgreSQLでwithを使ったクエリの例です。サブクエリにてregional_salesとtop_regiionsという２つの共通テーブル式を定義し、地域ごとの製品と売上数量、売上金額を抽出しています。
withを使って副問合せを記述すると、複雑な問い合わせを単純なクエリに分解することができる点です。
また、withを使って再帰的問い合わせの記述も可能です。
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
上記は部品（part）と構成部品（sub_part）を再帰的に問い合わせ、製品すべてのの部品、構成部品を抽出する例です。
SQLのwith句のまとめ
- with句で共通テーブル式を定義して、複雑なサブクエリを単純なクエリに分解して記述可能
- with句では、複数の共通テーブル式を記述可能
- with句で、再帰的問い合わせを記述できる
