1つのSQL文の中に、重複するサブクエリが何度も登場すると可読性を下げる原因となります。そこで積極的に使用したいのがSQLのWITH句。MySQLでも8.0で使用できるようになり、知名度を一気に上げました。
今回の記事ではSQLのWITH句とは何かについて詳しく解説をしつつ、SQLのWITH句を使ったハンズオンを実施していきます。
- SQL WITH句は、再帰共通表式を作るもの
- 再起共通表式とは、式の中で何度も呼び出せる即席テーブルのこと
- WITH句を使ってハンズオンを体験してみる
SQLのWITH句とは？
SQLのWITH句とは、1つのSQL文の中で共通した表式（テーブル）を簡略的に呼び出すことができる句のことです。そのため、SQLのWITH句で呼び出したテーブルのことを再帰共通表式（テーブル）と呼びます。
言葉だけではイメージがしづらいかと思うので、実際にSQLのWITH句を使用した例を見てみましょう。
前提として、以下のようなテーブルがあるとします。
+--------+-------+ | name | price | +--------+-------+ | Banana | 100 | | Apple | 200 | +--------+-------+
このテーブルの中から、nameカラムだけを取り出した再帰共通表式をWITH句を使って生成して見ましょう。
mysql> WITH food_name AS ( SELECT name from foods) SELECT * FROM food_name;
以下が出力結果です。
+--------+ | name | +--------+ | Banana | | Apple | +--------+
foodsテーブルからnameカラムだけを取り出した「food_name」という再帰共通表式を作成することができました。WITH句から始めるSQL文において「food_name」という再帰共通表式は何度でも使用することができます。そのためサブクエリ（副問い合わせ）を使用して、何度も同じテーブル呼び出しをしなくてよく、可読性が高まるというわけです。
SQLのWITH句を使ってみよう
では、実際にSQLのWITH句を使っていきましょう。
こちらではMySQLというデータベースを使用していきます。MySQLではバージョン8.0からWITH句が導入されていますので、バージョンを確認してからハンズオンに入っていきましょう。
準備
まずはMySQLを使用するための準備をしていきます。
MySQLのインストール・接続
まだMySQLをインストールしていない場合は、インストールしましょう。
$ brew install mysql
MySQLのインストールが終了したら、接続します。
$ sudo mysql -u root -p
データベースの作成
次にデータベースをMySQL上に作成し、使用するデータベースを指定しましょう。
mysql> CREATE DATABASE potepan; Query OK, 1 row affected (0.00 sec) mysql> USE potepan;
これでMySQLを使用する準備は完了しました。
テーブルの作成
まずは状態テーブル・商品テーブルの2つを作成します。状態テーブルの「状態」1の時は「賞味期限前」、2の時は「賞味期限切れ」という形で商品を管理していきます。
そして商品コードごとに、賞味期限切れの商品数と賞味期限前の商品数を月ごとに表示していきましょう。
mysql> CREATE TABLE 状態(商品_No INT, 賞味期限_年 INT, 賞味期限_月 INT, 状態 INT); Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE 商品(商品_No INT, 商品_コード INT); Query OK, 0 rows affected (0.01 sec)
以下が作成されたテーブルです。
状態テーブル
+------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+-------+ | 商品_No | int(11) | YES | | NULL | | | 賞味期限_年 | int(11) | YES | | NULL | | | 賞味期限_月 | int(11) | YES | | NULL | | | 状態 | int(11) | YES | | NULL | | +------------------+---------+------+-----+---------+-------+
商品テーブル
+------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+-------+ | 商品_No | int(11) | YES | | NULL | | | 商品_コード | int(11) | YES | | NULL | | +------------------+---------+------+-----+---------+-------+
レコードの作成
テーブルにレコードを挿入していきましょう。
mysql> INSERT INTO 状態 VALUES(0001, 2019, 12, 1),(0002, 2018, 9, 2),(0003, 2012, 5, 2); Query OK, 3 rows affected (0.01 sec)
mysql> INSERT INTO 商品 VALUES(0001, 221590), (0002, 221590), (0003, 981224); Query OK, 3 rows affected (0.00 sec)
これでテーブルは以下の状態になりました。
状態テーブル
mysql> SELECT * FROM 状態; +-----------+------------------+------------------+--------+ | 商品_No | 賞味期限_年 | 賞味期限_月 | 状態 | +-----------+------------------+------------------+--------+ | 1 | 2019 | 12 | 1 | | 2 | 2018 | 9 | 2 | | 3 | 2012 | 5 | 2 | +-----------+------------------+------------------+--------+
商品テーブル
mysql> SELECT * FROM 商品; +-----------+------------------+ | 商品_No | 商品_コード | +-----------+------------------+ | 1 | 221590 | | 2 | 221590 | | 3 | 981224 | +-----------+------------------+
WITH check_table AS (
SELECT
商品.商品_コード,
状態.賞味期限_年,
状態.賞味期限_月,
状態.状態
FROM
状態 INNER JOIN 商品 ON 状態.商品_No = 商品.商品_No),
SHOUHIN AS (
SELECT check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月,
COUNT(*) cnt
FROM
check_table
GROUP BY
check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月),
KIGEN_MAE AS (
SELECT check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月,
COUNT(*) cnt
FROM check_table
WHERE
check_table.状態 = 1
GROUP BY
check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月),
KIGEN_GO AS(
SELECT check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月, COUNT(*) cnt
FROM check_table
WHERE check_table.状態 = 2
GROUP BY
check_table.商品_コード,
check_table.賞味期限_年,
check_table.賞味期限_月)
SELECT SHOUHIN.商品_コード,
SHOUHIN.賞味期限_年, SHOUHIN.賞味期限_月,
SHOUHIN.cnt 商品数
FROM SHOUHIN LEFT JOIN
KIGEN_MAE ON
SHOUHIN.商品_コード = KIGEN_MAE.商品_コード AND
SHOUHIN.賞味期限_年 = KIGEN_MAE.賞味期限_年 AND
SHOUHIN.賞味期限_月 = KIGEN_MAE.賞味期限_月
LEFT JOIN
KIGEN_GO ON
SHOUHIN.商品_コード = KIGEN_GO.商品_コード AND
SHOUHIN.賞味期限_年 = KIGEN_GO.賞味期限_年 AND
SHOUHIN.賞味期限_月 = KIGEN_GO.賞味期限_月;
非常に長いですが、ポイントとして最初にWITH句で生成したcheck_tableがその後、何度も呼び出されているという点に注目しましょう。WITH句を使うと、サブクエリを入力しなくても再帰的に共通テーブルが呼び出せるというわけです。
まとめ
今回の記事ではSQLのWITH句とは何かについて詳しく解説をしつつ、SQLのWITH句を使ったハンズオンを実施していきました。
同じテーブルを何度も呼び出さなければならない場合、サブクエリを使用するよりも、1度呼び出せば何度も共通表式として呼び出せるWITH句がおすすめです。積極的にSQL文の中で使用し、可読性の高い式を記述できるようにしましょう。
上記の例ではWITH句の利便性を十分に感じられなかったかもしれません。次にご紹介する例では実際にWITH句の効果を感じられるようなハンズオンをご紹介していきますので、手を動かしながら体験して見ましょう。