Lateral Subquery in WHERE
Lateral Subquery in WHERE is a SQL statement in the Subqueries category. Correlated EXISTS subquery in WHERE clause to filter based on existence in another table. The syntax is SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id AND ...);. It returns existential filter. A typical example: SELECT d.* FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.dept_id = d.id AND e.salary > 100000 ); -- Departments with at least one high-earning employee -- NOT EXISTS (anti-join): SELECT d.* FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.dept_id = d.id ); -- Departments with NO employees at all 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.