# UNION, INTERSECT and EXCEPT — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/c-setops

> Combine results vertically.

## Set semantics

Set operations combine query results with the same number and types of columns. `UNION` removes duplicates (with a sort or hash cost) while `UNION ALL` keeps them and is cheaper, so use it when duplicates are impossible or wanted. `INTERSECT` returns rows in both results and `EXCEPT` rows in the first but not the second. Set operations compare NULLs as equal, unlike `=`.

## Four set operations, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). UNION deduplicates cities and adds Mumbai; UNION ALL keeps all 8 rows including NULL cities; customer 3 ordered in February but not January; customers 2 and 3 have both paid and refunded or cancelled orders.

```sql
SELECT city FROM customers WHERE city IS NOT NULL
UNION SELECT 'Mumbai' ORDER BY 1;

SELECT count(*) AS union_all_rows FROM (
  SELECT city FROM customers UNION ALL SELECT city FROM customers) t;

-- Customers who ordered in February but not in January
SELECT customer_id FROM orders WHERE ordered_on >= '2026-02-01' AND ordered_on < '2026-03-01'
EXCEPT
SELECT customer_id FROM orders WHERE ordered_on < '2026-02-01';

SELECT customer_id FROM orders WHERE status = 'paid'
INTERSECT
SELECT customer_id FROM orders WHERE status IN ('refunded', 'cancelled') ORDER BY 1;
```

Output:

```
  city  
--------
 Delhi
 Mumbai
 Pune
(3 rows)

 union_all_rows 
----------------
              8
(1 row)

 customer_id 
-------------
           3
(1 row)

 customer_id 
-------------
           2
           3
(2 rows)
```

## Prefer UNION ALL by default

Only pay for duplicate removal when you actually need it.

**Quiz:** Which operation returns rows in the first query but not the second?

- [x] EXCEPT
- [ ] INTERSECT
- [ ] UNION
- [ ] UNION ALL

*Answer:* EXCEPT. Set difference.