Why are the following SQL queries not equivalent?

Question

SELECT c.cname
FROM Customers c
WHERE c.age > ALL (SELECT c2.age
 FROM Customers c2
 WHERE c2.type = 'snowboard'); 

SELECT c.cname
FROM Customers c
WHERE c.age > (SELECT MAX(c2.age)
 FROM Customers c2
 WHERE c2.type = 'snowboard')

They look the same to me because MAX(c2.age) is greater or equal than all the values in the column ages and if c.age is greater than MAX(c2.age) then it's greater than all the values.

Answer 1

If there are no matches for snowboard, then the first returns all rows and the second returns none.

The logic for the first follows the colloquial definition of "all". If there are no matches, then any value is greater than the (non-existent) value from the subquery. Note: this is even true of NULL (at least in SQL Server).

The second query returns NULL when there are no matches. Basically, comparisons to NULL never return true -- well, there are a few exceptions.

Hint for the future: if two queries look like they are the same, but don't return the same values, then one of two things is usually the culprit:

• NULL values is involved
• One of the tables is empty