clauses in a join

sde · 05-30-2007, 05:43 PM

Code:

select a.field1 b.field2 from apples a
  left join bannanas b on b.field1=a.field1 and b.field3 > 0 and b.field4 = 2
  where a.field2='foo'

can anyone explain what the code after the left join means? .. i understand b.field1=a.field1, .. but i came across some code today which has more conditions after the left join and before the where clause.

DJMaze · 05-31-2007, 12:38 AM

left join bannanas b on (b.field1=a.field1 and b.field3 > 0 and b.field4 = 2)

It's just like multiple conditions inside where. Instead, it will only join bannanas if "all" conditions are met.

sde · 05-31-2007, 02:08 PM

thanks DJM. that's slick. i've always thought it was just to hook 2 tables together on an id.

DJMaze · 06-01-2007, 12:50 AM

"inner join" only show results from both tables if all conditions are met.
So if apples or bannanas has failed a condition there is no result for the record at all.
It's like: SELECT * FROM apples, bannanas

"right join" is the other way round of left join. First find the bannanas and then match an apple