01edab90   
   From: reb01501@NOyahooSPAM.com   
      
   Fred. wrote:   
   > Transact SQL apparently supports one-sided critera,   
   > such as the "a.SEL=15" in (1) below. in the join   
   > expression. I would expect that both (1) and (2)   
   > would return the same result set, and the examples   
   > I have tried match this expectation.   
   >   
   > My question is whether or not there can be a strong   
   > reason to prefer one over the other, such as impact   
   > on the order of evaluation.   
   >   
   > CREATE TABLE a (APK int PRIMARY KEY, SEL int, ... )   
   > CREATE TABLE b (BPK int PRIMARY KEY, AFK int, ... )   
   >   
   > (1) SELECT ... FROM a INNER JOIN b   
   >     ON a.APK=b.AFK AND a.SEL=15   
   >   
   > (2) SELECT ... FROM a INNER JOIN b   
   >     ON a.APK=b.AFK   
   >     WHERE a.SEL=15   
   >   
   There is a nice article about this in BOL (Books Online - it's installed   
   when you install the client tools).   
   The short story is:   
   with inner joins there is no difference - the same query execution plan is   
   used for both variations.   
      
   With outer joins, however, different results can be obtained depending on   
   where you put the criterion, especially when the predicate applies to the   
   table on the right side of a left outer join:   
      
    (1) SELECT ... FROM b LEFT JOIN a   
        ON a.APK=b.AFK AND a.SEL=15   
      
    (2) SELECT ... FROM b LEFT JOIN a   
        ON a.APK=b.AFK   
        WHERE a.SEL=15   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)