Once in a blue moon I see these Oracle SQL operators in code somewhere: ALL, ANY, and SOME. Their use is pretty obscure. The Oracle Performance Tuning Guide and Reference (http://docs.oracle.com/cd/B10500_01/server.920/a96533/opt_ops.htm#1005317) breaks down what is going on with them behind the scenes.
How the CBO Evaluates the ANY or SOME Operator
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.
In the following example, the optimizer expands the first condition into the second:
salary > ANY (:first_sal, :second_sal)
is transformed into
salary > :first_sal OR salary > :second_sal
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.
In the following example, the optimizer transforms the first condition into the second:
x > ANY (SELECT salary
FROM employees
WHERE job_id = ‘IT_PROG’)
is transformed into
EXISTS (SELECT salary
FROM employees
WHERE job_id = ‘IT_PROG’
AND x > salary)
How the CBO Evaluates the ALL Operator
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.
In the following example, the optimizer expands the first condition into the second:
salary > ALL (:first_sal, :second_sal)
is transformed into
salary > :first_sal AND salary > :second_sal
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. In the following example, the optimizer transforms the first condition into the second:
x > ALL (SELECT salary
FROM employees
WHERE department_id = 50)
is transformed into
NOT (x <= ANY (SELECT salary
FROM employees
WHERE department_id = 50) )
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:
NOT EXISTS (SELECT salary
FROM employees
WHERE department_id = 50
AND x <= salary)