Unexpected result of SQL query with ALL in PostgreSQL

Question

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?

@a_horse_with_no_name Yes, indeed! I completely forgot that NULLs can affect the result here. Adding AND c2.gdp IS NOT NULL to the subquery fixed it. — Christian
– Christian, Commented May 23, 2022 at 11:41

Laurenz Albe · Accepted Answer · 2022-05-23 12:25:16Z

1

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.

answered May 23, 2022 at 12:25

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unexpected result of SQL query with ALL in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related