Post reply

Do not use IN

  • October 21, 2008 at 2:29 pm

    #199822

    Ok - I've read NOT to use the IN statement.

    So how do a I do a conditional inner join?

    Here's what I'm doing at the moment in my WHERE clause:

    AND (@ProvTaxIdNbr IS NULL

    OR PHPM.PayToProviderID IN

    (SELECT ProvIdNbr

    FROM #TempProvTaxIdNbr )) -- Optional parameter

    I'm slowing learning. Thanks

  • October 21, 2008 at 2:43 pm

    #887843

    AND (@ProvTaxIdNbr IS NULL

    OR PHPM.PayToProviderID IN

    (SELECT ProvIdNbr

    FROM #TempProvTaxIdNbr [Wink] )

    Add ...

    Left Outer Join (SELECT ProvIdNbr

    FROM #TempProvTaxIdNbr

    GROUP BY ProvIdNbr) t

    ON t.ProvIdNbr = PHPM.PayToProviderID

    ... to your List of Joins and change the Where-clause to ...

    AND (@ProvTaxIdNbr IS NULL

    OR t.ProvIdNbr IS NOT NULL)

    devloping robust and performant databaseapplications with Microsoft SQL-Server

  • October 21, 2008 at 2:50 pm

    #887844

    Thanks! The only way to learn is to ask.

  • October 21, 2008 at 3:21 pm

    #887852

    There's no hard and fast rule about never using IN, so be careful when you see such absolutes being advanced. In 2005 especially - take a look at the query actually being executed. You may find that the IN code is evaluating exactly the same as what you're looking at.

    In other words, test the options, and figure out which one works best for you at that time.

    Also - for what it's worth: using a JOIN in this case can change your rowset, since you might have a one to many join going on (in which case your "tidy" no-duplicate rowset might end up with dupes after you use the join).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 22, 2008 at 8:18 am

    #888137

    Your million dollar answer - it depends!!!

    If there's no severe performance problem, using NOT IN is okay

    especially for code clarity

    NOT EXISTS or OUTER JOIN usually can replace NOT IN with much better performance

    doesn't necessarily mean you never can use NOT IN

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • October 22, 2008 at 11:49 am

    #888254

    Thanks to all for the info and clearification.

Viewing 6 posts - 1 through 5 (of 5 total)

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