Reference Outer Columns Inside a Subquery With LATERAL

Use `LATERAL` (or `CROSS APPLY` on SQL Server) to let a subquery reference columns from the preceding table in the `FROM` clause.

3/3 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Get the two highest-paid employees in each department

For each department row, the lateral subquery runs once with that row's d.id available as a reference. Engineering has three employees; the subquery returns the top two by salary: Ada (120) and Carol (110). Bob (95) is excluded. Finance has two employees so both qualify: Eve (105) and Dave (90). CROSS APPLY on SQL Server behaves identically — it implicitly filters out departments that return zero rows from the inner query, matching JOIN LATERAL … ON TRUE semantics.

MySQL PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (id INT, dept_id INT, name VARCHAR(50), salary INT);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, dept_id, name, salary)
VALUES
  (1, 1, 'Ada', 120),
  (2, 1, 'Bob', 95),
  (3, 1, 'Carol', 110),
  (4, 2, 'Dave', 90),
  (5, 2, 'Eve', 105);

Validated query

SQL

SELECT
  d.name AS department,
  e.name AS employee,
  e.salary
FROM
  departments d
  JOIN LATERAL (
    SELECT
      name,
      salary
    FROM
      employees
    WHERE
      dept_id = d.id
    ORDER BY
      salary DESC
    LIMIT
      2
  ) e ON TRUE
ORDER BY
  d.name,
  e.salary DESC;

Expected result

department	employee	salary
Engineering	Ada	120
Engineering	Carol	110
Finance	Eve	105
Finance	Dave	90

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (id INT, dept_id INT, name VARCHAR(50), salary INT);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, dept_id, name, salary)
VALUES
  (1, 1, 'Ada', 120),
  (2, 1, 'Bob', 95),
  (3, 1, 'Carol', 110),
  (4, 2, 'Dave', 90),
  (5, 2, 'Eve', 105);

Validated query

SQL

SELECT
  d.name AS department,
  e.name AS employee,
  e.salary
FROM
  departments d CROSS APPLY (
    SELECT
      TOP 2 name,
      salary
    FROM
      employees
    WHERE
      dept_id = d.id
    ORDER BY
      salary DESC
  ) e
ORDER BY
  d.name,
  e.salary DESC;

Expected result

department	employee	salary
Engineering	Ada	120
Engineering	Carol	110
Finance	Eve	105
Finance	Dave	90

MySQL, MariaDB, and PostgreSQL use JOIN LATERAL … ON TRUE with LIMIT 2. SQL Server uses CROSS APPLY with TOP 2. Results are identical.

Useful when

Where this command helps.

fetching the top N rows per group such as the two most recent orders per customer
applying a per-row computation or lookup that depends on column values from the outer query

Explanation

What the command is doing.

A lateral subquery is re-evaluated for each row of the outer query, and it can reference columns from that row. This makes it possible to get the top-N rows per group without window functions. PostgreSQL, MySQL 8.0.14+, and MariaDB 10.9+ use the JOIN LATERAL syntax. SQL Server uses CROSS APPLY, which is semantically equivalent.

Reference Outer Columns Inside a Subquery With LATERAL

Get the two highest-paid employees in each department

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.