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.

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.

All BigQuery lessons