LATERAL JOIN in PostgreSQL
LATERAL JOIN in PostgreSQL is a SQL statement in the JOIN Types category. LATERAL allows a subquery in FROM to reference columns from preceding FROM items. Powerful for correlated subqueries. The syntax is SELECT * FROM table1, LATERAL (SELECT ... WHERE table1.id = sub.id) sub;. It returns lateral join result. A typical example: SELECT e.name, top_order.amount, top_order.order_date FROM employees e, LATERAL ( SELECT amount, order_date FROM orders WHERE employee_id = e.id ORDER BY order_date DESC LIMIT 1 ) top_order; -- Latest order for each employee -- LATERAL with joins: SELECT d.name, recent.name AS recent_hire, recent.hire_date FROM departments d, LATERAL ( SELECT name, hire_date FROM employees WHERE dept_id = d.id ORDER BY hire_date DESC LIMIT 3 ) recent; -- 3 most recent hires per… A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type. A close relative is LEFT JOIN, which returns ALL rows from the left table and matching rows from the right. Unmatched right rows show NULL.