sql / expert
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
1
2
3
4
5
6
7
8
9
SELECT d.department_name, top_earners.nameFROM departments dCROSS JOIN LATERAL (SELECT e.nameFROM employees eWHERE e.department_id = d.idORDER BY e.salary DESCFETCH FIRST 3 ROWS ONLY) AS top_earners;
Breakdown
1
FROM departments d
The primary table that provides the context for each lateral iteration.
2
CROSS JOIN LATERAL (
Enables the subquery to access columns from the 'd' alias defined earlier.
3
WHERE e.department_id = d.id
The correlation condition that filters employees based on the current department.
4
FETCH FIRST 3 ROWS ONLY
Limits the result of the subquery to the top 3 records for each department iteration.