Fill missing values in SQL query

Question

I have a current SQL request that works OK

SELECT
    *
FROM
    firstTab 
LEFT JOIN 
    secondTab ON firstTab.id = secondTab.refId 
LEFT JOIN
    thirdTab ON firstTab.id = thirdTab.refId 
WHERE 
    secondTab.colY IN ('value1', 'value2') 
    AND thirdTab.colZ IN ('value3', 'value4')

So far so good.

The problem is that I get the result only for rows when there are data for the values in colA or colB (which is the normal behaviour).

(currently I only get

         colY      colZ       currentResult
--------------------------------------------
row1    value1    value3          resultA
row3    value2    value3          resultB

)(normal behaviour)

I wish to do fill the missing values with some NULL values in such a way

         colY      colZ       currentResult
--------------------------------------------
row1    value1    value3          resultA
row2    value1    value4           NULL
row3    value2    value3          resultB
row4    value2    value4           NULL

('value1','value2') and ('value3','value4') are given , I know them in advance

I tried to "init" the result with a cartesian product with CROSS JOIN of secondTab and thirdTab but then I am stuck.

How can I achieve this?

Thanks in advance

thanks for the remark, I edited the question

Makoto
– Makoto

2019-08-29 19:38:07 +00:00
Commented Aug 29, 2019 at 19:38 — Makoto
– Makoto, Commented Aug 29, 2019 at 19:38

Gordon Linoff · Accepted Answer · 2019-08-29 19:37:40Z

2

You need to move the filtering conditions to the ON clauses:

Select *
FROM firstTab LEFT JOIN 
     secondTab
     ON firstTab.id = secondTab.refId AND secondTab.colY in ('value1','value2') LEFT JOIN
     thirdTab
     ON firstTab.id = thirdTab.refId and thirdTab.colZ in ('value3','value4');

Unmatched values from the LEFT JOIN are NULL. The WHERE clause will filter these out, turning the outer joins into inner joins.

answered Aug 29, 2019 at 19:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

MihaiM · Accepted Answer · 2019-08-29 19:38:19Z

1

You need to do an FULL OUTER JOIN:

Select *
FROM firstTab LEFT JOIN 
     secondTab
     ON firstTab.id = secondTab.refId FULL OUTER JOIN
     thirdTab
     ON firstTab.id = thirdTab.refId 
WHERE secondTab.colY in ('value1','value2') and
      thirdTab.colZ in ('value3','value4')

Join ideas

answered Aug 29, 2019 at 19:38

MihaiM

2092 silver badges7 bronze badges

2 Comments

Makoto Over a year ago

thanks MihaiM for your response, I did try the FULL OUTER JOIN but it did not seem to work (or maybe I used it wrong ?)

jarlh Over a year ago

With that WHERE clause you'll still get INNER JOIN result.

Collectives™ on Stack Overflow

Fill missing values in SQL query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related