mysql - 另一个 SQL 教程问题 : Field > 0?

Question

好的，this one (3a；提供答案的示例问题)让我摸不着头脑:

bbc(name, region, area, population, gdp)
3a. Find the largest country in each region:

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

当我从数据库引擎循环遍历表条目并将每个 x.region 与当前匹配的角度考虑时，我理解了“WHERE y.region = x.region”的概念y.region(在嵌套的 SELECT 中)...但是 wtf 是 'AND population > 0' 吗？没有它就不是正确答案，但我不明白为什么不...

Answer 1

该子句存在只是因为欧洲表(针对梵蒂冈)中有一个条目在人口列中具有 NULL。以下也有效，我相信更容易理解:

SELECT region, name, population
  FROM bbc x
 WHERE population >= ALL
    (SELECT population
       FROM bbc y
      WHERE y.region = x.region
        AND population IS NOT NULL)

在MySQL documentation for ALL subqueries ，有一个有用的评论(强调他们的):

In general, tables containing NULL values and empty tables are "edge cases." When writing subquery code, always consider whether you have taken those two possibilities into account.