GeekInterview.com
Series: Subject: Topic:
Question: 339 of 378

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

Asked by: abhimanu.singh | Member Since Mar-2006 | Asked on: Mar 27th, 2006

View all questions by abhimanu.singh

Showing Answers 1 - 4 of 4 Answers
vijay Jaiswal

Answered On : Apr 19th, 2006

ANYThe If any value  from outcome of subquery is macthed then desired output will be retrived.It checks with lowest value .ALLItwill check for greatest value from outcome of subquery  

  
Login to rate this answer.
Nikhil_4_Oracle

Answered On : Mar 15th, 2007

DEAR ALL,ANY or SOME : Compares a value to each value in a list or retuned by a query. Must bepreceded by =,!=,>,<,<=,=> Evalutes FALSE when query returns no rows.select empno,ename,sal from emp where sal = any(1500,3000);EMPNO ENAME    SAL------- ---------- ----------7499 ALLEN 16007566 JONES 29757839 KING 50007902 FORD 3000so ,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

Yes  1 User has rated as useful.
  
Login to rate this answer.
rajakumar_na

Answered On : Nov 14th, 2007

View all answers by rajakumar_na

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 

  
Login to rate this answer.

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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.