msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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.


Filed in Oracle on 09 Aug 05 | Tags: , , ,


Reader's Comments

  1. |

    Interesting.

    Since the query optimizer changes these to more traditional queries, I wonder why these keywords exist.

    I imagine it was to some community of people to write their queries in a way that was more comfortable or understandable for them.

    If so, I wonder in which database or language these key words have their origin.

    What do you think?

  2. |

    Robert,

    Maybe you’re right. Maybe Oracle introduced these comparison operators just because other databases have it, I do not know. I do know however that SQL Server has similar ANY/SOME and ALL operators. I still want to prove that using ANY, for example, is slower than using >, < or = instead. I’ll put it on my to-do list.

  3. |

    Nice post, however I believe these operators make less readable a given query.

  4. |

    Very very descriptive and useful… Hats Off…

  5. |

    SELECT * FROM emp WHERE sal >=800 AND sal >=null; This query can return rows. Why?

    SELECT * FROM emp WHERE sal >=null; This query can not return rows. Why?

  6. |

    What is the meaning of the following query?

    SELECT * FROM emp WHERE NOT manager_id <> ALL(800,1600,null);

  7. |

    anguang, sal >=null is meaningless. It’s either sal is null or sal is not null.

  8. |

    Really it is very nice post , It clear my confusion between any and some.

  9. |

    please tell me the querey of “

    i want to details of that manager who have reported maximum no. of employees………from emp table.

  10. |

    Great Post. After 5 years I have understand real meaning and usefullness of any and all..

    Hats Off

    Thanks

  11. |

    Anguang, SELECT * FROM emp WHERE sal >=800 AND sal >=null; This query can return rows. Why?

    Optimization, as you can see with explain plan. My guess: 800 is sorted higher as Null, so we’ll only test sal >=800.

    Interesting question though.

    Jan