Solved

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

Posted on 2014-10-07
7
89 Views
Last Modified: 2014-10-17
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
0
Comment
Question by:Jim Horn
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 25 total points
ID: 40365920
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 400 total points
ID: 40365925
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 75 total points
ID: 40366448
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40366457
That's a good point Scott.
And EXISTS it's ANSI on opposite from SOME/ANY and ALL.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 0 total points
ID: 40367142
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 75 total points
ID: 40367150
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
 
LVL 65

Author Closing Comment

by:Jim Horn
ID: 40387294
Closing out the thread.  Thanks guys.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now