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?

Answers


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.


Need Your Help

Calculate category groups with results

php mysql drupal grouping category-theory

So we have a Drupal site with nodes tagged in 4 vocabularies. In SQL terms, these are all simply joins on a base table. In simplified terms, it looks roughly like this:

Get requested page <url> inside ajax call on some web page

ruby ajax parsing nokogiri

I'm using Nokogiri, at this moment, I have variable which contains code of some page: doc = Nokogiri::HTML(open(page)). There is script in code, ajax calls: