Why does this code work with a less than sign when the question requests for a greater than sign (nested selects from SQL zoo)?

Question

(updated) I'm trying to learn SQL from the following website: https://sqlzoo.net/wiki/Nested_SELECT_Quiz

In this quiz, the second question asks to find countries belonging to regions with all populations over 50000 -

It says that this is the correct answer:

SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)

Thats the answer it gives me. Can anyone explain in plain English why this works? If we're looking for population over 50,000 why is the code using a less than sign? And how do nested selects work in general then?

Answer 1

Order matters. 50000 < population is the same as population > 50000.

Why write it this funny way? Because you have to.

Specifically all is a quantified comparison predicate and it must be of the form <value> <operator> all(<subquery>). So 50000 < all(subquery). I can't say why it cannot be reversed, possibly to make parsing this special case easier.

And how do nested selects work in general then?

all is true if every row of the subquery meets the condition. 50000 < all(subquery) means that 50,000 is less than every row in the subquery (or every row in the subquery is over 50000).

SELECT name,region,population
FROM bbc x
WHERE 50000 < ALL (
  SELECT population
  FROM bbc y
  WHERE x.region=y.region AND y.population>0
)

The subquery runs once for each row in bbc. x is the bbc table in the original query and y is the bbc table in the subquery. where x.region=y.region filters the subquery results to only rows in the same region as the original row.