MySQL8.0になってWITH句が使えるようになった

こんにちは。株式会社クラス システム開発本部の佐々木です。
私のテックブログ記事の執筆が滞っている間にすっかり冬が到来してしまったみたいです。

今回はMySQLに関わる話をします。
弊社のインフラのDBはMySQLを採用していて、マイグレーションツールとの兼ね合いやAmazon RDSのサポートの都合などあり、ようやく今年5.7から8.0へのバージョンアップに踏み切りました。
そんなこんなで無事バージョンアップが完了しまして、それに伴い色々な恩恵を授かりましたのでその恩恵の中からピックアップして皆様に共有させて頂きます。

性能面やセキュリティ面など様々な側面での品質向上が見られるとのことですが、私自身が日々の業務の中で最も有り難みを実感しているのはSQLを書く際に「共通テーブル式」が使えるようになったことです。
「共通テーブル式」は「CTE(Common Table Expressions)」や「WITH句」とも呼称されたりします。

では使用例を見ていきましょう。例えば以下のようなデータベースがあるとします。

大変雑な仕上がり

「『グルメ』カテゴリーのタグ付きの投稿をしている日本在住ユーザーを取得したい」
という要望があった場合、MySQL5.7まではおおよそ以下のようなSQLを書くでしょう。

SELECT u.*
FROM user u
INNER JOIN address a ON a.user_id = u.id
INNER JOIN (
  -- 『グルメ』カテゴリーのタグ付き投稿をしているユーザーid
  SELECT p.user_id AS id
  FROM post p
  INNER JOIN tag t ON t.post_id = p.id
  WHERE t.category = 'Gourmet'
  GROUP BY p.user_id
) gourmet_user ON gourmet_user.id = u.id
WHERE a.country = 'Japan'
;

WITH句を使うと以下のように書けます。（もちろん上述のSQLでも可）

WITH
  -- 『グルメ』カテゴリーのタグ付き投稿をしているユーザーid
  gourmet_user AS (
    SELECT p.user_id AS id
    FROM post p
    INNER JOIN tag t ON t.post_id = p.id
    WHERE t.category = 'Gourmet'
    GROUP BY p.user_id
  )
SELECT u.*
FROM user u
INNER JOIN address a ON a.user_id = u.id
WHERE
  id IN (SELECT id FROM gourmet_user)
  AND a.country = 'Japan'
;

要はWITH句を使うと本体のSELECT文（UPDATE・DELETEでも使える）の書き出しの前段階で名前付きの結果セットを作っておいて、本体の中で任意に参照することができるのです。
ちなみに同一のレベルにおいて定義できる共通テーブルの数は特に上限は設けられていませんが、各々の環境の設定に依存するそうです。

WITH句を使ったSQLの最大のメリットは可読性の高さにあると私個人としては感じています。
SQL作成時、メンテナンス時、あるいは他者にレビューしてもらう際いずれのケースでもこれは有効であるはずです。

この程度の要件であれば正直どちら書いても大差ないですが、業務で複雑な要件で抽出クエリを書いたりする際にはこの差が歴然となります。

どうですか？
MySQL5.7以前を利用している方はこれだけでもMySQL8.0に移行したくて堪らなくなったのではないでしょうか。
（工数見合いでやむなく移行できてないという方も多いかもしれませんが…）

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.15 WITH (共通テーブル式)