Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IN and EXISTS Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 4:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, Visits: 3
I’m trying to really understand the difference between IN and EXISTS. Would the following be fair statements?

IN
• first evaluates: inner query
• evaluates outer query until: every row in outer query's table examined
• accepts as argument: a list of literal values or a condition (most often used when argument is a list of literal values)
• is faster when inner query's table contains: few records / values
• is usually: slower

EXISTS
• first evaluates: outer query
• evaluates outer query until: inner query finds a record that satifies condition
(if inner query doesn't find any records that satify condition: until every row in outer query's table examined)
• accepts as argument: a condition
• is faster when inner query's table contains: many records
• is usually: faster
Post #1465979
Posted Thursday, June 20, 2013 4:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
No, not at all.

They're two ways of stating the same thing, they perform and execute identically.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1465990
Posted Thursday, June 20, 2013 5:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,816, Visits: 5,915
GilaMonster (6/20/2013)
No, not at all.

They're two ways of stating the same thing, they perform and execute identically.


Isn't it true to say that they can be functionally different, in that you can perform a lookup on multiple columns with EXISTS, whereas IN only allows for testing one column? (although some people may concatenate columns to work around this limit)

Not disagreeing, just expanding on your answer for those pedants among me.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1466009
    Posted Thursday, June 20, 2013 5:47 PM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 9:22 AM
    Points: 40,632, Visits: 37,094
    mister.magoo (6/20/2013)
    Not disagreeing, just expanding on your answer for those pedants among me.


    *sigh*



    Gail Shaw
    Microsoft Certified Master: SQL Server 2008, MVP
    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

    Post #1466011
    Posted Friday, June 21, 2013 5:33 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Thursday, December 18, 2014 12:03 AM
    Points: 1,380, Visits: 2,708
    http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
    Post #1466138
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse