先日、緊急リリースで MySQL 8.0.15 が出たばかりですが（注：残念ながら「待ち焦がれた CHECK 制約」は 8.0.16 に先送りとなりました）、8.0.14 でLATERAL句がサポートされたという話が聞こえてきたので（自分では見逃してました）、とりあえず試してみました。
- Support for LATERAL derived tables added to MySQL 8.0.14（MySQL Server Blog）
やってみる
本来なら機能の解説を書いてから「やってみる」なのですが…うまく説明できない（笑）。ので、一応参考になりそうな記事へのリンクを付けておきます。
- LATERALを使ってみよう（Let's Postgres）
このページに、
内部的な挙動としては、まずLATERAL以外のサブクエリやリレーションが評価され結果が生成されます。その後、その結果の1行ごとにLATERAL内のサブクエリが評価されます。いわば、非LATERALをouter、LATERALをinnerとしたNestLoop Joinのような処理となります。
と書かれているような処理になるわけです。
テスト：年月・店舗別の売上集計っぽいやつ
以下のとおり、テーブルを作ってテストデータを流し込みます。
テスト準備
mysql> CREATE DATABASE lateral_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE lateral_test;
Database changed
mysql> CREATE TABLE shop (
-> shop_id INT PRIMARY KEY NOT NULL,
-> shop_name VARCHAR(20)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE sales (
-> sales_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> sales_date DATE NOT NULL,
-> shop_id INT NOT NULL,
-> product_id INT NOT NULL,
-> total_price INT NOT NULL,
-> total_count INT NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO shop SET shop_id=1, shop_name='東京本店';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO shop SET shop_id=2, shop_name='横浜支店';
Query OK, 1 row affected (0.00 sec)
（以降省略）
mysql> SELECT * FROM shop;
+---------+-----------------+
| shop_id | shop_name |
+---------+-----------------+
| 1 | 東京本店 |
| 2 | 横浜支店 |
| 3 | 大阪支店 |
| 4 | 名古屋支店 |
| 5 | 札幌支店 |
| 6 | 福岡支店 |
| 7 | 仙台支店 |
| 8 | 神戸支店 |
+---------+-----------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM sales;
+----------+------------+---------+------------+-------------+-------------+
| sales_id | sales_date | shop_id | product_id | total_price | total_count |
+----------+------------+---------+------------+-------------+-------------+
| 1 | 2019-01-03 | 2 | 5 | 12000 | 3 |
| 2 | 2019-01-03 | 4 | 3 | 10000 | 5 |
| 3 | 2019-01-03 | 1 | 5 | 9000 | 2 |
| 4 | 2019-01-05 | 5 | 3 | 15000 | 8 |
| 5 | 2019-01-05 | 8 | 2 | 18000 | 4 |
| 6 | 2019-01-08 | 7 | 1 | 12000 | 5 |
| 7 | 2019-01-09 | 6 | 2 | 12000 | 3 |
| 8 | 2019-01-10 | 3 | 4 | 22000 | 8 |
| 9 | 2019-01-10 | 1 | 3 | 12000 | 3 |
| 10 | 2019-01-10 | 2 | 5 | 13000 | 5 |
| 11 | 2019-01-10 | 3 | 4 | 18000 | 9 |
| 12 | 2019-01-10 | 7 | 2 | 12000 | 4 |
| 13 | 2019-01-15 | 4 | 5 | 16000 | 8 |
| 14 | 2019-01-17 | 2 | 4 | 12000 | 8 |
| 15 | 2019-01-19 | 6 | 1 | 15000 | 5 |
| 16 | 2019-01-19 | 7 | 1 | 14000 | 7 |
| 17 | 2019-01-25 | 3 | 2 | 16000 | 4 |
| 18 | 2019-01-28 | 8 | 4 | 15000 | 3 |
| 19 | 2019-01-30 | 1 | 3 | 17500 | 5 |
| 20 | 2019-01-31 | 5 | 5 | 21500 | 5 |
| 21 | 2019-02-01 | 5 | 4 | 10800 | 6 |
| 22 | 2019-02-01 | 7 | 3 | 12800 | 8 |
| 23 | 2019-02-03 | 2 | 2 | 16400 | 4 |
| 24 | 2019-02-03 | 1 | 2 | 14400 | 4 |
| 25 | 2019-02-04 | 3 | 1 | 19200 | 8 |
| 26 | 2019-02-04 | 2 | 1 | 19800 | 6 |
| 27 | 2019-02-04 | 1 | 2 | 16800 | 8 |
| 28 | 2019-02-05 | 6 | 5 | 24000 | 12 |
| 29 | 2019-02-05 | 8 | 4 | 20000 | 10 |
| 30 | 2019-02-05 | 7 | 2 | 18000 | 8 |
+----------+------------+---------+------------+-------------+-------------+
30 rows in set (0.00 sec)
LATERALを使わずに普通にやってみる
まずは普通にGROUP BY句で集計してみます。
普通にやってみる
mysql> SELECT
-> DATE_FORMAT(sa.sales_date, '%Y%m') ym, sh.shop_id, sh.shop_name, SUM(sa.total_price) tp, SUM(sa.total_count) tc
-> FROM
-> shop sh, sales sa
-> WHERE
-> sh.shop_id = sa.shop_id
-> GROUP BY
-> sh.shop_id, ym
-> ORDER BY
-> ym, sh.shop_id;
+--------+---------+-----------------+-------+------+
| ym | shop_id | shop_name | tp | tc |
+--------+---------+-----------------+-------+------+
| 201901 | 1 | 東京本店 | 38500 | 10 |
| 201901 | 2 | 横浜支店 | 37000 | 16 |
| 201901 | 3 | 大阪支店 | 56000 | 21 |
| 201901 | 4 | 名古屋支店 | 26000 | 13 |
| 201901 | 5 | 札幌支店 | 36500 | 13 |
| 201901 | 6 | 福岡支店 | 27000 | 8 |
| 201901 | 7 | 仙台支店 | 38000 | 16 |
| 201901 | 8 | 神戸支店 | 33000 | 7 |
| 201902 | 1 | 東京本店 | 31200 | 12 |
| 201902 | 2 | 横浜支店 | 36200 | 10 |
| 201902 | 3 | 大阪支店 | 19200 | 8 |
| 201902 | 5 | 札幌支店 | 10800 | 6 |
| 201902 | 6 | 福岡支店 | 24000 | 12 |
| 201902 | 7 | 仙台支店 | 30800 | 16 |
| 201902 | 8 | 神戸支店 | 20000 | 10 |
+--------+---------+-----------------+-------+------+
15 rows in set (0.00 sec)
はい、至って普通です（笑）。
LATERALを使ってやってみる
正直、このケースではLATERAL句の中にもGROUP BY句が入ってしまうため例としては微妙ですが…。
LATERALでやってみる
mysql> SELECT
-> t.ym, sh.shop_id, sh.shop_name, t.tp, t.tc
-> FROM
-> shop sh,
-> LATERAL (
-> SELECT
-> DATE_FORMAT(sa.sales_date, '%Y%m') ym, SUM(sa.total_price) tp, SUM(sa.total_count) tc
-> FROM
-> sales sa
-> WHERE
-> sh.shop_id = sa.shop_id
-> GROUP BY
-> ym
-> ) t
-> ORDER BY
-> t.ym, sh.shop_id;
+--------+---------+-----------------+-------+------+
| ym | shop_id | shop_name | tp | tc |
+--------+---------+-----------------+-------+------+
| 201901 | 1 | 東京本店 | 38500 | 10 |
| 201901 | 2 | 横浜支店 | 37000 | 16 |
| 201901 | 3 | 大阪支店 | 56000 | 21 |
| 201901 | 4 | 名古屋支店 | 26000 | 13 |
| 201901 | 5 | 札幌支店 | 36500 | 13 |
| 201901 | 6 | 福岡支店 | 27000 | 8 |
| 201901 | 7 | 仙台支店 | 38000 | 16 |
| 201901 | 8 | 神戸支店 | 33000 | 7 |
| 201902 | 1 | 東京本店 | 31200 | 12 |
| 201902 | 2 | 横浜支店 | 36200 | 10 |
| 201902 | 3 | 大阪支店 | 19200 | 8 |
| 201902 | 5 | 札幌支店 | 10800 | 6 |
| 201902 | 6 | 福岡支店 | 24000 | 12 |
| 201902 | 7 | 仙台支店 | 30800 | 16 |
| 201902 | 8 | 神戸支店 | 20000 | 10 |
+--------+---------+-----------------+-------+------+
15 rows in set (0.00 sec)
当然、結果は同じですが、イメージはついたでしょうか？
この程度の内容であればLATERAL句を使わないほうがシンプルですが、もう少し複雑なものになれば、LATERAL句を使うほうが処理の内容を分かりやすく記述できるケースがあると思います。
EXPLAINを比較してみる
ついでに、EXPLAINの結果も比較してみます。
EXPLAIN比較
mysql> EXPLAIN
-> SELECT
-> DATE_FORMAT(sa.sales_date, '%Y%m') ym, sh.shop_id, sh.shop_name, SUM(sa.total_price) tp, SUM(sa.total_count) tc
-> FROM
-> shop sh, sales sa
-> WHERE
-> sh.shop_id = sa.shop_id
-> GROUP BY
-> sh.shop_id, ym
-> ORDER BY
-> ym, sh.shop_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | sa | NULL | ALL | NULL | NULL | NULL | NULL | 30 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | sh | NULL | eq_ref | PRIMARY | PRIMARY | 4 | lateral_test.sa.shop_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select date_format(`lateral_test`.`sa`.`sales_date`,'%Y%m') AS `ym`,`lateral_test`.`sh`.`shop_id` AS `shop_id`,`lateral_test`.`sh`.`shop_name` AS `shop_name`,sum(`lateral_test`.`sa`.`total_price`) AS `tp`,sum(`lateral_test`.`sa`.`total_count`) AS `tc` from `lateral_test`.`shop` `sh` join `lateral_test`.`sales` `sa` where (`lateral_test`.`sh`.`shop_id` = `lateral_test`.`sa`.`shop_id`) group by `lateral_test`.`sh`.`shop_id`,`ym` order by `ym`,`lateral_test`.`sh`.`shop_id`
1 row in set (0.00 sec)
mysql> EXPLAIN
-> SELECT
-> t.ym, sh.shop_id, sh.shop_name, t.tp, t.tc
-> FROM
-> shop sh,
-> LATERAL (
-> SELECT
-> DATE_FORMAT(sa.sales_date, '%Y%m') ym, SUM(sa.total_price) tp, SUM(sa.total_count) tc
-> FROM
-> sales sa
-> WHERE
-> sh.shop_id = sa.shop_id
-> GROUP BY
-> ym
-> ) t
-> ORDER BY
-> t.ym, sh.shop_id;
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
| 1 | PRIMARY | sh | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort; Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | DEPENDENT DERIVED | sa | NULL | ALL | NULL | NULL | NULL | NULL | 30 | 10.00 | Using where; Using temporary |
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'lateral_test.sh.shop_id' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `t`.`ym` AS `ym`,`lateral_test`.`sh`.`shop_id` AS `shop_id`,`lateral_test`.`sh`.`shop_name` AS `shop_name`,`t`.`tp` AS `tp`,`t`.`tc` AS `tc` from `lateral_test`.`shop` `sh` join lateral (/* select#2 */ select date_format(`lateral_test`.`sa`.`sales_date`,'%Y%m') AS `ym`,sum(`lateral_test`.`sa`.`total_price`) AS `tp`,sum(`lateral_test`.`sa`.`total_count`) AS `tc` from `lateral_test`.`sales` `sa` where (`lateral_test`.`sh`.`shop_id` = `lateral_test`.`sa`.`shop_id`) group by `ym`) `t` order by `t`.`ym`,`lateral_test`.`sh`.`shop_id`
2 rows in set (0.00 sec)
WARNING にMessage: Field or reference 'lateral_test.sh.shop_id' of SELECT #2 was resolved in SELECT #1なんてのが出てますね。
なお、PostgreSQL ではこんな話もありますが、MySQL 8.0 ではどういう実行計画になるのか？是非試してみてください！
- クエリチューニング: GROUP BY から LATERAL への書き換え（fujii_masao さん）