I am new to SQL and was trying to solve a question on SQLzoo
Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values).
My answer to the question was
x
SELECT name FROM bbc
WHERE gdp > ALL (SELECT gdp
FROM bbc
WHERE region = 'Africa'
AND gdp<>NULL)
But the correct answer on the site is
SELECT name FROM bbc
WHERE gdp > (SELECT MAX(gdp)
FROM bbc
WHERE region = 'Africa')
I am not getting why the answer selected by me is wrong
Advertisement
Answer
The problem with the > all (...)
solution selected by you is the fact that some of the gdp values is null.
When you compare a non-null value with a null, then the result is null, unless you use a null-safe operator, such as is null
.