Oracle SQL - Subquery ALL Operators

  1. HOME
  2. Oracle SQL
  3. Subquery
  4. ALL Operators

Introduction

ALL means the result is true for all values 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 shows an example of using the ALL operator, showing the "happy few" with a higher salary than all managers. 

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

Demo

SQL>
SQL> select e.empno, e.ename, e.job, e.msal
  2  from   emp e
  3  where  e.msal > ALL (select x.msal
  4                       from   emp x
  5                       where  x.job = 'MANAGER');

    EMPNO | ENAME    | JOB      |      MSAL
--------- | -------- | -------- | ---------
 07902.00 | FORD     | TRAINER  |  03000.00
 07008.00 | SCOTT               |  03000.00
 07009.00 | KING     | DIRECTOR |  05000.00
 07012.00 | JONES    | ADMIN    |  08000.00

SQL>-- from w  w  w.j  a va2 s .c  o  m

Related Topics