SQLZOO : Derived Table inside the subquery vs Derived Table outside a subquery?

Question

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial 的问题 7

“找到每个大陆中最大的国家(按面积)，显示大陆、名称和面积:”

我不明白为什么

SELECT continent, name, area FROM world
  WHERE area >= ALL
    (SELECT area FROM world as x
        WHERE x.continent IN (continent))

结果:

+-----------+--------+----------+
| continent |   name |    area  |
+-----------+--------+----------+
| Eurasia   | Russia | 17125242 |
+-----------+--------+----------+

但是

SELECT continent, name, area FROM world as x
  WHERE area >= ALL
    (SELECT area FROM world 
        WHERE x.continent IN (continent))

或

SELECT continent, name, area FROM world as x
  WHERE area >= ALL
    (SELECT area FROM world as y
        WHERE x.continent IN (y.continent))

两者都给出了正确的结果:

+---------------+------------+----------+
|   continent   |    name    |   area   |
+---------------+------------+----------+
| Africa        | Algeria    |  2381741 |
| Oceania       | Australia  |  7692024 |
| South America | Brazil     |  8515767 |
| North America | Canada     |  9984670 |
| Asia          | China      |  9596961 |
| Caribbean     | Cuba       |   109884 |
| Europe        | Kazakhstan |  2724900 |
| Eurasia       | Russia     | 17125242 |
+---------------+------------+----------+

我不明白为什么在子查询内有派生表与在子查询外有派生表不同。它们不应该都一样吗？

Answer 1

您的查询:

SELECT continent, name, area FROM world
  WHERE area >= ALL
    (SELECT area FROM world as x
        WHERE x.continent IN (continent))

等同于:

SELECT continent, name, area FROM world
  WHERE area >= ALL
    (SELECT area FROM world as x
        WHERE x.continent IN (x.continent))
<=>
SELECT continent, name, area FROM world
  WHERE area >= ALL(SELECT area FROM world as x)
--(assuming that continent is defined as NOT NULL)
-- In result you are searching for biggest country(correct answer - Russia)

为避免这种情况，您应该明确为列名添加前缀:

SELECT continent, name, area FROM world
  WHERE area >= ALL
    (SELECT area FROM world as x
        WHERE x.continent IN (continent))
=>
SELECT continent, name, area FROM world
WHERE area >= ALL
    (SELECT x.area FROM world as x
        WHERE x.continent IN (world.continent))