CODEWITHSUNDEEP
sqlsql-server-2005
user278618
user278618

Reputation: 20292

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

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 ?

Upvotes: 0

Views: 301

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

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'

Upvotes: 5

thomaux
thomaux

Reputation: 19738

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.

Upvotes: 0

Related Questions