1
SQLIntermediate#queries#cte
SQLIntermediate#subqueries
Difference between IN, EXISTS and JOIN?
IN compares a value to a materialised list. EXISTS stops at the first matching row and handles NULLs safely. A JOIN can return duplicate outer rows when multiple matches exist, so use EXISTS for pure existence checks.
Example
SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);