Assist me to appreciate this: Within the sqlzoo tutorial for question 3a ("Discover the biggest country in every region"), how come affixing 'AND population > 0' towards the nested Choose statement get this to correct?
This is because since the:
AND population > 0
...is blocking the null row for that region "Europe", title "Vatican", which reduces the:
WHERE population >= ALL (SELECT population FROM ...)
...because NULL is not something, so Russia will not be rated correctly. The ALL operator mandates that the worthiness you had been evaluating to become greater or comparable to ALL the values came back in the subquery, which could never happen when there is a NULL inside.
My query would have been either:
SELECT region, name, population FROM bbc x WHERE population = (SELECT MAX(population) FROM bbc y WHERE y.region = x.region)
...or, utilizing a JOIN:
SELECT x.region, x.name, x.population FROM bbc x JOIN (SELECT y.region, MAX(y.population) AS max_pop FROM bbc y GROUP BY y.region) z ON z.region = x.region AND z.max_pop = x.population
No it does not. Biggest country includes a priori nonzero population.
It's like checking if your biggest book has any pages inside it.