CODEWITHSUNDEEP
sqlpostgresqljoin
Zephyer
Zephyer

Reputation: 343

SQL - using JOIN while filling missing values with NULL

The most related question I looked into was this but sadly I did not get a solution for my problem there.

I have two tables, both have a similar column. The only difference is that one column is missing a few values. I want to join the tables, so that for the missing value in one column, the join will show the missing values.

Ill provide an example since this might be confusing - 

table 1            table 2
ID count           ID count
1  9               1  2
2  2               2  1
3  1

I want the result to be

table 3
ID count2 count1
1  2      9
2  1      2
3  NULL   1

However, using LEFT OUTER JOIN I could only achieve the table "table 3" without the row for id 3, because it has no representation in table 2.

Can you help me with my problem?

Upvotes: 0

Views: 4173

Answers (1)

Jason Goemaat
Jason Goemaat

Reputation: 29234

A left join would work for your sample data, I'm guessing you want to know what to do if you move the row with id 3 into table 2 so that your query will show all ids. To show all rows from both tables, use a FULL OUTER JOIN:

SELECT CASE WHEN t1.id IS NULL THEN t2.id ELSE t1.id END AS id,
    t2.count as count2, t1.count as count1
FROM t1
    FULL OUTER JOIN t2 ON t2.id = t1.id

Upvotes: 3

Related Questions