OraFAQ Forum: SQL & PL/SQL » ANY,ALL Operators

Home » SQL & PL/SQL » SQL & PL/SQL » ANY,ALL Operators

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ANY,ALL Operators [message #609838]

Thu, 13 March 2014 00:48

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

In the below query why i am getting KING Salary when i used ANY Operator.

SQL> SELECT EMPNO,ENAME,SAL FROM emp
  2  WHERE sal > ANY(2000, 3000, 4000)
  3  order by sal
  4  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7698 BLAKE            2850
      7566 JONES            2975
      7902 FORD             3000
      7788 SCOTT            3000
      7839 KING             5000

6 rows selected.

SQL> SELECT EMPNO,ENAME,SAL FROM emp
  2  WHERE sal >all(2000, 3000, 4000)
  3  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000

Also can anyone please explain me how,where and why to use ANY,ALL Operators.

Report message to a moderator

Re: ANY,ALL Operators [message #609839 is a reply to message #609838]

Thu, 13 March 2014 01:08

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ANY evaluates to OR, such as

where sal > 2000 or sal > 3000 or sal > 4000

ALL evaluates to AND, such as

where sal > 2000 and sal > 3000 and sal > 4000

As anything else, you use them when they are appropriate operators to reach the solution you need.

Report message to a moderator

Re: ANY,ALL Operators [message #609840 is a reply to message #609839]

Thu, 13 March 2014 01:20

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thank you Littlefoot.. Smile

Report message to a moderator

Previous Topic:	Oracle SQL
Next Topic:	Oracle 11g R2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:29:33 CDT 2024