I am not sure what type of join I need to obtain my desired result. As per the below example I am trying to join table 1
to table 2
but I am missing Q3 in the first table.
table 1:
Quarter | Sales |
---|---|
Q1 | 100 |
Q2 | 200 |
Q4 | 300 |
table 2:
Quarter | Cost |
---|---|
Q1 | 20 |
Q2 | 50 |
Q3 | 70 |
Q4 | 10 |
I want to join both tables such that I get the following result:
Result:
Quarter | Sales | Cost |
---|---|---|
Q1 | 100 | 20 |
Q2 | 200 | 50 |
Q3 | NULL | 70 |
Q4 | 300 | 10 |
I have tried to use a FULL OUTER JOIN on Quarter
from each table. But I think because I’m selecting table1.Quarter
in the SELECT statement, I am getting NULL where Q3 should be. So essentially, I am getting the following which is incorrect:
Quarter | Sales | Cost |
---|---|---|
Q1 | 100 | 20 |
Q2 | 200 | 50 |
NULL | NULL | 70 |
Q4 | 300 | 10 |
Do I simply need to use the correct JOIN type, or is the problem a bit more complex than that?
Advertisement
Answer
Join with a synthesized table that has all the quarters.
SELECT q.quarter, s.sales, c.cost FROM ( SELECT 'Q1' AS quarter UNION SELECT 'Q2' UNION SELECT 'Q3' UNION SELECT 'Q4' ) AS q LEFT JOIN table1 AS s ON a.quarter = b.quarter LEFT JOIN table2 AS c ON a.quarter = c.quarter