Post reply

WHERE ... IN versus WHERE ... =

  • June 26, 2009 at 7:30 am

    #211384

    What is better, WHERE ... IN (var_1, var_2, var_3, ... var_n)

    or WHERE ... = var_1 OR ... = var_2 OR ... = var_3 OR ... = var_n?

    Is there a difference in the search through the table, or do both use the same technique?

  • June 26, 2009 at 7:39 am

    #1016019

    Identical. In fact, if you look at the exec plan, you'll see that the plans are the same and that SQL has converted the IN into a series of = OR predicates.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 26, 2009 at 7:44 am

    #1016025

    Yes, I was just looking at the execution plan and noticed that these where exactly the same. Just comes down to what you find easier to write/document as what you use then. I wasn't sure if the SQL engine did something clever if an IN was used like where a value was found the following values where ignored.

    Interesting...

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

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