Dependent Derived Tables with LATERAL

Snippet

Dependent Derived Tables with LATERAL

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding tables in the join order. This effectively creates a 'foreach' loop logic in SQL, enabling complex per-row calculations like finding the top three earners for each individual department in a single query.

snippet.sql
sql
SELECT d.department_name, top_earners.name
FROM departments d
CROSS JOIN LATERAL (
  SELECT e.name
  FROM employees e
  WHERE e.department_id = d.id
  ORDER BY e.salary DESC
  FETCH FIRST 3 ROWS ONLY
) AS top_earners;

Breakdown

FROM departments d

The primary table that provides the context for each lateral iteration.

CROSS JOIN LATERAL (

Enables the subquery to access columns from the 'd' alias defined earlier.

WHERE e.department_id = d.id

The correlation condition that filters employees based on the current department.

FETCH FIRST 3 ROWS ONLY

Limits the result of the subquery to the top 3 records for each department iteration.

Previous snippet Next snippet

From your library

Dependent Derived Tables with LATERAL

Related