# UNION, INTERSECT and EXCEPT in BigQuery

> In BigQuery, UNION must say ALL or DISTINCT, INTERSECT and EXCEPT only support DISTINCT, columns match by position unless you add CORRESPONDING or BY NAME, and NULLs count as equal.

Source: https://docs.querylab.io/bigquery/set-operations/

Updated: 2026-10-14

Set operators combine the results of two queries. BigQuery has the usual three, `UNION`, `INTERSECT` and `EXCEPT`, with stricter rules than Postgres or MySQL: you must always say whether duplicates are kept, and `INTERSECT` and `EXCEPT` can only remove them.

## UNION needs ALL or DISTINCT

In most databases a bare `UNION` removes duplicates. BigQuery refuses to guess:

**UNION without a quantifier**

```sql
SELECT 'a' AS letter
UNION
SELECT 'b'
```

BigQuery rejects this query:

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]
```

`UNION ALL` keeps every row from both sides. `UNION DISTINCT` removes duplicate rows, which costs a sort or hash over the whole result. Here Ben appears in both months:

**four rows with ALL, three with DISTINCT**

```sql
WITH jan AS (SELECT * FROM UNNEST(['ana', 'ben']) AS customer),
     feb AS (SELECT * FROM UNNEST(['ben', 'cai']) AS customer)
SELECT 'all' AS kind, COUNT(*) AS customers FROM (SELECT customer FROM jan UNION ALL SELECT customer FROM feb)
UNION ALL
SELECT 'distinct', COUNT(*) FROM (SELECT customer FROM jan UNION DISTINCT SELECT customer FROM feb)
ORDER BY kind
```

Result in BigQuery (2 rows):

| kind | customers |
| --- | --- |
| all | 4 |
| distinct | 3 |

Use `UNION ALL` unless you need the deduplication. It is faster, and it never hides rows you didn't expect to be duplicates.

## INTERSECT and EXCEPT

`INTERSECT DISTINCT` keeps rows that appear on both sides, and `EXCEPT DISTINCT` keeps rows of the first query that don't appear in the second:

**customers in both months, and customers who left**

```sql
WITH jan AS (SELECT * FROM UNNEST(['ana', 'ben', 'dee']) AS customer),
     feb AS (SELECT * FROM UNNEST(['ben', 'cai', 'dee']) AS customer)
