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!