ALL operator VS Any on an empty query

Question

I'm reading the oracle documentation on the ANY and ALL operators. I pretty understand their uses except for one thing. It states:

ALL :

If a subquery returns zero rows, the condition evaluates to TRUE.

ANY :

If a subquery returns zero rows, the condition evaluates to FALSE.

It doesn't seem very logical to me. Why would ALL on an empty subquery would return TRUE but ANY returns FALSE?

I'm relatively new to SQL, so I assume it would have a use-case for this behavior which is really counter-intuitive to me.

ANY and ALL on an empty set should return the same value no?

Answer 1

Consider the example of the EMP table in that link.

Specifically this query -

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ANY (SELECT e2.sal
                     FROM   emp e2
                     WHERE  e2.deptno = 20);

In case of ANY, the question you are asking is "Is my salary greater than anyone in department 20 (at least 1 person)". This means you are hoping at least one person has a salary less than you. When there are no rows, this returns FALSE because there is nobody whose salary is less than you, you were hoping for at least one.

In case of ALL, the obvious question you would be asking is "Is my salary greater than everyone?". Rephrasing that as "Is there nobody that has salary greater than me?" When there are no rows returned, your answer is TRUE, because "there is indeed nobody whose salary is greater than me.

Answer 2

Because ANY is to be interpreted as EXIST (if there is any, it means they exist). Therefore, it return false if no rows are found.

All does not certify that any values exist, it just certifies that it represents all possible values. Therefore, it return true even if no rows are found.