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 |
+-------+--------+--------+