CODEWITHSUNDEEP
sqlgoogle-bigquery
Maryam
Maryam

Reputation: 720

BigQuery Outer Join

I have two tables like these:

| id | A|
| ---| --- |
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |

| id | B |
| ---| ---|
| 3 | 1|
| 4 | 2|
| 5 | 3|
| 6 | 4|

and I want this by using sql in bigquery:

| id | A    | B |
| --- | --- | --- |
| 1|10|null|
| 2|20|null|
| 3|30|1|
| 4|40|2|
| 5| null|3|
| 6| null|4|

Upvotes: 1

Views: 3518

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Yet another - least verbose version (following Gordon's one) 

#standardSQL
SELECT *
FROM table1
FULL OUTER JOIN table2
USING (id)

As you can see, when you use USING(id) there is no ambiguity error being raised for id column - as it would if you use ON t1.id = t2.id (as in Tim's version)
Note: this assumes id is the only common column in those tables and rest of the columns are uniquely named - as it is in your example

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want a full join (as Tim points out). I recommend the using clause in this case:

SELECT id, t1.A, t2.B
FROM table1 t1 FULL OUTER JOIN
     table2 t2
     USING (id)
ORDER BY id;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Use a full outer join, assuming you are using Standard SQL:

SELECT
    COALESCE(t1.id, t2.id) AS id,
    t1.A,
    t2.B
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id
ORDER BY
    COALESCE(t1.id, t2.id);

Note that for the id we report whichever value from either table is not NULL, using the COALESCE function. Assuming neither table contains NULL values for the id, it should be guaranteed that at least one of the two tables has a non NULL value for the id.

Upvotes: 1

Related Questions