March 21, 2012 at 8:19 am
KWymore (3/21/2012)
I had only looked at the ANY and SOME syntax once before and did not see a need for them. They seem to perform exactly like the IN statement unless I am missing something. Are there any instances where you could not perform the same logic using IN instead of ANY?
You can use inequality comparisons with Any/Some. For example:
SELECT *
FROM ( VALUES ( 1), ( 2), ( 3) ) AS MyQuery (MyCol)
WHERE MyCol > ANY ( SELECT MyCol2
FROM ( VALUES ( 2), ( 3) ) AS MyQuery2 (MyCol2) ) ;
Note the use of ">ANY". IN will only do equality comparisons.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2012 at 8:26 am
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?
Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.
March 21, 2012 at 8:27 am
Nice question.
-----------------
Gobikannan
March 21, 2012 at 8:32 am
Hugo Kornelis (3/21/2012)
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.
There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.
I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2012 at 8:49 am
GSquared (3/21/2012)
Hugo Kornelis (3/21/2012)
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.
There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.
I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.
I would end up using Exists/Not Exists for these scenarios. However, I do see your point on readability as I have written some fairly complex, ugly Exists statements before. I will have to keep ANY/ALL in mind then for these scenarios.
March 21, 2012 at 8:59 am
GSquared (3/21/2012)
Hugo Kornelis (3/21/2012)
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.
There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.
I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.
I wonder, do they produce the same execution plan?
March 21, 2012 at 9:45 am
thanks for the question Steve.
March 21, 2012 at 10:30 am
Never used ANY or SOME like this and have learned something new.:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 21, 2012 at 11:08 am
Got it wrong this time because I thought the date string would not be explicitly converted, I think I will run the following statement in my mind:
USE SQLSERVER2008R2
SET ALWAYS_EXPLICIT_CONVERSION ON
GO
Though I have never used SOME, ANY or ALL, this is an interesting question that brings new knowledge.
Thank you Steve for the question.
"El" Jerry.
March 21, 2012 at 5:37 pm
Nice question.
I don't think I've ever used ANY - certainly not with = or != or <> and think not with any of the other comparisons, although I can see that it would sometimes make more readable code than using exists. Certainly haven't used SOME (too lazy to type 4 characters where 3 will do).
Tom
March 21, 2012 at 11:41 pm
Nice & Easy question 🙂
Thanks
March 22, 2012 at 2:17 am
Brilliant question! A bit tricky initially ... had to read between the lines of "ANY" returning a true for all rows in the table.
Kwex.
March 23, 2012 at 12:40 pm
Nice question and answer explanation. Thank you for covering the ANY operator since I learnt more.
April 2, 2012 at 7:11 pm
Have to read carefully to make sure you understand the question. Got my point but had to think about it. Thanks for submitting.
http://brittcluff.blogspot.com/
August 16, 2012 at 7:14 am
Nice question! Learned something new.:-)
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply