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.

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:

SELECT 'a' AS letter
UNION
SELECT 'b'

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:

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

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:

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

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

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

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:

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

To match by name, add CORRESPONDING:

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

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

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

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).

Column types

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

SELECT 1 AS x
UNION ALL
SELECT 2.5

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

SELECT 1 AS x
UNION ALL
SELECT 'two'

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:

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

This is the same rule GROUP BY and SELECT DISTINCT use. See NULL in BigQuery.

ORDER BY and LIMIT apply to 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:

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

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

See How a query is billed.