LATERAL Joins in PostgreSQL

A normal subquery in FROM runs independently — it can't reference columns from other tables in the same query. LATERAL lifts that restriction: a LATERAL subquery can reference columns from tables listed before it in the FROM clause, effectively re-running once per outer row.

The Problem It Solves: "Top N Per Group"

Getting each department's 3 highest-paid employees isn't something a plain GROUP BY/LIMIT can express — LIMIT applies to the whole query, not per group.

SELECT d.name AS department, top_emp.name, top_emp.salary
FROM departments d
JOIN LATERAL (
  SELECT name, salary
  FROM employees e
  WHERE e.department_id = d.id
  ORDER BY salary DESC
  LIMIT 3
) top_emp ON true;

• The subquery references d.id — a column from a table earlier in the FROM clause. That's only legal because of LATERAL.
• It effectively runs once per department, each time limited to that department's top 3 earners.
• ON true is standard here — the real filtering already happened inside the subquery via WHERE e.department_id = d.id.

LEFT JOIN LATERAL — Keep Groups With No Matches

Just like a regular LEFT JOIN, pairing it with LATERAL keeps rows from the left side even when the subquery returns nothing.

SELECT d.name AS department, top_emp.name
FROM departments d
LEFT JOIN LATERAL (
  SELECT name FROM employees e
  WHERE e.department_id = d.id
  ORDER BY salary DESC
  LIMIT 1
) top_emp ON true;
-- every department appears, even ones with zero employees (top_emp.name comes back NULL)

When to Reach for LATERAL

• Getting the "top N" or "most recent" rows per group.
• Calling a set-returning function (like unnest() or a table function) using a value from an earlier column.

Common Errors & Troubleshooting

• ERROR: column "d.id" does not exist inside the subquery — you forgot the LATERAL keyword; a plain subquery in FROM can't see columns from earlier tables at all.
• Departments with zero employees disappearing from results — a plain JOIN LATERAL behaves like an inner join and drops groups with no rows; switch to LEFT JOIN LATERAL ... ON true to keep them.
• Forgetting ON true — a LEFT JOIN LATERAL still requires some ON clause syntactically, even though the real filtering already happened inside the subquery; ON true is the conventional way to satisfy that requirement.

Key Takeaways

• LATERAL lets a subquery in FROM see columns from tables listed before it — a plain subquery can't do this.
• It's the standard SQL solution for "top N rows per group," something GROUP BY + LIMIT alone can't express.
• Use LEFT JOIN LATERAL ... ON true instead of JOIN LATERAL when groups with zero matching rows should still appear in the result.