Subqueries let one query depend on the result of another. They are useful for membership checks, threshold comparisons, and row-by-row existence logic when joins would be less clear.
- Use EXISTS for existence checks and stop scanning once a match is found.
- Keep correlated subqueries selective to avoid expensive repeated scans.
- Prefer readability first, then compare with equivalent JOIN alternatives.
Choose the subquery pattern by intent
Northstar fraud review often asks for customers who placed an order but never had a successful payment. EXISTS and NOT EXISTS express that intent directly and avoid accidental duplication.
IN works well for small, clear sets, while scalar subqueries fit single-value comparisons like above average order amount.
Handle correlated logic with care
A correlated subquery references outer query columns, so it can run once per outer row. That is powerful for row-specific checks but can be expensive on large tables without proper indexes.
Before shipping, run EXPLAIN and validate that key predicates use indexes on linked fields such as customer_id and order_id.
- Use NOT EXISTS for anti-joins when null behavior matters.
- Avoid SELECT * in subqueries used only for existence checks.
- Test with realistic row counts, not tiny development samples.
Find customers with unpaid orders
orders has order_id and customer_id, while payments has order_id and payment_status.
- Select distinct customers from orders as the outer set.
- Use EXISTS to require at least one order for each customer.
- Use NOT EXISTS to exclude customers whose orders have a successful payment record.
Common mistakes
- Using NOT IN with nullable subquery values and getting unexpected empty results.
- Writing correlated subqueries without indexes on join keys.
- Replacing clear EXISTS logic with a harder-to-read join workaround.
- Forgetting DISTINCT when outer rows can repeat by design.
Try one
Why is NOT EXISTS often safer than NOT IN for exclusion logic?
NOT EXISTS handles nulls predictably, while NOT IN can behave unexpectedly if the subquery returns null values.
Sources
- PostgreSQL 18 Docs: Subquery ExpressionsOfficial reference for EXISTS, IN, ANY, ALL, and subquery semantics.