先にウィンドウ関数（Window Functions）を取り上げたのでMySQL 8.0でのサポート順とは逆になりますが（CTEは8.0.1）、いまさらながらCTE（Common Table Expressions）を試してみました。
0. CTEとは
メインのSQLの問い合わせを実行するために補助的に使う、一時テーブルを（WITH句を使って）定義するものです。
…というと、派生テーブル（FROM句のサブクエリ）と何が違うの？となりますが、（非再帰のCTEはWITH句を使うか使わないか等の書式の違いを除いて、派生テーブルとほぼ同じようなものですので）実質的な違いは「再帰的に書ける」ところです。
MySQL 8.0でのCTEの使い方については、以下の記事・資料が参考になります。
- 13.2.11.9 WITH Syntax (Common Table Expressions)（MySQL 8.0 Reference Manual）
- MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)（Yakst翻訳記事）
- MySQL8.0 の共通テーブル式（CTE）を使ってみよう（SmartStyle TECH BLOG）
1. 非再帰CTE（テストは省略）
前述のYakst翻訳記事（オリジナルはMySQL Server Blogの記事）以外に（日本語で）あまり触れられていないような気がしますが、理由はやはり「派生テーブルでほぼ同じ内容が書けるから目新しさがない」からでしょうね。
というわけで…ここでも省略します（笑）。
もっとも、WITH句で切り出してSQLの前に持ってきたり、複数のCTEを順序立てて記述したりすることができたり（詳細は前述の記事を参照）、派生テーブルで書くよりもSQLが読みやすくなる利点があります。
※5.5以前のMySQLのようにパフォーマンスを気にしてすべて結合（JOIN）で書いたりするのと比べれば雲泥の差ですね。
2. 再帰CTE（SELECT）
WITH RECURSIVE句を使って、起点となるレコードに対するSELECT文と、再帰的に実行していくSELECT文をUNION ALL句でつないで表現します。
例えば、以下のような構造のテーブルがあり、データが入っているとします（説明を簡単にするため、親テーブルは省略。製品（商品）名（product_name）は本来テーブルをマスタ化して切り出すべきカラムですが、わかりやすさのためにあえて製品（商品）名をそのまま入れてあります）。
注文詳細（order_detail）テーブル
mysql> CREATE TABLE ctetest.order_detail (order_detail_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, order_id INT UNSIGNED NOT NULL, parent_order_detail_id INT UNSIGNED, product_name VARCHAR(100), cancel_flag INT UNSIGNED NOT NULL, INDEX (order_id), INDEX (parent_order_detail_id));
Query OK, 0 rows affected (0.03 sec)
（データ挿入は省略）
mysql> SELECT * FROM ctetest.order_detail;
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name | cancel_flag |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| 1 | 1 | NULL | 車両本体 Sグレード | 0 |
| 2 | 1 | 1 | セーフティーパッケージ | 0 |
| 3 | 1 | 2 | 衝突回避ブレーキシステム | 0 |
| 4 | 1 | 2 | 追加エアバッグセット | 0 |
| 5 | 1 | 3 | サイドエアバッグ | 0 |
| 6 | 1 | 3 | カーテンエアバッグ | 0 |
| 7 | 1 | 1 | 18インチセット | 0 |
| 8 | 1 | 7 | 225/40R18ラジアルタイヤ | 0 |
| 9 | 1 | 7 | 18インチアルミホイール | 0 |
| 10 | 1 | 1 | フロアマット | 0 |
| 11 | 2 | NULL | 車両本体 Bグレード | 0 |
| 12 | 2 | 11 | サイドバイザー | 0 |
| 13 | 2 | 11 | フロアマット | 0 |
| 14 | 3 | NULL | 車両本体 Xグレード | 0 |
| 15 | 3 | 14 | スタイリッシュパッケージ | 0 |
| 16 | 3 | 15 | 18インチセット | 0 |
| 17 | 3 | 16 | 225/40R18ラジアルタイヤ | 0 |
| 18 | 3 | 16 | 18インチアルミホイール | 0 |
| 19 | 3 | 15 | エアロセットB | 0 |
| 20 | 3 | 19 | フロントアンダースポイラー | 0 |
| 21 | 3 | 19 | サイドステップ | 0 |
| 22 | 3 | 19 | リアスポイラー | 0 |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
22 rows in set (0.00 sec)
構造的にはこんな感じです。
注文書詳細
order#1-+-車両本体 Sグレード
|
+-+-セーフティーパッケージ
| |
| +---衝突回避ブレーキシステム
| |
| +-+-追加エアバッグセット
| |
| +---サイドエアバッグ
| |
| +---カーテンエアバッグ
|
+-+-18インチセット
| |
| +---225/40R18ラジアルタイヤ
| |
| +---18インチアルミホイール
|
+---フロアマット
order#2-+-車両本体 Bグレード
|
+---サイドバイザー
|
+---フロアマット
order#3-+-車両本体 Xグレード
|
+-+-スタイリッシュパッケージ
|
+-+-18インチセット
| |
| +---225/40R18ラジアルタイヤ
| |
| +---18インチアルミホイール
|
+-+-エアロセットB
|
+---フロントアンダースポイラー
|
+---サイドステップ
|
+---リアスポイラー
ここで、order#1の構成要素のうち、セーフティーパッケージとそれを構成する子パーツ、孫パーツを取り出すには、以下のようにします。
order#!のセーフティーパッケージ抽出
mysql> WITH RECURSIVE product_order AS
-> (
-> SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
-> FROM ctetest.order_detail
-> WHERE order_detail_id = 2
-> UNION ALL
-> SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
-> FROM ctetest.order_detail AS child, product_order
-> WHERE product_order.order_detail_id = child.parent_order_detail_id
-> )
-> SELECT * FROM product_order;
+-----------------+------------------------+--------------------------------------+-------------+
| order_detail_id | parent_order_detail_id | product_name | cancel_flag |
+-----------------+------------------------+--------------------------------------+-------------+
| 2 | 1 | セーフティーパッケージ | 0 |
| 3 | 2 | 衝突回避ブレーキシステム | 0 |
| 4 | 2 | 追加エアバッグセット | 0 |
| 5 | 3 | サイドエアバッグ | 0 |
| 6 | 3 | カーテンエアバッグ | 0 |
+-----------------+------------------------+--------------------------------------+-------------+
5 rows in set (0.00 sec)
無事、取り出せました。
EXPLAINの結果は以下の通りです。
EXPLAIN(1)
mysql> EXPLAIN WITH RECURSIVE product_order AS ( SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag FROM ctetest.order_detail WHERE order_detail_id = 2 UNION ALL SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag FROM ctetest.order_detail AS child, product_order WHERE product_order.order_detail_id = child.parent_order_detail_id ) SELECT * FROM product_order;
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 2 | DERIVED | order_detail | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 3 | UNION | product_order | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where |
| 3 | UNION | child | NULL | ref | parent_order_detail_id | parent_order_detail_id | 5 | product_order.order_detail_id | 2 | 100.00 | NULL |
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: with recursive `product_order` as (/* select#2 */ select '2' AS `order_detail_id`,'1' AS `parent_order_detail_id`,'セーフティーパッケージ' AS `product_name`,'0' AS `cancel_flag` from `ctetest`.`order_detail` where 1 union all /* select#3 */ select `ctetest`.`child`.`order_detail_id` AS `order_detail_id`,`ctetest`.`child`.`parent_order_detail_id` AS `parent_order_detail_id`,`ctetest`.`child`.`product_name` AS `product_name`,`ctetest`.`child`.`cancel_flag` AS `cancel_flag` from `ctetest`.`order_detail` `child` join `product_order` where (`ctetest`.`child`.`parent_order_detail_id` = `product_order`.`order_detail_id`)) /* select#1 */ select `product_order`.`order_detail_id` AS `order_detail_id`,`product_order`.`parent_order_detail_id` AS `parent_order_detail_id`,`product_order`.`product_name` AS `product_name`,`product_order`.`cancel_flag` AS `cancel_flag` from `product_order`
1 row in set (0.00 sec)
Extra列に「Recursive」が出ています。
3. 再帰CTE（UPDATE）
再帰CTEは更新系の処理でも使うことができます。
INSERTの場合はINSERT INTOする行を取得・生成することができますが、ここではUPDATEを試してみます。
先ほどのテーブルで、order#1のセーフティーパッケージが注文キャンセルされたものとして、cancel_flagを「1」にしてみます。
order#!のセーフティーパッケージ取消
mysql> WITH RECURSIVE product_order AS
-> (
-> SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
-> FROM ctetest.order_detail
-> WHERE order_detail_id = 2
-> UNION ALL
-> SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
-> FROM ctetest.order_detail AS child, product_order
-> WHERE product_order.order_detail_id = child.parent_order_detail_id
-> )
-> UPDATE ctetest.order_detail
-> SET cancel_flag = 1
-> WHERE order_detail_id IN
-> (SELECT order_detail_id FROM product_order);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> SELECT * FROM ctetest.order_detail WHERE cancel_flag = 1;
+-----------------+----------+------------------------+--------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name | cancel_flag |
+-----------------+----------+------------------------+--------------------------------------+-------------+
| 2 | 1 | 1 | セーフティーパッケージ | 1 |
| 3 | 1 | 2 | 衝突回避ブレーキシステム | 1 |
| 4 | 1 | 2 | 追加エアバッグセット | 1 |
| 5 | 1 | 3 | サイドエアバッグ | 1 |
| 6 | 1 | 3 | カーテンエアバッグ | 1 |
+-----------------+----------+------------------------+--------------------------------------+-------------+
5 rows in set (0.00 sec)
正しく更新されました。
こちらもEXPLAINの結果を見てみます。
EXPLAIN(2)
mysql> EXPLAIN WITH RECURSIVE product_order AS ( SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag FROM ctetest.order_detail WHERE order_detail_id = 2 UNION ALL SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag FROM ctetest.order_detail AS child, product_order WHERE product_order.order_detail_id = child.parent_order_detail_id ) UPDATE ctetest.order_detail SET cancel_flag = 1 WHERE order_detail_id IN (SELECT order_detail_id FROM product_order);
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| 1 | UPDATE | order_detail | NULL | index | NULL | PRIMARY | 4 | NULL | 22 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | NULL | index_subquery | <auto_key0> | <auto_key0> | 5 | func | 2 | 100.00 | NULL |
| 3 | DERIVED | order_detail | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 4 | UNION | product_order | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where |
| 4 | UNION | child | NULL | ref | parent_order_detail_id | parent_order_detail_id | 5 | product_order.order_detail_id | 2 | 100.00 | NULL |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
5 rows in set (0.00 sec)
SELECTでは結果をそのまま取り出しているのに対し、UPDATEではWHERE～INのサブクエリで取り出しているので、結果が変わってきています。
※MySQL 5.5以前の例に倣って等結合やJOINに書き換えると、機能上の制約に引っかかって怒られます。
4. 再帰CTE（DELETE）
当然、DELETEで使うこともできます。
DELETE
mysql> WITH RECURSIVE product_order AS
-> (
-> SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
-> FROM ctetest.order_detail
-> WHERE order_detail_id = 2
-> UNION ALL
-> SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
-> FROM ctetest.order_detail AS child, product_order
-> WHERE product_order.order_detail_id = child.parent_order_detail_id
-> )
-> DELETE FROM ctetest.order_detail
-> WHERE order_detail_id IN
-> (SELECT order_detail_id FROM product_order);
Query OK, 5 rows affected (0.01 sec)
mysql> SELECT * FROM ctetest.order_detail;
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name | cancel_flag |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| 1 | 1 | NULL | 車両本体 Sグレード | 0 |
| 7 | 1 | 1 | 18インチセット | 0 |
| 8 | 1 | 7 | 225/40R18ラジアルタイヤ | 0 |
| 9 | 1 | 7 | 18インチアルミホイール | 0 |
| 10 | 1 | 1 | フロアマット | 0 |
| 11 | 2 | NULL | 車両本体 Bグレード | 0 |
| 12 | 2 | 11 | サイドバイザー | 0 |
| 13 | 2 | 11 | フロアマット | 0 |
| 14 | 3 | NULL | 車両本体 Xグレード | 0 |
| 15 | 3 | 14 | スタイリッシュパッケージ | 0 |
| 16 | 3 | 15 | 18インチセット | 0 |
| 17 | 3 | 16 | 225/40R18ラジアルタイヤ | 0 |
| 18 | 3 | 16 | 18インチアルミホイール | 0 |
| 19 | 3 | 15 | エアロセットB | 0 |
| 20 | 3 | 19 | フロントアンダースポイラー | 0 |
| 21 | 3 | 19 | サイドステップ | 0 |
| 22 | 3 | 19 | リアスポイラー | 0 |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
17 rows in set (0.00 sec)
対象行が削除されました。
なお、EXPLAINの結果はUPDATEのときと同等（違いはid:1のUPDATE→DELETEのみ）でした。
EXPLAIN(3)
mysql> EXPLAIN WITH RECURSIVE product_order AS ( SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag FROM ctetest.order_detail WHERE order_detail_id = 2 UNION ALL SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag FROM ctetest.order_detail AS child, product_order WHERE product_order.order_detail_id = child.parent_order_detail_id ) DELETE FROM ctetest.order_detail WHERE order_detail_id IN (SELECT order_detail_id FROM product_order);
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| 1 | DELETE | order_detail | NULL | ALL | NULL | NULL | NULL | NULL | 22 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | NULL | index_subquery | <auto_key0> | <auto_key0> | 5 | func | 2 | 100.00 | NULL |
| 3 | DERIVED | order_detail | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 4 | UNION | product_order | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where |
| 4 | UNION | child | NULL | ref | parent_order_detail_id | parent_order_detail_id | 5 | product_order.order_detail_id | 2 | 100.00 | NULL |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
5 rows in set (0.00 sec)
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。