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.
//
Search across all documentation pages
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.
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.
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:
| Alternative | Use When | Don't Use When |
|---|---|---|
| ORM query builder | Team standardizes on one stack | Complex SQL becomes opaque |
| Materialized view | Repeat expensive aggregates | Needs refresh strategy |
| Warehouse replica | Heavy BI scans | Not for OLTP latency |
Use multiline template strings or SQL files; lint in CI.
When you need unique rows without aggregates.
Sort can use index order if query matches index columns.
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+.
Reviewed by Chris St. John·Last updated Jul 18, 2026