|
Lateral join is a useful feature introduced in MySQL 8. PostgreSQL also supports it.
https://dev.mysql.com/doc/refman/8.0/en/join.html
https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html
table_factor: {
|
tbl_name [PARTITION (partition_names)]
|
[[AS] alias] [index_hint_list]
|
| [LATERAL] table_subquery [AS] alias [(col_list)]
|
| ( table_references )
|
}
|
support in mysql,
mysql> select * from (select 1) as t1 LEFT JOIN LATERAL (select 1) as t on 1=1;
|
+---+------+
|
| 1 | 1 |
|
+---+------+
|
| 1 | 1 |
|
+---+------+
|
1 row in set (0.00 sec)
|
|
mysql> select version()\G
|
*************************** 1. row ***************************
|
version(): 8.0.32
|
1 row in set (0.00 sec)
|
but not support in MariaDB until now.
MariaDB [s1]> select * from (select 1) as t1 LEFT JOIN LATERAL (select 1) as t on 1=1;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select 1) as t on 1=1' at line 1
|
MariaDB [s1]> select version();
|
+---------------------------------------+
|
| version() |
|
+---------------------------------------+
|
| 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 |
|
+---------------------------------------+
|
1 row in set (0.000 sec)
|
|