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. For example:
```
FROM departments AS d INNER JOIN LATERAL (...)
```
Copy
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¶

The following example uses these tables:

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

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

This following query is 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;

Copy

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

The following SQL statement is equivalent and produces the same output. It uses the keywords INNER JOIN instead of the comma in the FROM clause.

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;

Copy