IN and EXISTS

• vxhughes

  SSCommitted

  Points: 1627

  More actions

  June 20, 2013 at 4:06 pm

  #294957

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 20, 2013 at 4:56 pm

  #1626158

  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, 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

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  June 20, 2013 at 5:44 pm

  #1626173

  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
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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