IN->Equal to Any One in the List.
ANY->Compares Value to Each Value Returned by the Sub Query.
ALL->Compares Value To Every Value Returned by the Sub Query.
For Example:
IN:
(Q):Display the Details of all the Employees Whose Salaries are Matching with Least Investments of Departments?
(A):
SQL>Select Ename,Sal,Deptno from Emp Where
Sal IN(Select Min(Sal) From Emp Group By Deptno);
ANY:
<ANY:->Meaans Less Than The Maximum Value in the List.
(Q):Get The Details of All Employees Who are Earning Less Than The Highest Earning Employee Controling Other Emp?
(A):
SQL>Select Empno,Ename,Job,Sl From Emp
Where Sal <Any(Select Distinct MGR From Emp));
>ANY:->Meaans More Than The Minimum Value in the List.
(Q):Get The Details Of All Emps Who are Earning more than the least paid of Department 10?
(A):
SQL>Select Empno,Ename,Job,Sal From Emp
Where Sal>Any(Select Min(Sal) From Emp Where Deptno=10);
=ANY:->It's Equivalent to In Operator.
Note: 'Some' is also used insted of ANY.
ALL
<ALL:->Means Less Than The Minimum Value in the List.
(Q):Get The Details Of All Emps Who are Earning Less than the
Avg Investment of Department 10?
(A):
SQL>Select Empno,Ename,Job,Sal From Emp
Where Sal<All(Select Avg(Sal) From Emp Where Deptno=10);
>ALL:->Means More Than The Maximum Value in the List.
(Q):Get The Details Of All Emps Who are Earning More than the
Avg Investment of Department 10?
(A):
SQL>Select Empno,Ename,Job,Sal From Emp
Where Sal>All(Select Avg(Sal) From Emp Where Deptno=10);
Thank you
Any Mistakes please Inform to me...
-Suresh
