Special Operator Any Or Some In SQL Server

In this article you will learn about special operator Any or Some in SQL Server.

SOME and ANY operators are interchangeable – wherever we use the terms ANY, SOME would work just the same.

Let’s see examples,

CREATE TABLE #TEMP (EMPID INT IDENTITY (1,1),NAME VARCHAR(20),CITY VARCHAR(20))

CREATE TABLE #TEMPCITY( CITYNAME VARCHAR(20))

INSERT INTO #TEMP (NAME,CITY)
SELECT 'ROHAN','ALLHABAD'
UNION
SELECT 'SUMIT','JAIPUR'
UNION
SELECT 'RAHL','DELHI'
UNION
SELECT 'RAJU', 'NOIDA'

INSERT INTO #TEMPCITY(CITYNAME)
SELECT  'JAUNPUR'
UNION
SELECT'ALLHABAD'

UNION
 SELECT'NOIDA'

SELECT * FROM #TEMP
WHERE CITY=ANY(SELECT CITYNAME FROM #TEMPCITY)

DROP TABLE  #TEMP
DROP TABLE #TEMPCITY

 
The ANY operator takes all values produced by the subquery. In this case all city values in the #tempcity table, and evaluate to TRUE if ANY of them equal the city value of the current row of the outer query. Of course, this means that the subquery must select values whose datatype is comparable to what they are being compared to in the main predicates, just as IN and relational operator must. This is contract to EXISTS, which simply determine whether or not subquery produce result and does not actually use the result, so that the datatype of the subquery output column do not matter.

Let's use Of SOME Operator.

CREATE TABLE #TEMP (EMPID INT IDENTITY (1,1),NAME VARCHAR(20),CITY VARCHAR(20))

CREATE TABLE #TEMPCITY( CITYNAME VARCHAR(20))

INSERT INTO #TEMP (NAME,CITY)
SELECT 'ROHAN','ALLHABAD'
UNION
SELECT 'SUMIT','JAIPUR'
UNION
SELECT 'RAHL','DELHI'
UNION
SELECT 'RAJU', 'NOIDA'


INSERT INTO #TEMPCITY(CITYNAME)
SELECT  'JAUNPUR'
UNION
SELECT'ALLHABAD'

UNION
 SELECT'DELHI'

SELECT * FROM #TEMP
WHERE CITY=SOME(SELECT CITYNAME FROM #TEMPCITY)

DROP TABLE  #TEMP
DROP TABLE #TEMPCITY

Inthe  first example it returns two row and the same as example 2.