LATERAL Derived Tables
A lateral derived table is a subquery in the FROM clause that can reference columns from tables that appear earlier in the same FROM clause. Compared with a standard derived table, whose subquery cannot reference columns from other tables in the same FROM clause, a lateral derived table is more flexible.
Starting from v8.5.7, TiDB supports parsing the LATERAL syntax for derived tables, which is compatible with the MySQL 8.0 syntax (WL#8652).
Syntax
SELECT ... FROM table_ref, LATERAL (subquery) [AS] alias [(col_list)] ...
SELECT ... FROM table_ref [INNER | CROSS | LEFT [OUTER] | RIGHT [OUTER]] JOIN LATERAL (subquery) [AS] alias [(col_list)] ON ...
- The
LATERALkeyword must precede the derived table subquery. - A table alias must be specified after the closing parenthesis of the subquery.
- The
ASkeyword before the alias is optional. - An optional derived column list can follow the alias, for example,
LATERAL (...) AS dt(col1, col2).
Examples
Use a comma join with a LATERAL derived table
SELECT * FROM t1, LATERAL (SELECT * FROM t2 WHERE t2.id = t1.id) AS dt;
In this example, t1 and the LATERAL derived table are joined by a comma in the same FROM clause. The subquery in the LATERAL derived table references t1.id, a column from the preceding table t1. A regular derived table without LATERAL does not support this capability.
Use a LATERAL derived table (with a derived column list) in LEFT JOIN
SELECT t1.id, dt.val
FROM t1
LEFT JOIN LATERAL (SELECT t2.val FROM t2 WHERE t2.id = t1.id LIMIT 1) AS dt(val)
ON TRUE;
In this example, the LATERAL derived table is used as the right table of the LEFT JOIN and can reference the column t1.id from the left table t1. The derived column list (val) names the column returned by the subquery to val.
Comparison with standard derived tables
MySQL compatibility
The LATERAL derived table syntax of TiDB is compatible with MySQL 8.0 at the syntax level.