How to Use UNION in BigQuery (ALL vs DISTINCT)

GoogleSQL requires you to say which one you mean, so a plain UNION is a syntax error. UNION ALL simply concatenates the inputs and is much cheaper; UNION DISTINCT deduplicates across the whole combined result, which costs a shuffle.

SELECT order_id, amount FROM orders_2025
UNION ALL
SELECT order_id, amount FROM orders_2026;

Columns match by position

Set operations line up the branches left to right by position and ignore the column names entirely, so the first column of one branch must be type-compatible with the first column of the other. The output takes its names from the first branch, which is why a wrong-order SELECT gives you silently swapped data rather than an error.

-- Wrong: amount lands in the order_id column
SELECT order_id, amount FROM orders_2025
UNION ALL
SELECT amount, order_id FROM orders_2026;

When the types do not line up

If one branch has an INT64 where the other has a STRING, the query fails instead of coercing. CAST the narrower side explicitly. A branch that is missing a column altogether needs an explicit typed NULL so the positions still align.

SELECT order_id, amount, customer_id FROM orders_2025
UNION ALL
SELECT order_id, CAST(amount AS NUMERIC), CAST(NULL AS STRING) FROM orders_2026;

INTERSECT and EXCEPT

The other two set operators follow the same rule and require the DISTINCT keyword. EXCEPT DISTINCT returns rows in the first branch that are absent from the second, which makes it a quick way to diff two tables.

SELECT order_id FROM orders_2026
EXCEPT DISTINCT
SELECT order_id FROM shipped_orders;

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser