CODEWITHSUNDEEP
sqlgoogle-bigqueryunion
mdahlman
mdahlman

Reputation: 9400

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?

Upvotes: 27

Views: 56367

Answers (6)

Weezy.F
Weezy.F

Reputation: 493

This helped me out very much for doing a UNION INTERSECT with big query's StandardSQL. 

#standardSQL
WITH
  a AS (
  SELECT
    *
  FROM
    table_a),
  b AS (
  SELECT
    *
  FROM
    table_b)
SELECT
  *
FROM
  a INTERSECT DISTINCT
SELECT
  *
FROM
  b

I STOLE/MODIFIED THIS EXAMPLE FROM: https://gist.github.com/yancya/bf38d1b60edf972140492e3efd0955d0

Upvotes: 1

Joshua Conner
Joshua Conner

Reputation: 912

Note that, if you're using standard SQL, the comma operator now means JOIN - you have to use the UNION syntax if you want a union:

In legacy SQL, the comma operator , has the non-standard meaning of UNION ALL when applied to tables. In standard SQL, the comma operator has the standard meaning of JOIN.

For example:

#standardSQL
SELECT
    column_name,
    count(*)
from
    (SELECT * FROM me.table1 UNION ALL SELECT * FROM me.table2)
group by 1

Upvotes: 2

assaflavi
assaflavi

Reputation: 427

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: enter image description here

Upvotes: 6

vgt
vgt

Reputation: 217

You can always do:

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

It does the same thing as :

SELECT * from query1 UNION select * from query 2;

Upvotes: 3

Emre Colak
Emre Colak

Reputation: 814

Unions are indeed supported. An excerpt from the link that you posted:

Note: Unlike many other SQL-based systems, BigQuery uses the comma syntax to indicate table unions, not joins. This means you can run a query over several tables with compatible schemas as follows:

// Find suspicious activity over several days

SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url
  FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503]
  WHERE event.username = 'root' AND NOT event.source_ip.is_internal;

Upvotes: 0

Jordan Tigani
Jordan Tigani

Reputation: 26637

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.

Upvotes: 61

Related Questions