CODEWITHSUNDEEP
sql
user695652
user695652

Reputation: 4275

"WHERE X > ALL SELECT MAX" vs "WHERE X > SELECT MAX"

I came across multiple queries which use the all quantor for a max subquery:

Is there any difference between

SELECT name FROM bbc
 WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

and

SELECT name FROM bbc
 WHERE population > 
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

?

Upvotes: 5

Views: 502

Answers (1)

StilesCrisis
StilesCrisis

Reputation: 16300

SELECT MAX is an aggregate operation and, therefore, your subquery will select a single row.

Applying ALL to a single row will have no effect.

If your subquery returned multiple rows, the non-ALL version would result in an error. Also note that when using ALL, you could remove the MAX from the subquery and you'd get correct results (presumably with the same performance characteristics).

Upvotes: 4

Related Questions