Support UNION function in BigQuery SQL

BigQuery does not seem to have support for UNION yet: https://developers.google.com/bigquery/docs/query-reference

(I don't mean unioning tables together for the source. It has that.)

Is it coming soon?

Solution 1:

If you want UNION so that you can combine query results, you can use subselects in BigQuery:

SELECT foo, bar 
FROM
  (SELECT integer(id) AS foo, string(title) AS bar 
   FROM publicdata:samples.wikipedia limit 10),
  (SELECT integer(year) AS foo, string(state) AS bar 
   FROM publicdata:samples.natality limit 10);

This is almost exactly equivalent to the SQL

SELECT id AS foo, title AS bar 
FROM publicdata:samples.wikipedia limit 10
UNION ALL
SELECT year AS foo, state AS bar 
FROM publicdata:samples.natality limit 10;

(note that if want SQL UNION and not UNION ALL this won't work)

Alternately, you could run two queries and append the result.

Solution 2:

BigQuery recently added support for Standard SQL, including the UNION operation.

When submitting a query through the web UI, just make sure to uncheck "Use Legacy SQL" under the SQL Version rubric:

Solution 3:

You can always do:

SELECT * FROM (query 1), (query 2);

It does the same thing as :

SELECT * from query1 UNION select * from query 2;