SOME and ANY: Can someone give me a real-world use?

Hi All

I recently sat for the 70-433 SQL 2008 Development exam, and one of the exam questions dealt with SOME and ANY as used in a correlated subquery.  

Question:  Has anyone come across a real-world use for SOME and ANY?  I haven't.

The question went something like this:
-- Given these tables...
IF EXISTS (SELECT name FROM sys.tables WHERE name='ta')
	DROP TABLE ta

IF EXISTS (SELECT name FROM sys.tables WHERE name='tb')
	DROP TABLE tb

CREATE TABLE ta (ID int) ;
GO

INSERT INTO ta (id) VALUES (1) ;
INSERT INTO ta (id) VALUES (2) ;
INSERT INTO ta (id) VALUES (3) ;
INSERT INTO ta (id) VALUES (4) ;

CREATE TABLE tb (id int, value varchar(10)) ;
GO

INSERT INTO tb (id, value) VALUES (1, 'banana') ;
INSERT INTO tb (id, value) VALUES (2, 'grape') ;
INSERT INTO tb (id, value) VALUES (3, 'banana') ;
INSERT INTO tb (id, value) VALUES (4, 'watermelon') ;

SELECT * FROM ta
SELECT * FROM tb

-- What's the SQL to return id values in tb that are not in ta?

SELECT ta.*
FROM ta
WHERE 3 < SOME (SELECT id FROM tb WHERE ta.id = tb.id); 

SELECT ta.*
FROM ta
WHERE 3 < ALL (SELECT id FROM tb WHERE ta.id = tb.id) 

Open in new window


Answers with blind links will be shamelessly mocked.

Thanks in advance.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
lol Love the last line of the question :)

I commented because I want to follow this question and see an example because its been so long.
my professor in college did a chapter on this (11 years ago)  he used an example with baseball teams statistics.. the ANY (subquery) about the players in all of the national league being left or right handed

Please don't mock me for trying to remember the college days :)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SOME and ANY are the same and they do the same as EXISTS clause. Speaking for myself I'm so used to the EXISTS keyword that I never remember to use SOME or ANY.
In your example if you use EXISTS will be like this:
SELECT ta.*
FROM ta
WHERE EXISTS (SELECT id FROM tb WHERE ta.id = tb.id AND ta.id > 3) 

Open in new window

So, if you ever used EXISTS then you have the real world examples for SOME and ANY.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
I can't think of a real-world case where I'd need or want to use SOME or ANY.

I have used ALL once or twice, though.

Btw, why are test makers so intent on pulling out obscure nothings instead of testing for real-world knowledge?  I've been doing relational for more than 20 years and I've never spent any time working on a SOME or ANY query; as Vitor noted, there are other more understandable ways of doing the same thing.  If you did use ANY or SOME, the poor guy after you has to hit BOL before he can even follow the code!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
That's a good point Scott.
And EXISTS it's ANSI on opposite from SOME/ANY and ALL.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I am noticing some differences...
IF EXISTS (SELECT name FROM sys.tables WHERE name='ta')
	DROP TABLE ta

IF EXISTS (SELECT name FROM sys.tables WHERE name='tb')
	DROP TABLE tb

CREATE TABLE ta (ID int) ;
GO

INSERT INTO ta (id) VALUES (1) ;
INSERT INTO ta (id) VALUES (2) ;
INSERT INTO ta (id) VALUES (3) ;
INSERT INTO ta (id) VALUES (4) ;
INSERT INTO ta (id) VALUES (5) ;
INSERT INTO ta (id) VALUES (6) ;
INSERT INTO ta (id) VALUES (7) ;
INSERT INTO ta (id) VALUES (8) ;

CREATE TABLE tb (id int, value varchar(10)) ;
GO

INSERT INTO tb (id, value) VALUES (1, 'banana') ;
INSERT INTO tb (id, value) VALUES (4, 'banana') ;
INSERT INTO tb (id, value) VALUES (4, 'watermelon') ;
INSERT INTO tb (id, value) VALUES (7, 'kiwi') ;
INSERT INTO tb (id, value) VALUES (8, 'strawberry') ;

--SELECT * FROM ta
--SELECT * FROM tb

-- SOME returns only matching rows
SELECT ta.*
FROM ta
WHERE 3 < SOME (SELECT id FROM tb WHERE ta.id = tb.id); 

-- ANY returns matching rows plus rows in ta not in tb
SELECT ta.*
FROM ta
WHERE 3 < ALL (SELECT id FROM tb WHERE ta.id = tb.id) 

-- SOME, returns TRUE<,there are some ta rows less than 3
IF 3 < SOME (SELECT ID FROM ta)
	PRINT 'TRUE' 
	ELSE
	PRINT 'FALSE' ;

-- ANY, returns FALSE, not all ta rows are less than 3, as one has a 1. 
IF 3 < ALL (SELECT ID FROM ta)
	PRINT 'TRUE' 
	ELSE
	PRINT 'FALSE' ;

Open in new window


@Scott - I've noticed a trend where the exams don't reflect real-world knowledge, but knowledge of the material.

For example...
Q:  How do you insert data into a two-column table with the minimum amount of code possible?
Correct A:  INSERT target_table SELECT * FROM source_table

It's not considered best practice to do it this way, as the statement would throw an error if the schemas between source_table and target_table were different, but that's the answer they are expecting.
0
Scott PletcherSenior DBACommented:
Well, in that one, they did say "the minimum amount of code", not "the minimum amount of good code" :-) .

But:
 INSERT target_table WITH DEFAULT VALUES
saves one word, as long as the table has proper defaults set ;-).


Btw, be sure to note the effect of NULL values appear in the subquery list.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Closing out the thread.  Thanks guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.