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:
[sql]
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’
[/sql]
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:
[sql]
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’
[/sql]
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.