Outer Union equivalent - GBQ

user_11883834 0
Viewed 230

I've seen how an outer union would work in this post

https://stackoverflow.com/a/52524364/11883834

and I wanted to know how I would go about implementing something similar in Google Big Query, as when I try to run an outer union, I just get given back an error (I think because outer unions are not supported in GBQ).

E.G.

Table1
+---+---+
| a | b |
+---+---+
| 1 | X |
| 2 | Y |
+---+---+

Table2

+---+---+
| b | d |
+---+---+
| U | 1 |
+---+---+

CREATE TABLE OuterUnionTable AS
SELECT * FROM Table1
OUTER UNION CORR
SELECT * FROM Table2

OuterUnionTable
+----+----+---+
| a  | b  | d |
+----+----+---+
|  1 | X  |   |
|  2 | Y  |   |
|    | U  | 1 |
+----+----+---+
google-bigquery union sql
1 Answers

Looks like you are looking for below

select a, b, d 
from `project.dataset.table1`
full outer join `project.dataset.table2`
using(b)

if applied to sample data in your question - output is

enter image description here

You can avoid to specify all columns - like in below example (but you will not control order of columns in this case)

select * 
from `project.dataset.table1`
full outer join `project.dataset.table2`
using(b)

In case if you need to preserve order - see below

select t1.*, t2.* except(b)
from `project.dataset.table1` t1
full outer join `project.dataset.table2` t2
using(b)

In case if you really need union - you can use below

select a, b, null as d from `project.dataset.table1`
union all
select null as a, b, d from `project.dataset.table2`
user_52219440
Related