January 29, 2015 at 8:36 am
Interesting question, learned "SOME" thanks Steve...
January 29, 2015 at 8:42 am
January 29, 2015 at 10:26 am
I've never used SOME either. I would be interested in seeing a real world example where SOME is the best choice to use in a query.
January 29, 2015 at 10:44 am
Brian Hibbert (1/29/2015)
I think I'm not quite understanding what SOME (or Any) actually does.A quick read of the BOL says the return type is Boolean, but it's not quite as simple as that. SOME isn't a standalone function as I initially thought, it's more like a modifier to the comparison operators.
I too was fooled by the fact that the return type was Boolean. I said zero rows returned. Learned something - don't use SOME!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 29, 2015 at 5:03 pm
+2. Liike the previous post said, I aven't seen the some function outside of certification practice exams.
January 29, 2015 at 11:08 pm
nice question. never used this one but yet again learned a new thing. Thanks for sharing
January 30, 2015 at 12:59 am
david.gugg (1/29/2015)
It's looking like most here have never used this, me included. Does anyone have any real world examples?
If you take a look a little bit above in this thread, you'll see my reply of the use of ANY (which is a synonym for SOME) in a query generated by BO.
My guess BO uses it to deal with multi-value parameters. Not that it is a great solution...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 30, 2015 at 6:10 am
Thanks Steve - I've learned a new FUNCTION. Gill
January 30, 2015 at 9:51 am
So I tried to look at some of the references for "SOME"
and I can't see any difference between the "SOME" command and a simple "LIKE" command?
Or I should say that in all the examples, I could write the query to get the same results with 'LIKE' or 'EXISTS' etc.
so honestly, what's 'SOME' for? where would you have to use it that you couldn't use something else?
Peter A.R. Johnson
Data Therapist
Federal Way Public Schools
January 30, 2015 at 10:02 am
doug.davidson (1/29/2015)
Brian Hibbert (1/29/2015)
Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.So this is the real answer. Much better than the provided link in explaining why 8 was the correct answer.
Hey wait a second! Why DOES this work??? Because you're right. the documents state that SOME returns a true/false.
Correct??
So in SQL a constant compared to true will return true?
Or to put it another way, any WHERE statement like:
WHERE 'whatever' = a SOME statement that always returns a true result
will always return all rows?
Peter A.R. Johnson
January 30, 2015 at 10:19 am
pjohnson 63193 (1/30/2015)
doug.davidson (1/29/2015)
Brian Hibbert (1/29/2015)
Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.So this is the real answer. Much better than the provided link in explaining why 8 was the correct answer.
Hey wait a second! Why DOES this work??? Because you're right. the documents state that SOME returns a true/false.
Correct??
So in SQL a constant compared to true will return true?
Or to put it another way, any WHERE statement like:
WHERE 'whatever' = a SOME statement that always returns a true result
will always return all rows?
Peter A.R. Johnson
The documentation is a little confusing. It doesn't actually return true or false directly like a function call would, to then be compared to the value on the other side of the comparison operator. it instead will modify the comparison operator so that IT returns true when it matches SOME or ANY rows in the subquery. SOME and ANY are operators rather than standalone functions even though they look like functions.
January 30, 2015 at 10:30 am
Brian Hibbert (1/30/2015)
SOME and ANY are operators rather than standalone functions even though they look like functions.
I believe that this is consistent in style with operators such as IN.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
January 30, 2015 at 10:55 am
Brian Hibbert (1/30/2015)
pjohnson 63193 (1/30/2015)
doug.davidson (1/29/2015)
Brian Hibbert (1/29/2015)
Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.So this is the real answer. Much better than the provided link in explaining why 8 was the correct answer.
Hey wait a second! Why DOES this work??? Because you're right. the documents state that SOME returns a true/false.
Correct??
So in SQL a constant compared to true will return true?
Or to put it another way, any WHERE statement like:
WHERE 'whatever' = a SOME statement that always returns a true result
will always return all rows?
Peter A.R. Johnson
The documentation is a little confusing. It doesn't actually return true or false directly like a function call would, to then be compared to the value on the other side of the comparison operator. it instead will modify the comparison operator so that IT returns true when it matches SOME or ANY rows in the subquery. SOME and ANY are operators rather than standalone functions even though they look like functions.
Peter: Hmm.... function vs. operator.... pretty SQL 101 I suppose, but I'll have to think about it....
still don't see how a simple LIKE or EXISTS statement doesn't do the same thing.....
February 1, 2015 at 3:58 am
To all the people who responded that they are happy to have learned the SOME operator ... please unlearn it, as soon as possilble.
Every expression with SOME (and its companions ANY and ALL) can be replaced with an EXISTS operator. There is no reason to ever use these operators. I have never seen them in actual code, and I will never use them.
February 2, 2015 at 6:54 am
I didn't know this keyword existed and had to look it up. Thanks.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply