Joining two tables where keys from one table is missing

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?

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

Advertisement

Answer