Very interesting! So you don't use a WHERE clause at all? Or, if you do, how do you decide what goes in the WHERE and what goes in the JOIN?
zahid
>From what I know... The where clause happen after all the records has been filtered once with the JOIN. I'de use the AND because I'm pretty sure it is faster. Also, I had a problem once which make me want to use the AND from now on -).
>
>>Uh oh ... I get the feeling that I'm about to learn something new :)
>>
>>Arguably correct? Under what circumstances would it be beneficial to include filter conditions which filter the leftmost table of a LEFT JOIN inside the JOIN ... ON clause? In other words, why would
SELECT *
>>FROM Table1
>>LEFT JOIN Table2 ON Table1.key = Table2.key
>>AND Table1.otherfield = 'ABC'...
ever be preferable to
SELECT *
>>FROM Table1
>>LEFT JOIN Table2 ON Table1.key = Table2.key
>>WHERE Table1.otherfield = 'ABC'...
(other than to confuse the maintenance programmers? <g>)
>>
>>Thanks for the info!
>>zahid
>>
>>>>I think my post was correct. The KB article you reference indicates that
>>>>filter conditions affecting the right side of a LEFT JOIN clause should go in
>>>>the JOIN condition. Which is true. But the SQL that Randall posted included
>>>>filter conditions affecting the left side. Those have no business being in the
>>>>JOIN clause.
>>>
>>>Arguably correct.