Set Operations

Set operations combine the results of two or more SELECT queries into a single result set. PostgreSQL supports UNION, UNION ALL, INTERSECT, and EXCEPT — all following standard SQL rules, with some important performance considerations.

Requirements for Set Operations

For two queries to be combined with a set operation:

1. Both must have the same number of columns
2. Corresponding columns must have compatible data types
3. Column names come from the first query

UNION — Combine Results, Remove Duplicates

UNION merges rows from two queries and removes duplicates:

-- All unique product types from both current catalog and archive
SELECT product_name FROM current_catalog
UNION
SELECT product_name FROM archived_catalog;

Example with Real Tables

-- Employees and contractors who work on the platform
SELECT name, 'Employee'   AS type, dept FROM employees
UNION
SELECT name, 'Contractor' AS type, dept FROM contractors
ORDER BY name;

UNION ALL — Combine Without Deduplication

UNION ALL is faster than UNION because it skips the deduplication step. Use it when:

• You know duplicates are impossible (different tables, different time periods)
• You actually want duplicates preserved

-- All transactions from Q1 and Q2 (may have duplicates if needed)
SELECT order_id, amount, order_date FROM orders_q1
UNION ALL
SELECT order_id, amount, order_date FROM orders_q2
ORDER BY order_date;

Performance Comparison

-- EXPLAIN shows the difference
EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t2;
-- Plan includes: HashAggregate (to remove duplicates)

EXPLAIN SELECT a FROM t1 UNION ALL SELECT a FROM t2;
-- Plan: simple Append (no extra work)

INTERSECT — Rows in Both Queries

Returns only rows that appear in both result sets:

-- Products ordered in both January and February
SELECT product FROM orders WHERE order_month = 'January'
INTERSECT
SELECT product FROM orders WHERE order_month = 'February';

Finding Common Customers

-- Customers who bought both Widget AND Gadget
SELECT customer_id FROM orders WHERE product = 'Widget'
INTERSECT
SELECT customer_id FROM orders WHERE product = 'Gadget';

INTERSECT vs EXISTS

Both are equivalent — choose whichever is clearer:

-- Using INTERSECT
SELECT customer_id FROM orders WHERE product = 'Widget'
INTERSECT
SELECT customer_id FROM orders WHERE product = 'Gadget';

-- Using EXISTS (often what the optimizer prefers internally)
SELECT DISTINCT o1.customer_id
FROM orders o1
WHERE o1.product = 'Widget'
  AND EXISTS (
      SELECT 1 FROM orders o2
      WHERE o2.customer_id = o1.customer_id AND o2.product = 'Gadget'
  );

EXCEPT — Rows in First But Not Second

Returns rows from the first query that do not appear in the second:

-- Products in current catalog but NOT in archived catalog
SELECT product_name FROM current_catalog
EXCEPT
SELECT product_name FROM archived_catalog;

Finding Missing Data

-- Employees who have NOT submitted a timesheet this month
SELECT employee_id FROM employees
EXCEPT
SELECT DISTINCT employee_id FROM timesheets
WHERE month = DATE_TRUNC('month', NOW())
ORDER BY employee_id;

Anti-Join Alternative

EXCEPT is equivalent to a LEFT JOIN anti-join:

-- Using EXCEPT
SELECT id FROM employees
EXCEPT
SELECT employee_id FROM timesheets WHERE month = '2024-01-01';

-- Equivalent LEFT JOIN (may be faster with good indexes)
SELECT e.id
FROM employees e
LEFT JOIN timesheets t ON t.employee_id = e.id AND t.month = '2024-01-01'
WHERE t.employee_id IS NULL;

ALL Variants

Both INTERSECT and EXCEPT support ALL to preserve duplicates:

-- INTERSECT ALL — keep duplicates (rare use case)
SELECT product FROM cart_a
INTERSECT ALL
SELECT product FROM cart_b;

-- EXCEPT ALL — multiset difference
SELECT product FROM order_items
EXCEPT ALL
SELECT product FROM fulfilled_items;
-- If 'Widget' appears 5 times in orders and 3 times in fulfilled,
-- result contains 2 Widgets (5 - 3)

Combining Multiple Set Operations

Use parentheses to control evaluation order:

-- Without parentheses: left-to-right evaluation
SELECT a FROM t1
UNION ALL
SELECT a FROM t2
EXCEPT
SELECT a FROM t3;

-- With parentheses: explicit order
SELECT a FROM t1
UNION ALL
(
    SELECT a FROM t2
    EXCEPT
    SELECT a FROM t3
);

Type Matching and Casting

When column types don't exactly match, PostgreSQL tries automatic type coercion:

-- This works: INTEGER and BIGINT are compatible
SELECT 42::INTEGER UNION SELECT 100::BIGINT;  -- result is BIGINT

-- This fails: incompatible types
SELECT 'hello' UNION SELECT 42;
-- ERROR: UNION types text and integer cannot be matched

-- Fix with explicit cast
SELECT 'hello' UNION SELECT 42::TEXT;

Set Operations with CTEs

Set operations compose naturally with CTEs:

WITH active_users AS (
    SELECT user_id FROM sessions WHERE last_seen > NOW() - INTERVAL '7 days'
),
paying_users AS (
    SELECT user_id FROM subscriptions WHERE status = 'active'
)
-- Users who are active but not paying (conversion opportunity)
SELECT user_id FROM active_users
EXCEPT
SELECT user_id FROM paying_users
ORDER BY user_id;

Quick Reference