Subqueries & EXISTS

EXISTS checks presence without returning duplicate rows from a one-to-many child. IN works for small static sets; EXISTS scales better for correlated semi-joins.

Recipe

SELECT c.id, c.email
FROM app.customers c
WHERE EXISTS (
  SELECT 1 FROM app.orders o
  WHERE o.customer_id = c.id AND o.total > 1000
);
 
SELECT id FROM app.customers
WHERE id IN (SELECT customer_id FROM app.orders WHERE total > 1000);

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

SELECT c.email
FROM app.customers c
WHERE NOT EXISTS (
  SELECT 1 FROM app.orders o WHERE o.customer_id = c.id
);

What this demonstrates:

• EXISTS short-circuits on first match
• NOT EXISTS finds customers without orders
• IN equivalent when child key is unique per parent filter

Deep Dive

How It Works

• PostgreSQL parses SQL into a query tree.
• The planner estimates costs using statistics.
• Executor returns rows to the client protocol.

Notes

• Prefer readable SQL; optimizers rewrite internally.
• Test with production-like row counts.

Gotchas

• IN with NULL in subquery - Unknown poisons result to unknown.. Fix: Use NOT EXISTS or enforce NOT NULL keys.
• Large IN lists - Thousands of literals parse slowly.. Fix: Use temp table join or = ANY(array).
• Correlated nested loop cost - EXISTS per outer row without index.. Fix: Index child join columns.

Alternatives

FAQs

Related

• JOIN Types - when join replaces exists
• LATERAL Joins - per-row subqueries

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.