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

 

 

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

rajakumar_na

 Profile Answers by rajakumar_na 

• Nov 14th, 2007

 

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 

sure_prince

 Profile Answers by sure_prince Questions by sure_prince

• Jun 26th, 2009

 

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

saurabh

• Jan 30th, 2015

 

ANY
The If any value from outcome of sub query is matched then desired output will be retrieved. It.It checks with lowest value .
ALL
ALL
It will check for greatest value from outcome of sub-query

Samudrala

• Jan 30th, 2015

 

ALL
The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators, as shown below.
SELECT empno, sal
FROM emp
WHERE sal > ALL (2000, 3000, 4000);
EMPNO SAL
---------- ----------
7839 5000
SQL>
-- Transformed to equivalent statement without ALL.
SELECT empno, sal
FROM emp
WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
EMPNO SAL
---------- ----------
7839 5000

Samudrala

• Jan 30th, 2015

 

ANY
The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operators, as shown below.
SELECT empno, sal
FROM emp
WHERE sal > ANY (2000, 3000, 4000);
EMPNO SAL
---------- ----------
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000
SQL>
-- Transformed to equivalent statement without ANY.
SELECT empno, sal
FROM emp
WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
EMPNO SAL
---------- ----------
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000