Subqueries, EXISTS, IN, and UNION
A subquery is a query nested inside another statement. EXISTS tests whether at least one row is returned and is often expressive for relationship checks. IN compares a value with a set of values; be mindful that NULL values can affect NOT IN logic. UNION combines result sets and removes duplicates, while UNION ALL preserves duplicates and is often faster.
Use the form that best expresses intent, then inspect the query plan for performance-sensitive workloads. Correlated subqueries reference values from an outer query; PostgreSQL may optimize them, but performance depends on indexes, cardinality, and query shape.
Find customers with orders
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);💡 EXISTS returns true when the subquery finds at least one order for the current customer. The selected value inside the EXISTS subquery is not used as output.
Key Takeaways
- EXISTS tests whether matching rows exist.
- UNION removes duplicates; UNION ALL keeps them.
- Handle NULL carefully with NOT IN; NOT EXISTS can be safer for anti-joins.
Knowledge Check
Which set operation preserves duplicates?