SQL: Redundant WHERE clause specifying column is > 0?
Help me understand this: In the sqlzoo tutorial for question 3a ("Find the largest country in each region"), why does attaching 'AND population > 0' to the nested SELECT statement make this correct?
The reason is because the:
AND population > 0
...is filtering out the null row for the region "Europe", name "Vatican", which complicates the:
WHERE population >= ALL (SELECT population FROM ...)
...because NULL isn't a value, so Russia won't be ranked properly. The ALL operator requires that the value you were comparing to be greater or equal to ALL the values returned from the subquery, which can never happen when there's a NULL in there.
My query would've been either:
SELECT region, name, population FROM bbc x WHERE population = (SELECT MAX(population) FROM bbc y WHERE y.region = x.region)
...or, using 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 doesn't. Largest country has a priori nonzero population.
It's like checking if a largest book has any pages in it.