CODEWITHSUNDEEP
sqlgoogle-bigquery
sergiomahi
sergiomahi

Reputation: 992

Merge two tables on sql when keys don't fully match

So basically I want to perform a very simple action and I can't think of a simple work around to merge two tables and sum the number of days.

Imagine my tables are these.

T1

id   |   days
---  -  ------
 1   |     2
 2   |     4
 3   |     1

T2

id   |   days
---  -  ------
 1   |     2
 3   |     1
 4   |     2

So my output should look like this

id   |   days
---  -  ------
 1   |     4
 2   |     4
 3   |     2
 4   |     2

I've tried using full outer join, but then I can't select the id column because it's ambiguous. Another thing I've thought is to do a UNION and group by id and sum days, but this would be very hard (since my data is way more complicated than this example and have several columns).

I'm working on Big Query.

Upvotes: 0

Views: 550

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

Below is for BigQuery Standard SQL

I've tried using full outer join, but then I can't select the id column because it's ambiguous

You can use USING() instead of ON to eliminate such an ambiguity

#standardSQL
SELECT id, IFNULL(t1.days, 0) + IfNULL(t2.days, 0) AS says
FROM `project.dataset.table1` t1
FULL OUTER JOIN `project.dataset.table2` t2
USING(id)

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 1 id, 2 days UNION ALL
  SELECT 2, 4 UNION ALL
  SELECT 3, 1 
), `project.dataset.table2` AS (
  SELECT 1 id, 2 days UNION ALL
  SELECT 3, 1 UNION ALL
  SELECT 4, 2 
)
SELECT id, IFNULL(t1.days, 0) + IFNULL(t2.days, 0) AS says
FROM `project.dataset.table1` t1
FULL OUTER JOIN `project.dataset.table2` t2
USING(id)

with output

Row id  days     
1   1   4    
2   2   4    
3   3   2    
4   4   2

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use full outer join -

   select
     coalesce(table1.id,table2.id) as id,
     coalesce(table1.days,0)+coalesce(table2.days,0)
   from table1 full outer join table2 on table1.id=table2.id

Upvotes: 2

Related Questions