What are UNION, INTERSECT and EXCEPT?

Question

Accepted Answer

These are **set operations** that combine the results of two queries. **`UNION`** combines rows (removing duplicates), **`INTERSECT`** returns rows in both, **`EXCEPT`** returns rows in the first but not the second. They operate on whole result sets vertically (stacking rows), unlike JOINs (which combine columns horizontally).

## UNION — combine results (deduplicated)

```sql
-- combine two result sets into one, removing duplicates
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;
-- → all distinct names/emails from BOTH tables, no duplicates

-- UNION ALL — keeps duplicates (faster, no dedup step)
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;
```

`UNION` stacks the rows of two queries (which must have **compatible columns** — same number, compatible types), removing duplicates. **`UNION ALL`** keeps duplicates and is faster (no deduplication) — use it when you know there are no duplicates or don't care.

## INTERSECT — rows in BOTH

```sql
-- people who are BOTH customers AND suppliers
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;
```

## EXCEPT (or MINUS in Oracle) — rows in the first, not the second

```sql
-- customers who are NOT suppliers
SELECT email FROM customers
EXCEPT
SELECT email FROM suppliers;
```

## Requirements and a UNION vs UNION ALL note

```text
Requirements for set operations:
  ✓ Both queries must have the SAME number of columns
  ✓ Corresponding columns must have COMPATIBLE types
  ✓ Column names come from the FIRST query

UNION vs UNION ALL:
  UNION     → removes duplicates (extra work — sorts/compares)
  UNION ALL → keeps all rows (faster) — prefer it when dedup isn't needed
```

## UNION vs JOIN

```text
JOIN  → combines tables HORIZONTALLY (adds columns from related rows)
UNION → combines results VERTICALLY (stacks rows from separate queries)
→ Different purposes: JOIN for related data across tables, UNION for merging
  similar result sets.
```

## Why it matters

Set operations (`UNION`, `INTERSECT`, `EXCEPT`) are useful SQL features for **combining result sets**, and understanding them is valuable for queries that need to merge, intersect, or subtract data from multiple sources.

The most commonly-used is **`UNION`** (combining rows from two queries — like merging similar data from different tables), and an important practical distinction is **`UNION` vs `UNION ALL`**: UNION removes duplicates (incurring a sort/compare cost) while **`UNION ALL` keeps all rows and is faster** — knowing to prefer `UNION ALL` when deduplication isn't needed is a useful performance practice. `INTERSECT` (rows in both) and `EXCEPT` (rows in the first but not the second) handle set-difference logic concisely.

Understanding the **requirements** (matching column counts and compatible types) and the key conceptual distinction between **set operations and JOINs** — set operations combine results *vertically* (stacking rows from separate queries) while JOINs combine *horizontally* (adding columns from related rows) — is important for choosing the right tool.

Since combining result sets from multiple queries is a real need (merging data from similar tables, finding common or distinct entries between sets), and since understanding the operations, the UNION/UNION ALL performance distinction, and how set operations differ from JOINs helps you express these requirements correctly and efficiently, knowing set operations is useful, practically-relevant SQL knowledge that complements JOINs and enables a class of queries involving merging or comparing whole result sets.