Can some redundancy in this nested SELECT COUNT query be eliminated? For the record, this is not a homework question.  I am a professional brushing up on my SQL skills.

Given a table called world that looks like this,



I want to find the continents where all countries have a population &lt;= 25000000. Specifically, I want the name, continent, and population of the countries associated with these continents.

I wrote this query, and it appears to be functionally correct:

SELECT name, continent, population
FROM world x
WHERE (
  SELECT COUNT(name)
  FROM world y
  WHERE y.population &lt;= 25000000
  AND x.continent = y.continent
) = (
  SELECT COUNT(name)
  FROM world z
  WHERE x.continent = z.continent
)
ORDER BY continent, population DESC


At first glance, that query looks unnecessarily redundant.  I'd like to simplify the query (or improve it in other ways), but I'm unsure how to do that.  Can someone show some better alternatives?

MySQL or Postgres are preferable feature sets, if it matters.



The problem is exactly as it appears in exercise 9 of this page in sqlzoo.  Queries can be tested and iterated upon there as well.
At least the inner countings are not necessary. You can easily use an EXISTS query, in negative:

SELECT name, continent, population
FROM world x
WHERE NOT EXISTS (
  SELECT 1
  FROM world y
  WHERE y.population &gt; 25000000
  AND x.continent = y.continent
)
ORDER BY continent, population DESC


This is more readable also since it literally says "give me the continents, except if there is a country where population is larger than the limit" and the person reading it doesn't have to spend time wondering what the counts are. Also the DB engine doesn't have to do any counting.

Another way would be to ask for the continents which have smaller countries and exclude those:

SELECT name, continent, population
FROM world x
WHERE continent NOT IN (
  SELECT DISTINCT continent
  FROM world y
  WHERE y.population &gt; 25000000
)
ORDER BY continent, population DESC


This may have better performance since it needs to only once find the continents that don't match and then run the main query as opposed to the previous one checking continents separately.