What is the difference between ALL and ANY in ORACLE?can any one explain with example?

Questions by abhimanu.singh

Showing Answers 1 - 4 of 4 Answers

vijay Jaiswal

  • Apr 19th, 2006
 

ANY
The If any value  from outcome of subquery is macthed then desired output will be retrived.It checks with lowest value .

ALL
Itwill check for greatest value from outcome of subquery

 

 

  Was this answer useful?  Yes

Nikhil_4_Oracle

  • Mar 15th, 2007
 

DEAR ALL,

ANY or SOME : Compares a value to each value in a list or retuned by a query. Must be

preceded by =,!=,>,<,<=,=> Evalutes FALSE when query returns no rows.



select empno,ename,sal from emp where sal = any(1500,3000);


EMPNO ENAME    SAL

------- ---------- ----------

7499 ALLEN 1600

7566 JONES 2975

7839 KING 5000

7902 FORD 3000


so ,optimizer take above query as ,

--select empno,ename,sal from emp where sal>1500 or sal>3000;


ALL : Compares a value to every value in a list or return by a query .Must be preceded by

=,!=,>,<,=>,<=. Evalutes to TRUE if the query returns no rows.


select empno,ename,sal from emp where sal > ALL (1600,3000);


EMPNO ENAME SAL

---------- ---------- ----------

7839 KING 5000


-- in other words optimizer take this query as ,

--select empno,ename,sal from emp where sal 1600 AND sal>3000;



Regards,

Nikhil




ANY and ALL are Multiple row operators.
<ANY - Less than the Maximum
>ANY - Greater than the Minimum
<ALL - Less than the Minimum
>ALL - Greater than the maximum 

  Was this answer useful?  Yes

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




  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions