LATERAL Subquery (PostgreSQL)

LATERAL Subquery (PostgreSQL) is a SQL statement in the Subqueries category. A subquery that can reference columns from preceding FROM items. Powerful for row-dependent subqueries. The syntax is SELECT * FROM table1, LATERAL (SELECT ... WHERE table1.col = sub.col) AS alias;. It returns lateral joined result. A typical example: SELECT e.name, recent.sale_total FROM employees e, LATERAL ( SELECT SUM(amount) AS sale_total FROM sales s WHERE s.salesperson_id = e.id AND s.sale_date > NOW() - INTERVAL '30 days' ) recent WHERE recent.sale_total > 10000; -- Top salespeople in last 30 days A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query. A close relative is EXISTS, which returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets.