none
SOME/ANY vs EXISTS

    Question

  • Hello

    I recently came across SOME/ANY and just wondering is there any difference between EXISTS?  I have looked up Books Online but it does not seem to have much info.  Can anyone give me examples of how it could be useful.  I have included the comparision code below.

    Thanks
    SELECT [Number] 
    INTO #Test
    FROM 
    (SELECT 1 AS Number UNION ALL 
    SELECT 2 UNION ALL 
    SELECT 3) AS dv
    
    IF 2 = SOME
    (SELECT Number FROM #Test) BEGIN
      PRINT 'Some are 2s'
    END
    ELSE BEGIN
      PRINT 'No 2s'
    END
    
    IF EXISTS(SELECT * FROM #test WHERE Number=2) BEGIN
      PRINT 'Some are 2s'
    END
    ELSE BEGIN
      PRINT 'No 2s'
    END
    Monday, August 24, 2009 3:11 AM

Answers

All replies

  • Never mind, as soon as closed the window to post this message I found another article in Books Online.  SOME/ANY and EXISTS are interchangeable.

    http://msdn.microsoft.com/en-us/library/ms187074(SQL.90).aspx
    Monday, August 24, 2009 3:22 AM
  • Just a suggestion - never use SOME/ANY, they are horrible when it comes to performance.
    Mangal Pardeshi
    SQL With Mangal
    Monday, August 24, 2009 4:20 AM
  • Please mark ar answer to this post so that it would not be in top of unanswered list.
    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Monday, August 24, 2009 5:30 AM
  • I just wrote about ALL, ANY, and SOME in a blog post over the weekend...  it mentions the ANY/EXISTS connection, and explores other stuff about them in depth.

    Check it out:  http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

    One word of warning... SOME/ANY and EXISTS are interchangeable... but (in a way) only with =ANY and <>ANY.  When it comes to Value>ANY, for example, it's equivalent to EXISTS (... WHERE SubQueryValue<OuterQuery.Value)... in other words, you flip it around.

    --Brad (My Blog)
    Monday, August 24, 2009 4:44 PM
    Moderator
  • Thanks Brad, that's the sort explanation I was after.

    Rob
    Tuesday, August 25, 2009 12:31 AM
  • Look at very interesting way the ALL is applied and a much peformance is gained

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-search-for-all-words-inclusive-wi
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
  • Hi Naom...

    That's actually a clever way to use the ALL operator.  I like it.

    As I explained in my blog post, what's really happening in this WHERE clause (from your blog)...

    WHERE not exists (SELECT * FROM @WordsToExclude W WHERE ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')
        and 1 = ALL ( SELECT CASE WHEN ' ' + m.Searched + ' ' like '% ' + k.Word + ' %' THEN 1 ELSE 0 END FROM @Keys k)

    ...is that SQL will translate the ALL query into an ANY query (using the opposite comparison operator... i.e. changing = into <>) and applying a NOT to it:

    WHERE not exists (SELECT * FROM @WordsToExclude W WHERE ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')
        and NOT 1 <> ANY ( SELECT CASE WHEN ' ' + m.Searched + ' ' like '% ' + k.Word + ' %' THEN 1 ELSE 0 END FROM @Keys k)

    ...and then it translates the ANY into an EXISTS query:

    WHERE not exists (SELECT * FROM @WordsToExclude W WHERE ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')
        and NOT EXISTS ( SELECT * FROM @Keys k WHERE CASE WHEN ' ' + m.Searched + ' ' like '% ' + k.Word + ' %' THEN 1 ELSE 0 END<>1)

    ...which is the logically the same as:

    WHERE not exists (SELECT * FROM @WordsToExclude W WHERE ' ' + m.Searched + ' ' like '% ' + W.Word + ' %')
        and NOT EXISTS ( SELECT * FROM @Keys k WHERE ' ' + m.Searched + ' ' NOT like '% ' + k.Word + ' %')

    All of the above WHERE clauses are equivalent and have the same query plan in the sense that it does a LEFT ANTI SEMI JOIN between the RealTest table (Aliased as M) and the @Keys table (which is a NOT EXISTS concept).

    Anyway, one could certainly argue that the 1 = ALL query is perhaps clearer as to what is going on.  The NOT EXISTS (... WHERE x NOT LIKE y) gets a little confusing with the double NOTs.

    Thanks for sharing that.

    --Brad (My Blog)
    Tuesday, August 25, 2009 1:08 AM
    Moderator
  • Very nice written blog, Brad - I loved it, thanks.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog




    Thanks!!

    I've also enjoyed reading your entries at LessThanDot.  Looking forward to more...
    Tuesday, August 25, 2009 1:59 AM
    Moderator

  • BTW, I see you were VFP MVP - I didn't know - but I just checked your posts on UT. Nice meeting you here.

    I never hung out much at UT... too much chatter and politics.

    In the 90's I hung out at CompuServe's FoxForum (extinct now).  In this decade, it was mostly at Foxite.com... you can find hundreds of messages posted by me there:

    http://www.google.com/search?hl=en&q=%22posted+by+brad+schulz%22+site%3Afoxite.com&aq=f&oq=&aqi= 

    but I wasn't a consistent contributor... I'd contribute for a few months and then just drift away.

    But it's been fun here... I learn something new every day.


    --Brad (My Blog)
    Tuesday, August 25, 2009 1:53 PM
    Moderator