UNION ALL and set operations
Sometimes one report needs answers from two different queries in the same layout. UNION ALL stacks them into one result.
How it works
Each branch is an ordinary query with its own filters and grouping. UNION ALL appends the rows of the second branch under the rows of the first. The branches must have the same number of columns, and the names come from the first branch.
- Every branch needs the same number of columns, and columns in the same position must have compatible types.
- Column names come from the first branch. Later branches only supply values.
UNION ALLkeeps every row.UNION DISTINCTremoves duplicate rows. BigQuery needs one of the two words afterUNION.- A literal such as
'make' AS dimensionlabels which branch a row came from.
Common mistake
Columns are matched by position, so swapping two columns in the second branch mixes their values without an error whenever the types are compatible. Write the columns in the same order in every branch.
Worked example
SELECT 'passed' AS outcome, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE test_result = 'PASSED'
AND completed_date BETWEEN '2024-11-01' AND '2024-11-30'
UNION ALL
SELECT 'failed', COUNT(*)
FROM `learn.mot.fact_mot_test`
WHERE test_result = 'FAILED'
AND completed_date BETWEEN '2024-11-01' AND '2024-11-30'Each branch counts one outcome in November 2024 and returns one row.
UNION ALL puts the two rows into one result. The column names come from the first branch.