LEFT JOIN and WHERE 1

[imstillatwork]

This question is about using left joins, and a where constrain on a field in the joined table.

I I have a really simple query like so...

 SELECT
  a.field1a
  ,b.field1b
 FROM tableA as a
 LEFT JOIN tableB as b
 ON a.id = b.a_id

 WHERE b.this = 1

so something like this works great. but if there are no matching rows in tableB, I don't even get my data from tableA, which I still need...

The sql above makes sense, and the reason I don't get the data from tableA makes sense too...

but what do I do if I want to get the data from tableA even if there is no data in tableB matching the WHERE claus?

I mean, I am using a left join because I want tableA data even if there is none in tableB. but as soon as you constain it to a condition of tableB in the WHERE clause, that 'theory' dies...

 

[r937]

this is a classic situation, and here's the solution --

select a.field1a
     , b.field1b
  from tableA as a
left outer
  join tableB as b
    on a.id = b.a_id
   [b]and[/b] b.this = 1

the reason is, in a LEFT OUTER JOIN, for any row in the left table that has no match in the right table, as defined by the ON clause, then the column(s) from the right table are NULL for that row

so for an unmatched row, all b columns would be NULL, including b.this

but your WHERE clause was checking b.this=1, and of course NULL is not equal to anything, so that unmatched row is thrown away by the WHERE clause

by moving the condition to the ON clause, it becomes a specification for the join, and thus an unmatched row is one which has no matching b row with b.this=1

make sense?



r937.com | rudy.ca

 

[imstillatwork]

one of the problems of being self tought is that you often miss simple things that make a huge difference....

I NEVER KNEW you could use and / os in the ON clause...

That just made my day..I need to work less and read more

thanks! your always a help!

 

[imstillatwork]

that is.. and / or...