Not using Where when Right side is null - problem with left join

Question

I have a query:

Select a.Col1,b.Col2
From a
Left Join B b ON a.C = b.C
Where b.D ='someValue'

But when right side is null, I don't get record , so Left Join doesn't work.

Do you know how to change this query to correct query ?

Answer 1

The query returns nothing when the right side is null due to your where clause. If there's no table b referred, your where clause will not be met.

I'm not quite sure how to correct your query. In my opinion, this query insinuates the link between both tables is required and thus your result is correct, semantically.

Answer 2

Move the additional join condition into the on clause:

Select a.Col1,b.Col2
From a
Left Join B b ON a.C = b.C and b.D = 'someValue'