> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dynamosql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Set Operations

> UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, and INTERSECT ALL.

Set operations combine the results of two or more `SELECT` statements. Both queries must produce the same number of columns, and corresponding columns must have compatible types.

## UNION

Combines two result sets and removes duplicate rows:

```sql theme={null}
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
```

Because deduplication requires comparing every row, `UNION` is slower than `UNION ALL`. Use it only when you actually need distinct results.

## UNION ALL

Combines two result sets and keeps all rows, including duplicates:

```sql theme={null}
SELECT customer_id, total FROM myschema.orders_2024
UNION ALL
SELECT customer_id, total FROM myschema.orders_2025
```

`UNION ALL` is faster than `UNION` because no deduplication step is needed. Prefer it when duplicates are acceptable or when you know the two result sets are already disjoint.

## EXCEPT

Returns rows from the left query that do not appear in the right query, with duplicates removed:

```sql theme={null}
SELECT id FROM myschema.all_customers
EXCEPT
SELECT id FROM myschema.opted_out_customers
```

## EXCEPT ALL

Like `EXCEPT`, but preserves multiplicity. If a row appears *m* times on the left and *n* times on the right, the result contains `max(m - n, 0)` copies of that row:

```sql theme={null}
SELECT product_id FROM myschema.cart_items
EXCEPT ALL
SELECT product_id FROM myschema.backordered_items
```

## INTERSECT

Returns rows that appear in both the left and right query, with duplicates removed:

```sql theme={null}
SELECT id FROM myschema.premium_customers
INTERSECT
SELECT id FROM myschema.active_customers
```

## INTERSECT ALL

Like `INTERSECT`, but preserves multiplicity. If a row appears *m* times on the left and *n* times on the right, the result contains `min(m, n)` copies of that row:

```sql theme={null}
SELECT product_id FROM myschema.wishlist_items
INTERSECT ALL
SELECT product_id FROM myschema.in_stock_items
```

## ORDER BY after set operations

`ORDER BY` is supported after any set operation. However, you must reference columns by **position** (1-based) or by **alias** — not by the original column name from the base tables:

```sql theme={null}
-- Valid: column position
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
ORDER BY 1, 2

-- Valid: alias defined in the SELECT list
SELECT id, name AS customer_name FROM myschema.active_customers
UNION
SELECT id, name AS customer_name FROM myschema.archived_customers
ORDER BY customer_name
```

`LIMIT` and `OFFSET` / `FETCH` may follow `ORDER BY` as usual:

```sql theme={null}
SELECT id FROM myschema.new_orders
UNION ALL
SELECT id FROM myschema.backfill_orders
ORDER BY 1 DESC
LIMIT 100
```

## Chaining Multiple Set Operations

```sql theme={null}
SELECT id, status FROM myschema.orders WHERE region = 'us-east'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'us-west'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'eu-central'
```

Set operations associate left-to-right. `UNION ALL` followed by `EXCEPT` first unions the two queries, then removes rows from the third:

```sql theme={null}
SELECT id FROM myschema.base_set
UNION
SELECT id FROM myschema.additions
EXCEPT
SELECT id FROM myschema.exclusions
```

## Not Supported

* Parenthesized set operations (e.g., `(SELECT ... UNION SELECT ...) UNION SELECT ...`)