ANY/SOME/ALL Operators in SQL SERVER

ANY/SOME/ALL Operators

Any/all/some operators are used with sub queries when the inner querie returning more than one value.

we use IN operator to compare the equality with multiple values, we can't perform the comparison operation with IN.

All operator implies the condition should satisfy all the values in the list,if any one value in the list not satisfied with condition entire condition failed

Write a query to get all the employees whose sal is less than all the salaries of dept 10.

 select * from Employee10 where sal< all
 (select sal from Employee10 where dno=10)

ANY and SOME are same,here the condition satisfy when any value in the list satisfies with the condition.

Write a querie to get all the employees whose sal is less than the max sal of dept 10

 select * from Employee10 where sal< any
 (select sal from Employee10 where dno=10)

 select * from Employee10 where sal< some
 (select sal from Employee10 where dno=10)

 select * from employee10 where sal<
 (select MAX(sal) from Employee10 where dno=10)

All the above 3 queries result the same values

Let me know if you have any doubts on this Query.

Thanks

Srinivas