SQL - using JOIN while filling missing values with NULL

Question

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?

Jason Goemaat · Accepted Answer · 2014-12-08 21:48:13Z

3

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

answered Dec 8, 2014 at 21:48

Jason Goemaat

29.3k15 gold badges89 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zephyer Over a year ago

you are a wizard! thank you very much, i used hours on this without knowing CASE or FULL are available for such a scenario.

Collectives™ on Stack Overflow

SQL - using JOIN while filling missing values with NULL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related