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:

table 2:

I want to join both tables such that I get the following result:

Result:

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:

Do I simply need to use the correct JOIN type, or is the problem a bit more complex than that?

CodePudding user response：

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