Post reply

WHERE Clause vs. JOIN Clause

  • May 2, 2007 at 3:13 am

    #172228

    I'm re-writing some sp's and have come across the following:

    SELECT 

         O.*

    FROM

     C3Reports.dbo.tbl_Order O WITH (NOLOCK)

     FULL JOIN C3Reports.dbo.tbl_Agreement A WITH (NOLOCK) ON O.OrderNo = A.OrderNo AND O.Type < 3

    etc etc

    WHERE 

    SOMETHING... AND

     O.Type < 5 AND

    etc..

    Why wouldn't O.Type < 5 and O.Type < 3 only ever return O.Types < 3??

  • May 2, 2007 at 4:16 am

    #703759

    This query JOINS tbl_Order to tbl_Agreement ON O.OrderNo = A.OrderNo AND O.Type < 3.

    The FULL JOIN then adds the rows from tbl_Order and tbl_Agreement which were not included in the original join. (There will be NULLs in the other table's columns.)

    The WHERE clause will then remove rows WHERE O.Type < 5. This means:

    1. Orders with types of 3 and 4 will have NULLs in the tbl_Agreement columns even if an agreement exists.

    2. Rows with NULL in O.Type will be removed. This means the FULL JOIN is inefficient and a LEFT JOIN will achieve the same result.

  • May 2, 2007 at 9:13 am

    #703846

    Kevin has a great analysis. Go with that.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply