Oracle SQL - Rewriting SQL Statements Containing ANY and ALL

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

Introduction

In most cases, you can rewrite your SQL statements in such a way that you don't need the ANY and ALL operators.

For example, we could have used a group function to rebuild the subquery into a single-row subquery.

The following code uses the MAX Function in the Subquery, Instead of ALL. 

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

The following SQL constructs are logically equivalent:

  • X = ANY(subquery) <=> X IN (subquery)
  • X <> ALL(subquery) <=> X NOT IN (subquery)

Look at the following two rather special cases of ANY and ALL:

  • X = ALL(subquery)
  • X <> ANY(subquery)

If the subquery returns two or more different values, the first expression is always FALSE, because X can never be equal to two different values at the same time.

If the subquery returns two or more different values, the second expression is always TRUE, because any X will be different from at least one of those two values from the subquery.

Related Topic