UNION ALL operator in BigQuery

The UNION ALL operator in BigQuery is used to concatenate the result sets of two or more SELECT queries into a single result set.

Unlike UNION, the UNION ALL operator does not remove duplicate rows. Every row returned by each SELECT statement is included in the final output.

Handling Duplicate Rows

BigQuery provides two UNION variants to control duplicate handling:

• UNION ALL – Includes all rows, including duplicates.
• UNION (or UNION DISTINCT) – Removes duplicate rows from the combined result set.

SQL Syntax

SELECT column1, column2, ...
FROM `project.dataset.table1`
[WHERE conditions]

UNION ALL

SELECT column1, column2, ...
FROM `project.dataset.table2`
[WHERE conditions];    

Key Points

• UNION ALL is faster than UNION because it does not perform duplicate elimination.
• Skipping duplicate checks reduces processing time and query cost.

Example: Combine sales data from two regions.

SQL

SELECT order_id, customer_id, order_amount
FROM `project.dataset.sales_india`

UNION ALL

SELECT order_id, customer_id, order_amount
FROM `project.dataset.sales_usa`;