# UNION, INTERSECT, EXCEPT

> Combine query results with UNION, INTERSECT, and EXCEPT, with or without duplicates.

---

LLMS index: [llms.txt](/en/llms.txt)

---

Set operations combine two result sets row-by-row. All three require the queries on either side to return the same number of columns with compatible types. `UNION` returns rows in either side (duplicates removed). `INTERSECT` returns rows in both. `EXCEPT` returns rows in the first that are not in the second. Each has an `ALL` variant. `UNION ALL` concatenates both inputs. `INTERSECT ALL` keeps the smaller occurrence count of each row; `EXCEPT ALL` subtracts the right count from the left, with a minimum of zero. The latter two still need to match and count rows.

## What you'll learn {#learn}

- `UNION` vs `UNION ALL` and when each is appropriate
- `INTERSECT` and `INTERSECT ALL`
- `EXCEPT` and `EXCEPT ALL`
- Combining set operations and how precedence works
- Performance: when `UNION ALL` is meaningfully faster

## Example {#example}

```sql {num="32.1" caption="UNION, INTERSECT, EXCEPT"}
-- UNION removes duplicates across the combined result
SELECT 1 AS n UNION SELECT 2 UNION SELECT 1;

-- UNION ALL keeps duplicates (cheaper, no sort/hash)
SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2;

-- INTERSECT: rows present in both
SELECT id FROM fruits WHERE id <= 3
INTERSECT
SELECT id FROM fruits WHERE id >= 2;

-- EXCEPT: rows in the first query but not the second
SELECT id FROM fruits WHERE id <= 4
EXCEPT
SELECT id FROM fruits WHERE id >= 3;

-- Order of evaluation: INTERSECT binds tighter than UNION / EXCEPT.
-- Use parentheses to be explicit:
(SELECT id FROM fruits WHERE id < 5)
UNION
(SELECT id FROM fruits WHERE id > 100);

-- ORDER BY applies to the combined result; column refs by position or alias
SELECT id AS n FROM fruits WHERE id < 3
UNION
SELECT id     FROM fruits WHERE id > 100
ORDER BY n;
```

`UNION` deduplicates by sort or hash on the full row — that work is real and shows up in `EXPLAIN`. If you know the inputs cannot duplicate (because the predicates are disjoint, for example), prefer `UNION ALL`. On large queries the difference can be substantial.

`INTERSECT` and `EXCEPT` are less common in application code but handy for analysis: "users who clicked A and B," "products in catalog A that are not in catalog B."

Set operations are *set-of-rows* operations. The column types must align across the inputs — `int` and `text` cannot be unioned directly. Names come from the first query's column names.

## Run the example {#run}

To run (requires `fruits`):

```text
$ psql -f source/union-intersect-except.sql postgres
 n
---
 1
 2
(2 rows)

 n
---
 1
 1
 2
(3 rows)
 ...
```

## Common pitfalls {#pitfalls}

- `UNION` instead of `UNION ALL` on queries that cannot produce duplicates wastes CPU on deduplication. Pick `UNION ALL` unless duplicates are possible and unwanted.
- Misaligned column counts or types: `SELECT id, name FROM t UNION SELECT id FROM t2` is an error. Pad with `NULL::text` or align columns.
- `ORDER BY` after a set operation refers to the *combined* result; it cannot use table-qualified column names. Use positions (`ORDER BY 1`) or aliases from the first query.
- `EXCEPT` is sensitive to NULL: `EXCEPT` treats two NULLs as equal (like `DISTINCT`), so a row with NULL on the left and NULL on the right is removed. This is usually what you want — but worth remembering.

> [!TIP] Tip
> When you find yourself writing `SELECT ... WHERE x = 1 UNION ALL SELECT ... WHERE x = 2 UNION ALL ...`, consider whether a single `WHERE x IN (1, 2, 3)` would do. Set operations are for genuinely different queries with results that need to be combined.

> [!NOTE] Try it
> Write two `SELECT`s from fruits with different `WHERE` clauses and combine with `UNION`. Then change to `UNION ALL` and observe the duplicate count change (if any). Then try `INTERSECT` and `EXCEPT` between them.

## Downloads {#downloads}

SQL file: [union-intersect-except.sql](/examples/source/union-intersect-except.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)