Database Reference
In-Depth Information
Implicit UNION ALL
Standard SQL defines two types of
UNION
:
UNION
and
UNION ALL
.
UNION
(without
ALL
) combines two tables but ignores duplicates. If table A has 10
rows and table B has 7 rows, but two of the rows in table B are identical to
rows in table A, the
UNION
of those two tables will have 15 rows—all the rows
in A and B with the duplicates removed. As you can probably imagine, this
is a computationally expensive operation.
UNION ALL
, however, performs blind concatenation. If table A has 10 rows
and table B has 7 rows, when you
UNION ALL
them together, you'll get all
17 rows of A and B together.
UNION ALL
is a much less computationally
expensive operation because it doesn't have to find the duplicate values.
Currently, neither
UNION
nor
UNION ALL
is directly supported in BigQuery.
So why is it discussed here? BigQuery does support
UNION ALL
via a
nonstandard syntax: a comma-separated list of tables in the
FROM
clause.
This is mentioned in the same section with
JOIN
operations because
standard SQL uses this notation to mean
JOIN
.
