はじめに

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

環境

1
2
3
4

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句を使う

1
2
3
4

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

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

1
2
3
4
5
6

+----------------+-------------+-------------+---------------+----------------+------------+
| 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をサブクエリとして何回も使用する場合などに有効

1
2
3

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

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

1
2
3
4
5

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

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

参考

おわりに

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