SQLのWith句を使ってみる

はじめに

SQLのWith句を使ってみたいと思ったので備忘録として書いておく。

環境

MacOS Ventura
Docker version 24.0.6, build ed223bc
Docker Compose version v2.21.0-desktop.1
MySQL 8.1.0

準備

今回、検証するにあたって、Docker を使用し、実際にWith句を実行できるかを試す。
下記のリポジトリに、データベースの勉強用リポジトリとして色々入れているので一緒に試せるようにした。
https://github.com/katsuobushiFPGA/db-study

検証

上記のリポジトリを使用すると、test データベースに多くのテーブルができている。
これらのテーブルを使用して、With句が使えることを試す。

With句を使う

with temp AS (
    select * from reservations
)
select * from temp;

上記を実行すると下記が得られる。

+----------------+-------------+-------------+---------------+----------------+------------+
| reservation_id | customer_id | room_number | check_in_date | check_out_date | total_cost |
+----------------+-------------+-------------+---------------+----------------+------------+
|              1 |           1 | 101         | 2023-09-10    | 2023-09-15     |     500.00 |
+----------------+-------------+-------------+---------------+----------------+------------+
1 row in set (0.00 sec)

With句は、クエリの実行中にのみ有効な一時テーブルを構築してくれる。
この場合、tempと言う名前で、 select * from reservations のSQLを実行した結果を一時テーブルとして構築する。

https://dev.mysql.com/doc/refman/8.0/ja/with.html

使い所

可読性のメリット

例えば、下記のような複雑なSQLをサブクエリとして何回も使用する場合などに有効

select * from reservations 
where total_cost > 250 
and customer_id IN (1,2);

このSQLをFROM句に何回もセットしたい場合に、下記としてSQLを定義する。

with temp AS (
  select * from reservations 
  where total_cost > 250 
  and customer_id IN (1,2);
)

後は、使用したい箇所で、 select * from tempなどで使用すれば良い。

参考

13.2.15 WITH (共通テーブル式) | MySQL 8.0 リファレンスマニュアル
https://dev.mysql.com/doc/refman/8.0/ja/with.html

おわりに

With句を使うとサブクエリ内の可読性が上がるため、使う機会があれば使用していきたい。
そもそもサブクエリを使うレベルで複雑なSQLを組む機会がないのだが…