UNION, INTERSECT, EXCEPT

Set operations and dedup behavior. Practical PostgreSQL patterns for production queries.

Recipe

SELECT email FROM app.customers
UNION
SELECT contact_email FROM app.suppliers;
 
SELECT email FROM app.customers
INTERSECT
SELECT email FROM app.newsletter_subscribers;
 
SELECT email FROM app.customers
EXCEPT
SELECT email FROM app.unsubscribed;

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

Working Example

SELECT email, 'customer' AS source FROM app.customers
UNION ALL
SELECT contact_email, 'supplier' FROM app.suppliers;

What this demonstrates:

• UNION removes duplicates (sort/hash dedup cost)
• UNION ALL keeps duplicates, faster
• INTERSECT and EXCEPT compare whole rows

Deep Dive

Compatibility

• Column counts and types must align; casts may be needed.
• ORDER BY applies to final result; use outer parentheses.
• UNION ALL preferred when duplicates are impossible or acceptable.

Gotchas

• SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
• NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
• Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
• Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
• Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

FAQs

Related

• SQL Querying Basics - projections
• Common Table Expressions - readable pipelines

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+.