Tag: ANY & ALL operators in SQL Server

SQL

ANY, ALL & SOME Operators in SQL Server

ANY is a SQL operator that used to compare a scalar value with a set of single column resultset from a subquery. If any one of the value in the resultset is satifying the operator condition to the scalar value, then it return TRUE, else FALSE.

--Table Creation
create Table EmpDetails
(
	EmployeeID int,
	EmployeeName Varchar(200),
	Grade Int
)
CREATE tABLE EmpYears
(
	EmployeeID int,
	YearOfExp Int
)

--Data Population
Insert into EmpDetails Values 
(1,'Employee1',3),(2,'Employee1',4),(3,'Employee1',5),
(4,'Employee1',6),(5,'Employee1',7),(6,'Employee1',8),
(7,'Employee1',9)

Insert into EmpYears Values 
(1,3),(2,5),(3,7),(4,10),
(5,15),(6,20),(7,25)

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = ANY(Select EmployeeID From EmpYears where YearOfExp>10)

Drop Table EmpDetails, EmpYears

SOME operator is also working similar to ANY, the below returns same output as ANY.

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = SOME(Select EmployeeID From EmpYears where YearOfExp>10)

ALL operator in SQL Server returns TRUE if *all* the resultset from subquery is true to the operator condition to the scalar value.

If you enjoyed this blog post, feel free to share it with your friends!