Find the countries with a GDP greater than a group of countries

Question

I'm attempting question 5 of SQLZoo's SELECT within SELECT tutorial

Which countries have a GDP greater than any country in Europe? [Give the name only.]

This is my solution, which is incorrect but I don't understand why.

SELECT name  
  FROM world  
 WHERE gdp > ALL (SELECT gdp FROM world WHERE continent ='Europe')

What am I doing incorrectly?

Answer 1

select name, gdp 
from world x 
where gdp > all (select gdp from world y where continent = "Europe" and gdp > 0)

you were missing a NULL value check because in case of null value unknown result will be fetched, see ref: http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

world table might have null values for some European countries when you tested your query therefore it is good to put a check to avoid null values.

Answer 2

SELECT name 
  FROM world 
 WHERE gdp > (SELECT MAX(gdp) FROM world WHERE continent = 'Europe')

Using the MAX aggregate function returns the highest GDP from the sub-query which is what you want to compare with the outer query.