1

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,

world table

I want to find the continents where all countries have a population <= 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 <= 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.

3

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 > 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 > 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.

1

You can also use window aggregates for this. Find the maximum country population for each continent and keep only those that are not over 25 million.
This will work in Postgres (and MariaDB 10.2+ that has added window functions and CTEs) but not in current MySQL versions:

WITH ct AS
  ( SELECT name, continent, population,
           MAX(population) OVER (PARTITION BY continent) AS max_population 
    FROM world
  )
SELECT name, continent, population
FROM ct
WHERE max_population <= 25000000
ORDER BY continent, population DESC ; 

A similar solution with a derived table will work in MySQL:

SELECT w.name, w.continent, w.population
FROM 
  ( SELECT continent
    FROM world
    GROUP BY continent
    HAVING MAX(population) <= 25000000
  ) AS ct
  JOIN world AS w
  ON  w.continent = ct.continent
ORDER BY continent, population DESC ; 
  • Interesting use of window functions! I wish I could choose two answers. I appreciate the more advanced features demonstrated in this one. – kdbanman Mar 22 '17 at 3:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.