Categories:: Query syntax

LATERAL¶

In a FROM clause, the LATERAL keyword allows an inline view to reference columns from a table expression that precedes that inline view.

A lateral join behaves more like a correlated subquery than like most joins. A lateral join behaves as if the server executed a loop similar to the following:

for each row in left_hand_table LHT:
    execute right_hand_subquery RHS using the values from the current row in the LHT

Copy

Unlike the output of a non-lateral join, the output from a lateral join includes only the rows generated from the inline view. The rows on the left-hand side do not need to be joined to the right hand side because the rows on the left-hand side have already been taken into account by being passed into the inline view.

Syntax¶

SELECT ...
FROM <left_hand_table_expression>, LATERAL ( <inline_view> )
...

Copy

Parameters¶

left_hand_table_expression

This is a source of rows, such as:

A table.

A view.

A subquery.

A table function.

The result of an earlier join.

inline_view

The inline_view can be:

An inline view (a view defined within the statement, and valid only for the duration of the statement).

A subquery.

A table function (either a built-in table function such as FLATTEN or a user-defined table function (UDTF)).

The inline_view cannot be a table.

Usage notes¶

The inline view after the keyword LATERAL can reference columns only from the inline view itself and from tables to the left of the inline view in the FROM clause.
```
SELECT *
    FROM table_reference_me, LATERAL (...), table_do_not_reference_me ...
```
Copy
Although the inline view typically references a column(s) from the left_hand_table_expression, it is not required to do so.
Just as INNER JOIN syntax can use either the comma or the keywords INNER JOIN, a lateral join can also use the comma or the keywords INNER JOIN.
You cannot specify the ON, USING, or NATURAL JOIN clause in a lateral table function (other than a SQL UDTF), and you cannot specify the ON, USING, or NATURAL JOIN clause in an outer lateral join to a table function (other than a SQL UDTF).

For details, refer to the Usage Notes in the JOIN topic.

Examples¶

This example uses the data shown below:

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR, 
                        department_ID INTEGER, project_names ARRAY);

Copy

INSERT INTO departments (department_ID, name) VALUES 
    (1, 'Engineering'), 
    (2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES 
    (101, 'Richards', 1),
    (102, 'Paulson',  1),
    (103, 'Johnson',  2); 

Copy

Basic example¶

This example shows a lateral join with a subquery.

SELECT * 
    FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
    ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Copy

Example of using LATERAL with FLATTEN()¶

This example shows how a lateral join can use the inline view returned by FLATTEN:

SELECT * FROM table1, LATERAL FLATTEN(...);

Copy

First, update the employee table to include ARRAY data that FLATTEN can operate on:

UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs') 
    WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
    WHERE employee_ID = 102;

Copy

Second, execute a query that uses FLATTEN and contains a reference to a column(s) in a table that precedes it:

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
    FROM employees AS emp, LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
    ORDER BY employee_ID;
+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME         |
|-------------+-----------+-------+----------------------|
|         101 | Richards  |     0 | "Materialized Views" |
|         101 | Richards  |     1 | "UDFs"               |
|         102 | Paulson   |     0 | "Materialized Views" |
|         102 | Paulson   |     1 | "Lateral Joins"      |
+-------------+-----------+-------+----------------------+

Copy

Examples showing both join syntaxes¶

The following SQL statements are equivalent and produce the same output (the output is shown only once below). The first SQL statement below uses a comma before the keyword LATERAL while the second SQL statement uses the keywords INNER JOIN.

SELECT * 
    FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
    ORDER BY employee_ID;

Copy

SELECT * 
    FROM departments AS d INNER JOIN LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
    ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Copy