ALL and ANY

[dgvijayanand]

Hi All

Currently i am using oracle 9i

I want to know the difference between ALL and ANY with example.

Thanks in advance

vijay

[amitpatel66]

Hi Dgvijayanand ,

Welcome to TSDN

Please make sure you follow POSTING GUIDELINES every time you post in this forum

Thanks
MODERATOR

[amitpatel66]

Hi All

Currently i am using oracle 9i

I want to know the difference between ALL and ANY with example.

Thanks in advance

vijay

Check this out:

 
SQL> select empno from emp where empno IN (1,2);
 
    EMPNO
---------
        1
        2
 
-- Two records returned
 
SQL> ed
Wrote file afiedt.buf
 
  1* select empno from emp where empno= ALL(1,2)
SQL> /
 
no rows selected
 
-- because EMPNO can be either equal to 1 or 2 but not both so the query with ALL does not return any record
 
SQL> ed
Wrote file afiedt.buf
 
  1* select empno from emp where empno= ANY(1,2)
SQL> /
 
    EMPNO
---------
        1
        2
 
/* Query with ANY also returns 2 records because the value for EMPNO can be any of the values specified within bracket. ANY is almost similar to IN but with slight change in the syntax of the query. We use "=" sign with ANY but not with IN as you can see above */
 
 

[debasisdas]

Please find a related discussion here.