Oracle SQL - Subquery ANY Operators

Introduction

SQL can use standard comparison operators (<, >, =, and so on) with subqueries returning any number of rows by using ANY or ALL between the comparison operator and the subquery.

ANY means the result is true for at least one value returned by the subquery.

The following table illustrates the definitions of ANY and ALL using iterated OR and AND constructs.

In the table, the symbol, #, represents any standard comparison operator: <, >, =, >=, <=, or <>.

V1, V2, V3, and so on represent the values returned by the subquery.

X # ANY(subquery)	X # ALL(subquery)
(X # V1) OR	(X # V1) AND
(X # V2) OR	(X # V2) AND
(X # V3) OR	(X # V3) AND

The following code illustrates an example of using the ANY operator, showing all emp with a monthly salary that is higher than at least one manager.

select e.empno, e.ename, e.job, e.msal
from   emp e
where  e.msal > ANY (select x.msal
                     from   emp x
                     where  x.job = 'MANAGER');

Demo

SQL>
SQL>--  w  ww.  j  a va 2 s . c o m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> select e.empno, e.ename, e.job, e.msal
  2  from   emp e
  3  where  e.msal > ANY (select x.msal
  4                       from   emp x
  5                       where  x.job = 'MANAGER');

    EMPNO | ENAME    | JOB      |      MSAL
--------- | -------- | -------- | ---------
 07004.00 | JACK     | MANAGER  |  02975.00
 07006.00 | BLAKE               |  02850.00
 07008.00 | SCOTT    | TRAINER  |  03000.00
 07009.00 | KING     | DIRECTOR |  05000.00
 07012.00 | JONES    | ADMIN    |  08000.00
 07902.00 | FORD     | TRAINER  |  03000.00

6 rows selected.

SQL>

• Previous
• Next