Unexpected result of SQL query with ALL in PostgreSQL-CodePudding

I have a very simple table countries(name, gdp, continent) with data I collected from Wikipedia (actually, doesn't really matter). If I want to know how many countries each continent has with

SELECT continent, COUNT(*) AS cnt
FROM countries
GROUP BY continent;

I get the following result:

   continent   | cnt 
--------------- -----
 Africa        |  56
 Asia          |  46
 South America |  12
 North America |  22
 Europe        |  46
 Oceania       |  14

So I have for each content the corresponding countries. In any case, there are only 196 rows so the data is very small.

No I want to use a query to get for each continent the country with the largest GDP. The query is also very simple and looks like this:

SELECT name, continent, gdp
FROM countries c1
WHERE gdp >= ALL (SELECT gdp 
                  FROM countries c2 
                  WHERE c2.continent = c1.continent);

However, the result I get is:

            name            |   continent   |      gdp       
---------------------------- --------------- ----------------
 Australia                  | Oceania       |  1748000000000
 Brazil                     | South America |  1810000000000
 People's Republic of China | Asia          | 19910000000000
 United States              | North America | 25350000000000

In short, the corresponding countries for Europe and Africa are missing. By looking at the data, the countries with the highest GDP is Germany, but I don't understand why it's not part of the result set.

As a test, if I run the query

SELECT continent, MAX(GDP) AS max_gdp
FROM countries
GROUP BY continent;

I correctly get 6 GDP values for each continent (incl. the correct value for Germany):

   continent   |    max_gdp     
--------------- ----------------
 Africa        |   498060000000
 Asia          | 19910000000000
 South America |  1810000000000
 North America | 25350000000000
 Europe        |  4319000000000
 Oceania       |  1748000000000

Why is the ALL query missing the 2 rows for the European and African countries?

CodePudding user response：

The problem turned out to be NULL values, as a_horse_with_no_name suspected.

Note that there is a more performant way to write this query in PostgreSQL:

SELECT DISTINCT ON (continent)
       name, continent, gdp
FROM countries
ORDER BY continent, gdp DESC NULLS LAST;

For each continent, that will output the first row in ORDER BY order.