CODEWITHSUNDEEP
sqloracle-database
Blaisem
Blaisem

Reputation: 637

What are the unique use cases for the any and all operators?

I am currently studying for the Oracle 1Z0-071 certified associate exam.

I am learning about multiple row subqueries that use ANY or ALL

SELECT first_name, last_name, salary
FROM employees
WHERE salary >= any (select salary from employees where department_id='90')

SELECT first_name, last_name, salary
FROM employees
WHERE salary >= all (select salary from employees where department_id='90')

These are tables from the built-in HR-schema with Oracle 19c.

It seems to me I could replace

'any' with (select min(salary) from employees where department_id='90')

'all' with (select max(salary) from employees where department_id='90')

for the example queries above.

Inputting this in sqlplus does indeed return the same output. From my limited perspective, this makes any and all seem redundant and something I could potentially afford to forget after cramming for the exam.

Is there any use case for 'any' or 'all' that can't be expressed just as easily with a MIN or MAX function?

Upvotes: 0

Views: 42

Answers (1)

user5683823
user5683823

Reputation:

You can't replace the conditions with "min" and "max" conditions in the straightforward way you did - not if null is possible.

The comparison > all (... ) will fail automatically if null is present in the column in the subquery. On the other hand, max ignores null.

Compare:

select 1 as result from dual where 2000 > all (select comm from scott.emp);

no rows selected

select 1 as result from dual where 2000 > (select max(comm) from scott.emp);

    RESULT
----------
         1

Of course, you can use "max" in a rewrite of the "all" query, but it will be more complicated. It's not that you can't avoid "all" (you can!), it's just not as trivial as you think.

Upvotes: 1

Related Questions