LATERAL SUBQUERY clause - Amazon OpenSearch Service

LATERAL SUBQUERY clause

Note

To see which AWS data source integrations support this SQL command, see Supported OpenSearch SQL commands and functions.

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:

    1. Table relation

    2. Aliased query

      Syntax: ( query ) [ [ AS ] alias ]

    3. Aliased relation

      Syntax: ( relation ) [ [ AS ] alias ]

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