UNION operator in BigQuery

The UNION operator combines the results of two or more input queries by pairing columns from the results of each query and vertically concatenating them.

-- This works
query1 UNION ALL query2 UNION ALL query3

-- This works
query1 UNION ALL (query2 UNION DISTINCT query3)

For example, a simple query may look like this:

SELECT "apple" AS fruit, 1 as number, "football" as ball

When we use UNION to connect two queries together

SELECT "apple" AS fruit, 1 as number, "football" as ball
UNION ALL
SELECT "banana" AS fruit, 2 as number, "basketball" as ball;

If a given row R appears exactly m times in the first input query and n times in the second input query (m >= 0, n >= 0):

• For UNION ALL, R appears exactly m + n times in the result.
• For UNION DISTINCT, the DISTINCT is computed after the UNION is computed, so R appears exactly one time.

SELECT 5  as  col1
UNION DISITINCT
SELECT 5  as  col1

/*----------+
 | col1     |
 +----------+
 | 5        |
 +----------*/

SELECT 5  as  col1
UNION ALL
SELECT 5  as  col1

/*----------+
 | col1     |
 +----------+
 | 5        |
 +----------*
 | 5        |
 +----------*/

SELECT 5  as  col1
UNION DISITINCT
SELECT 6  as  col1

/*----------+
 | col1     |
 +----------+
 | 5        |
 +----------*
 | 6        |
 +----------*/