【超便利】SQLのWITH句を使って可読性をアップしよう

「同じようなサブクエリを何度も書いてしまってSQLが無駄に長くなってしまう…」
「分析用に簡易的なテーブルを作成したい…」

SQLを書いていて、このような経験はありませんか？

この問題を解決してくれるのがSQLのWITH句です。SQLのWITH句とは一言で言うと、クエリの中に新しくテーブルを作成することができる文法です。

本記事ではSQLのWITH句とは何か、どんな場面で使うと便利になるのか、具体的な使い方について解説していきます。

サブクエリって何？という方は先に下記の記事をご覧ください。

2021年12月2日【SQL】副問合せ（サブクエリ）の使い方を初心者向けに解説

SQLのWITH句とは

改めて、SQLのWITH句はクエリ内で新しいテーブルを作成できる文法です。新しいテーブルと言っても本当にデータベースにテーブルが作成されてしまう訳ではありません。

あくまでも、今作成しているクエリの中だけで使える仮のテーブルを作成できるという意味です。最大の特徴は、WITH句によって作成したテーブルはクエリ内で何度でも使い回しができる点です。

これにより、SQLの可読性を上げられるというメリットがあります。

テキストだけではイメージが付きにくいかもしれないので、サンプルデータを見ながら具体的な使用例を見ていきましょう。

サンプルデータ

下記のとおり、商品情報を管理するproductsテーブルと、取引情報を管理するtransactionsテーブルを用意します。

id	name	category	price
1	化粧水	スキンケア	1000
2	クリーム	スキンケア	1500
3	パック	スキンケア	1500
4	ファンデーション	メイク	4480
5	口紅	メイク	1980

productsテーブル

id	product_id	orders	created_at
1	2	3	2022-05-01
2	4	2	2022-05-14
3	5	1	2022-05-23
4	1	2	2022-06-20
5	2	2	2022-06-30
6	4	10	2022-07-04
7	3	1	2022-07-15

transactionsテーブル

transactionsテーブルのordersカラムは注文数を表しています。

WITH句の使い方

WITH句を使って独自のテーブルを作る基本構文が下記です。

SQL

WITH テーブル名称 AS (
  SELECT カラム名
  FROM テーブル名
);

WITH句を使って作成したテーブルは、同じクエリ内で自由に使い回すことが可能です。

具体例を見ていきましょう。

サンプルデータを用いて、それぞれの商品の累計売上を算出するクエリを作成します。サブクエリを使ってクエリを作成する場合と、WITH句を使ってクエリを作成する場合、それぞれ解説していきます。

サブクエリを使った場合

サブクエリを使った場合のクエリは以下になります。

SQL

SELECT
  name,
  price * total_orders AS sales
FROM (
  SELECT
    product_id,
    SUM(orders) AS total_orders
  FROM transactions
  GROUP BY product_id
) t
INNER JOIN products AS p
ON t.product_id = p.id
ORDER BY sales DESC;

考え方の流れは下記です。

FROM句の中でサブクエリを使用して商品ごとの注文数を算出
productsテーブルをJOINする
productsテーブルのpriceカラムとサブクエリ内で定義したtotal_ordersカラムを掛け算して売上を算出
最後にORDER BY句を使って売上が高い順に並び替える

実行結果

name	sales
ファンデーション	53760
クリーム	7500
化粧水	2000
口紅	1980
パック	1500

商品の累計売上が高い順にデータを取得できました。

では次にWITH句を使って同じ実行結果を得る方法を見ていきます。

WITH句を使った場合

WITH句を使った場合のクエリが以下になります。

SQL

WITH ordersByProducts AS (
  SELECT
    product_id,
    SUM(orders) AS total_orders
  FROM transactions
  GROUP BY product_id
)

SELECT
  name,
  price * total_orders AS sales
FROM ordersByProducts AS o
INNER JOIN products AS p
ON o.product_id = p.id
ORDER BY sales DESC;

考え方の流れは下記です。

WITH句を使って商品ごとの注文数を管理するordersByProductsテーブルを作成
WITH句で定義したordersByProductsテーブルとproductsテーブルをJOINする
ordersByProductsテーブルのtotal_ordersカラムとproductsテーブルのpriceカラムを掛け算して売上を算出
最後にORDER BY句を使って売上が高い順に並び替える

ポイントはWITH句を使ってordersByProductsテーブルを作成している点です。このように、WITH句を使うことでクエリ内で新しくテーブルを作成することができます。

今回はサブクエリを1回しか使用していませんが、何度も同じようなサブクエリを書く必要がある場合、WITH句を使うことでクエリを簡略化できるというメリットがあります。

また、テーブルを作成することで情報を整理できて、クエリが読みやすくなるというメリットもあると思います。

実行結果はサブクエリを使ってもWITH句を使っても同じになります。

WITH句を複数使う方法

複数回使用する場合は、WITH句同士をカンマ区切りで繋げます。

先ほどのクエリを複数回WITH句を使って書き換えてみます。

SQL

WITH ordersByProducts AS (
  SELECT
    product_id,
    SUM(orders) AS total_orders
  FROM transactions
  GROUP BY product_id
),

salesByProducts AS (
  SELECT
    name,
    price * total_orders AS sales
  FROM ordersByProducts AS o
  INNER JOIN products AS p
  ON o.product_id = p.id
)

SELECT *
FROM salesByProducts;

商品ごとの注文数を管理するordersByProductsテーブルと、商品ごとの売上を管理するsalesByProductsテーブルの2つのテーブルをWITH句を使って定義しています。

WITH句内で必要な処理を全て行なっているので、最後にシンプルなSELECT文でデータを取得すればOKです。

このように、何度でもWITH句でテーブルを作成して情報を整理することができます。複雑なクエリになればなるほどWITH句を使うメリットも大きくなるので、頻繁にクエリを書く人は使いこなせるようにしておくととても便利です。