SELECT 'both months' AS kind, customer FROM (SELECT customer FROM jan INTERSECT DISTINCT SELECT customer FROM feb)
UNION ALL
SELECT 'churned', customer FROM (SELECT customer FROM jan EXCEPT DISTINCT SELECT customer FROM feb)
ORDER BY kind, customer
```

Result in BigQuery (3 rows):

| kind | customer |
| --- | --- |
| both months | ben |
| both months | dee |
| churned | ana |

The `ALL` versions, which keep duplicate counts, don't exist in BigQuery:

**EXCEPT ALL**

```sql
SELECT x FROM UNNEST([1, 1, 2]) AS x
EXCEPT ALL
SELECT x FROM UNNEST([1]) AS x
```

BigQuery rejects this query:

```text
EXCEPT ALL is not supported. Only EXCEPT DISTINCT is supported.
```

For a multiset difference, number the duplicates on each side with `ROW_NUMBER() OVER (PARTITION BY ...)` and use `EXCEPT DISTINCT` on the numbered rows, or count with `GROUP BY` and subtract.

## Columns match by position

By default the first column of one query lines up with the first column of the other, whatever the names. The result takes its names from the first query. Swap two columns of the same type and the data is silently mixed up:

**the second query lists last_name first**

```sql
SELECT 'Ada' AS first_name, 'Lovelace' AS last_name
UNION ALL
SELECT 'Turing' AS last_name, 'Alan' AS first_name
```

Result in BigQuery (2 rows):

| first_name | last_name |
| --- | --- |
| Ada | Lovelace |
| Turing | Alan |

To match by name, add `CORRESPONDING`:

**UNION ALL CORRESPONDING**

```sql
SELECT 'Ada' AS first_name, 'Lovelace' AS last_name
UNION ALL CORRESPONDING
SELECT 'Turing' AS last_name, 'Alan' AS first_name
ORDER BY first_name
```

Result in BigQuery (2 rows):

| first_name | last_name |
| --- | --- |
| Ada | Lovelace |
| Alan | Turing |

`BY NAME` gives the same result here. It is the spelling DuckDB uses:

**UNION ALL BY NAME**

```sql
SELECT 'Ada' AS first_name, 'Lovelace' AS last_name
UNION ALL BY NAME
SELECT 'Turing' AS last_name, 'Alan' AS first_name
ORDER BY first_name
```

Result in BigQuery (2 rows):

| first_name | last_name |
| --- | --- |
| Ada | Lovelace |
| Alan | Turing |

The two differ when the sides don't have the same columns:

- `CORRESPONDING` keeps only the columns both sides have and drops the rest without a warning.
- `BY NAME` is short for `STRICT CORRESPONDING`: different column sets are an error.
- `FULL` or `LEFT` before the operator keeps every column (or every column of the first query) and fills the missing ones with NULL: `SELECT ... FULL UNION ALL CORRESPONDING SELECT ...`.
- `CORRESPONDING BY (col1, col2)` and `BY NAME ON (col1, col2)` pick the columns to match.

When you combine tables that may gain columns over time, such as a set of daily tables, matching by name is safer than `SELECT *` by position. `FULL ... CORRESPONDING` keeps working when one side gains a column. In Querylab.io, `UNION` arms that select the same columns in a different order get a hint with a "Use UNION BY NAME" quick fix ([Code quality](https://docs.querylab.io/writing-sql/code-quality/)).

## Column types

Matching columns must share a type, or be convertible to a common one. Integers and floating-point numbers combine into `FLOAT64`:

**INT64 and FLOAT64 combine**

```sql
SELECT 1 AS x
UNION ALL
SELECT 2.5
```

Result in BigQuery (2 rows):

| x |
| --- |
| 1.0 |
| 2.5 |

A number and a string don't, so cast one side:

**INT64 and STRING**

```sql
SELECT 1 AS x
UNION ALL
SELECT 'two'
```

BigQuery rejects this query:

```text
Column 1 in UNION ALL has incompatible types: INT64, STRING at [3:1]
```

## NULLs are equal here

In a `WHERE` clause, `NULL = NULL` isn't true. Set operators treat NULLs as equal, so `UNION DISTINCT` keeps one NULL and `INTERSECT` matches NULL with NULL:

**three NULLs become one**

```sql
SELECT x FROM UNNEST([1, NULL, NULL]) AS x
UNION DISTINCT
SELECT x FROM UNNEST([CAST(NULL AS INT64)]) AS x
```

Result in BigQuery (2 rows):

| x |
| --- |
| 1 |
| NULL |

This is the same rule `GROUP BY` and `SELECT DISTINCT` use. See [NULL in BigQuery](https://docs.querylab.io/bigquery/null-handling/).

## ORDER BY and LIMIT on the whole result

An `ORDER BY` or `LIMIT` at the end applies to the combined result, not to the last query. To sort or limit one side, put that side in parentheses: `(SELECT ... ORDER BY x LIMIT 10) UNION ALL (SELECT ...)`. Mixing different operators without parentheses, like `a UNION ALL b EXCEPT DISTINCT c`, is an error; parenthesize to say which runs first.

## What it costs

A column read by several branches of a set operation is billed once. Reading `word` from the same table on both sides of a `UNION ALL` costs the same as reading it once:

**the same column on both sides**

```sql
SELECT word FROM `bigquery-public-data.samples.shakespeare`
UNION ALL
SELECT word FROM `bigquery-public-data.samples.shakespeare`
```

BigQuery dry run: this query processes 1.3 MB.

**the column read once**

```sql
SELECT word FROM `bigquery-public-data.samples.shakespeare`
```

BigQuery dry run: this query processes 1.3 MB.

See [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-billed/).

Try INTERSECT and EXCEPT on the same two lists. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, set operators](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set_operators)

- [Conversion rules, supertypes](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules)