ANY, SOME and ALL in Oracle

I recently stumbled upon a SQL query with ANY in the WHERE clause; it caught my attention because it reminded me of the rarely used SQL comparison operators ANY, SOME and ALL. Let’s play with these operators a little bit. But first, some definitions (from the Oracle Docs):

ANY or SOME: Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.

ALL: Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows.

Now some examples:

Select all employees:

scott@eddev> select ename, job, sal
  2  from emp;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

14 rows selected.

Select all employees with a salary greater than 1600 or greater than 2999:

scott@eddev> select ename, sal
  2  from emp
  3  where sal > any (1600, 2999);

ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000

6 rows selected.

The optimizer expands a condition that uses the ANY or SOME comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and OR logical operators. So, the following query returns the same result as the previous query with the ANY operator.

scott@eddev> select ename, sal
  2  from emp
  3  where sal > 1600 or sal > 2999;

ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000

6 rows selected.

ANY and SOME are interchangeable. You can use either one and get the same result. Here is an example:

Select employees whose name starts with either A, W or J:

scott@eddev> select ename from emp
  2  where substr(ename,1,1) = any ('A', 'W', 'J');

ENAME
----------
ALLEN
WARD
JONES
ADAMS
JAMES

scott@eddev> c /any/some
  2* where substr(ename,1,1) = some ('A', 'W', 'J')
scott@eddev> l
  1  select ename from emp
  2* where substr(ename,1,1) = some ('A', 'W', 'J')
scott@eddev> /

ENAME
----------
ALLEN
WARD
JONES
ADAMS
JAMES

You can use a subquery instead of a a parenthesized list of values after ANY or SOME:

Select employees whose salary is greater than any salesman’s salary:

scott@eddev> select ename
  2    from emp
  3    where sal > any (
  4      select sal
  5      from emp
  6      where job = 'SALESMAN');

ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
FORD
MILLER

9 rows selected.

The optimizer transforms a condition that uses the ANY or SOME operator followed by a subquery into a condition containing the EXISTS operator and a correlated subquery. So, The above query is equivalent to the following (added table aliases for clarification):

scott@eddev> select emp1.ename
  2    from emp emp1
  3    where exists (
  4      select emp2.sal
  5      from emp emp2
  6      where emp2.job = 'SALESMAN'
  7        and emp1.sal > emp2.sal);

ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
FORD
MILLER

9 rows selected.

Now on to the ALL:

Select all employees with a salary greater than 1600 and greater than 2999 (This is not a logical query but it does show the usage of ALL):

scott@eddev> select ename, sal
  2    from emp
  3    where sal > all (1600, 2999);

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000

The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and AND logical operators. So, the following query is equivalent to the previous one:

scott@eddev> select ename, sal
  2    from emp
  3    where sal > 1600 and sal > 2999;

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000

You can use a subquery instead of a a parenthesized list of values after ALL:

Select employees whose salary is greater than every salesman’s salary:

scott@eddev>  select ename
  2        from emp
  3        where sal > all (
  4          select sal
  5          from emp
  6          where job = 'SALESMAN');

ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

The optimizer transforms a condition that uses the ALL comparison operator followed by a subquery into an equivalent condition that uses the ANY comparison operator and a complementary comparison operator. So, the optimizer transforms the first condition (using ALL) into this one (using ANY):

scott@eddev>  select emp1.ename
  2        from emp emp1
  3        where not (
  4          emp1.sal <= any (
  5          select emp2.sal
  6          from emp emp2
  7          where emp2.job = 'SALESMAN'));

ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

The optimizer then further transforms the second query into the following query using the rule for transforming conditions with the ANY comparison operator, followed by a correlated subquery:

scott@eddev>  select emp1.ename
  2        from emp emp1
  3        where
  4          not exists (
  5          select emp2.sal
  6          from emp emp2
  7          where emp2.job = 'SALESMAN'
  8            and emp1.sal <= emp2.sal);

ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

The comparison operators ANY, SOME and ALL can be used when writing queries that answer specific questions; Whether they are the best option to use as far as performance is concerned has to be analysed on a case by case basis.

Related documentation can be found here and here.

Related articles:

• SQL problem
• Different ways to count
• User Defined Operators

Filed in Oracle on 09 Aug 05 | Tags: all, any, some, sql

---