Understanding SOME ANY ALL Operators with examples

Posted on : 21-12-2011 | By : Devi Prasad | In : SQL Server Basics, SQLServerPedia Syndication

Share:

0


These operators involve a query and a Sub Query.
QUERY (CONDITION) SOME|ANY|ALL (SUB QUERY)

CONDITION Above would include the following operators
= (or) < > (or) ! = (or) > (or) > = (or)! > (or) < (or) < = (or) ! < SOME Operator: This operator compares the value in the query with the values in the sub query. It returns true if the value condition matches with any of the results in the sub query.
ANY operator is same as SOME Operator.

Example of using some (or) any operator:

DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

IF 2 > SOME (SELECT ID FROM @vTable)
PRINT 'Returned True' 
ELSE
PRINT 'Returned False'

IF 3 < ANY (SELECT ID FROM @vTable)
PRINT 'Returned True' 
ELSE
PRINT 'Returned False'

In the above query 2 greater some of 1,2 and 3.
So 2 when compared with 1, 2 is greater.
Hence the result is true.

In the next statement 3 is lesser than any of 1,2 and 3.
So when compared to each of it none of them return true,
Hence the result is false.

ALL Operator: This operator compares the value in the query with the values in the sub query. It returns true if the condition matches with all of the results in the sub query.

Example of using ALL operator:

DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

IF 4 > ALL (SELECT ID FROM @vTable)
PRINT 'Returned True' 
ELSE
PRINT 'Returned False'

IF 3 > ALL (SELECT ID FROM @vTable)
PRINT 'Returned True' 
ELSE
PRINT 'Returned False'

In the above query 4 greater than all of 1,2 and 3.
So 4 when compared with all of 1, 2 and 3 we can find 4 is greater.
Hence the result is true.

In the next statement 3 greater than all of 1,2 and 3.
So when compared to each of it 3 is greater than 1,2 but is lesser than 3.
So 3 is not greater than all of 1,2,3.
Hence the result is false.

(Visited 398 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , ,