这个self join是如何工作的？

Question

这是来自SQLZOO.NET的问题

+-------------+-----------+---------+
|    name     | continent |  area   |
+-------------+-----------+---------+
| Afghanistan | Asia      | 652230  |
| Albania     | Europe    | 2831741 |
| Algeria     | Africa    | 28748   |
| ...         | ...       | ...     |
+-------------+-----------+---------+

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

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

Answer 1

为了解释，我假设一个只有5个国家的世界表格如下：

Algeria      2381741
Australia    7692024
South Africa 1221037
New Zealand   268021
/*And to make it a little interesting:*/
Algeria Twin 2381741

子查询与基查询的每一行匹配，每次匹配1。这就是所谓的相关子查询。虽然相关的子查询工作良好，但通常认为它们是危险的，因为如果优化器不能找出更有效的等效结构，它们的性能特性就会很差。

下表说明了如何计算数据的逻辑视图。请注意，数据库的查询引擎可能能够在内部将计划转换为数学上等价的东西，但效率要高得多。

+-------------+--------------+--------+
| continent   |   name       |  area  |
+-------------+--------------+--------+
|Africa       | Algeria      | 2381741| >= ALL( /*y.continent='Africa'*/
                                               2381741, /*Alegria*/
                                               1221037, /*South Africa*/
                                               2381741) /*Alegria Twin*/
|Oceania      | Australia    | 7692024| >= ALL( /*y.continent='Oceania'*/
                                               7692024, /*Australia*/
                                               268021)  /*New Zealand*/
|Africa       | South Africa | 1221037| >= ALL( /*y.continent='Africa'*/
                                               2381741, /*Alegria*/
                                               1221037, /*South Africa*/
                                               2381741) /*Alegria Twin*/
|Oceania      | New Zealand  |  268021| >= ALL( /*y.continent='Oceania'*/
                                               7692024, /*Australia*/
                                               268021)  /*New Zealand*/
|Africa       | Algeria Twin | 2381741| >= ALL( /*y.continent='Africa'*/
                                               2381741, /*Alegria*/
                                               1221037, /*South Africa*/
                                               2381741) /*Alegria Twin*/
+-------------+--------------+--------+

WHERE area = ( SELECT MAX(y.area)
               FROM   world y
               WHERE  y.continent=x.continent)

WHERE area = ( SELECT y.area
               FROM   world y
               WHERE  y.continent=x.continent
               ORDER BY y.area DESC LIMIT 1)

但是，要小心下面这些看起来是等价的，但实际上并非如此。

/* The problem here is that only 1 Algeria will happen to be 
   first in the sub-query. Meaning 1 row will be missing from 
   the final result set. */
WHERE name = ( SELECT y.name
               FROM   world y
               WHERE  y.continent=x.continent
               ORDER BY y.area DESC LIMIT 1)

最后，我提到相关的子查询可能会有性能问题。因此，如果可以的话，通常最好考虑将相关的子查询改写为直接连接到FROM子句中的子查询的子查询。例如。

SELECT  x.contient, x.name, x.area
FROM    world x
        INNER JOIN (
            SELECT MAX(y.area) as max_area, y.continent
            FROM   world y
            GROUP BY y.continent
        ) z ON
            x.continent = z.continent
        AND x.area = z.max_area