LATERAL JOIN

LATERAL JOIN is a SQL clause in the JOIN Types category. A LATERAL join allows a subquery to reference columns from tables that precede it in the FROM clause. The syntax is SELECT * FROM table1, LATERAL (subquery) AS alias;. It returns joined subquery with access to preceding columns. A typical example: SELECT e.name, recent.order_count FROM employees e, LATERAL ( SELECT COUNT(*) AS order_count FROM orders o WHERE o.salesperson_id = e.id ) recent WHERE e.department = 'Sales'; -- Each row evaluates the subquery using the employee id 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. A close relative is RIGHT JOIN, which returns ALL rows from the right table and matching rows from the left. Opposite of LEFT JOIN.