SQL Server Performance

EXISTS or IN?

Discussion in 'T-SQL Performance Tuning for Developers' started by vasgab, Aug 28, 2006.

  1. vasgab New Member

    Hello,
    I need to know why the EXISTS clause is more efficient than IN clause. Can someone explain me?

    Thanks.
    vasgab, Aug 28, 2006
    #2

  2. FrankKalis Moderator

    Both can serve a different purpose and not always is EXISTS more efficient than IN. What exactly is your question/problem?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Aug 28, 2006
    #2

  3. dineshasanka Moderator

    dineshasanka, Aug 28, 2006
    #2

  4. Adriaan New Member

    EXISTS will probably be faster if the target column is relatively low in selectivity (many rows with identical values), and is not covered by an index.

    Otherwise an IN clause with a subquery will probably be as fast (or as slow).

    An IN clause with a value list is of course a different matter.
    Adriaan, Aug 29, 2006
    #2

  5. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan
    An IN clause with a value list is of course a different matter.
    This actually traslated into multiple ORs

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Aug 29, 2006
    #2

  6. FrankKalis Moderator

    Also IN will get tricky when negated and NULLs comes into play.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Aug 29, 2006
    #2

  7. Adriaan New Member

    Frank, the same can be said about NOT EXISTS, is it not?
    Adriaan, Aug 29, 2006
    #2

  8. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    Frank, the same can be said about NOT EXISTS, is it not?
    NO

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Aug 29, 2006
    #2

  9. FrankKalis Moderator

    FrankKalis, Aug 29, 2006
    #2

  10. Adriaan New Member

    Sorry if I was a bit vague. Wanted to say something along the lines of :

    The same as with NOT IN, with NOT EXISTS performance will be more difficult to predict than with EXISTS or IN.

    Good to know about the issue with NULLs for a NOT IN subquery! But I must say I usually select the EXISTS or NOT EXISTS syntax.
    Adriaan, Aug 29, 2006
    #2

  11. vasgab New Member

    People,

    Thank you for response. Now, I have clearly the concepts.
    vasgab, Sep 1, 2006
    #2

  12. chiragkhabaria New Member

    chiragkhabaria, Sep 2, 2006
    #2

Share This Page