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

Answer 1

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

Answer 2

Just a suggestion - never use SOME/ANY, they are horrible when it comes to performance.

---

Mangal Pardeshi
SQL With Mangal

Answer 3

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

Answer 4

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)

Answer 5

Thanks Brad, that's the sort explanation I was after.

Rob

Answer 6

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

Answer 7

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)

Answer 8

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

Answer 9

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)