layout: global title: LATERAL SUBQUERY displayTitle: LATERAL SUBQUERY license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
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.
[ LATERAL ] primary_relation [ join_relation ]
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 ]
join_relation
Specifies a Join relation.
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 | +-------+--------+--------+