LATERAL SUBQUERY
Description
LATERAL SUBQUERY is a subquery that is preceded by the keyword LATERAL. It provides a way to reference columns in the preceding FROM clause.
Without the LATERAL keyword, subqueries can only refer to columns in the outer query, but not in the FROM clause. LATERAL SUBQUERY makes the complicated
queries simpler and more efficient.
Syntax
[ LATERAL ] primary_relation [ join_relation ]
Parameters
-
primary_relation
Specifies the primary relation. It can be one of the following:
- Table relation
-
Aliased query
Syntax:
( query ) [ [ AS ] alias ] -
Aliased relation
Syntax:
( relation ) [ [ AS ] alias ] - Table-value function
- Inline table
-
join_relation
Specifies a Join relation.
Examples
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0, 1), (1, 2);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t2 VALUES (0, 2), (0, 3);
SELECT * FROM t1,
LATERAL (SELECT * FROM t2 WHERE t1.c1 = t2.c1);
+--------+-------+--------+-------+
| t1.c1 | t1.c2 | t2.c1 | t2.c2 |
+-------+--------+--------+-------+
| 0 | 1 | 0 | 3 |
| 0 | 1 | 0 | 2 |
+-------+--------+--------+-------+
SELECT a, b, c FROM t1,
LATERAL (SELECT c1 + c2 AS a),
LATERAL (SELECT c1 - c2 AS b),
LATERAL (SELECT a * b AS c);
+--------+-------+--------+
| a | b | c |
+-------+--------+--------+
| 3 | -1 | -3 |
| 1 | -1 | -1 |
+-------+--------+--------+