postgreSQL 相关子查询事件序列

Question

此查询查找每个大陆中按面积计算的最大国家/地区。它显示大陆、名称和面积。

SELECT
c1.continent,
c1.name,
c1.area
FROM
countries c1
WHERE
c1.area = (
SELECT
MAX(c2.area)
FROM
countries c2
WHERE
c1.continent = c2.continent
)

如果我理解正确，子查询会将每个条目的大陆从 c1 匹配到具有相同大陆的所有 c2 条目并找到最大值。如果是这样，子查询将为 c1 中的每个条目运行。这似乎没有必要吗？

有人可以解释一下此查询中发生的事件的顺序以及是否有更好的方法可以做到这一点。谢谢。

Answer 1

您需要了解 SQL 查询是非过程性的：它们描述结果集，而不是如何创建结果集。

如果索引在countries(continent, area)上可用，那么"子查询"只是在索引中查找 - 一个非常有效的操作。

如果索引不可用，则某些数据库引擎足够智能，可以将子查询实现为一种连接操作 - 如果数据足够大，通常使用哈希连接。

诚然，一些数据库会为外部查询中的每一行运行子查询，实质上是执行嵌套循环连接。 但是没有要求引擎这样做。

Answer 2

Gordon已经回答了关于如何处理这个问题的部分。

如果您想知道 Postgres 是否将共同相关的子查询重写为连接 - 使用explain (analyze) ...检查执行计划。

---

如果有更好的方法可以做到这一点。

是的，有。使用标准 SQL，使用窗口函数时通常要快得多：

select continent, name, area
from (
select continent, name, area, 
dense_rank() over (partition by continent order by area desc) as rnk
from countries
) t
where rnk = 1;

使用Postgres 使用distinct on ()通常比使用窗口函数的解决方案更快：

select distinct on (continent) * 
from countries 
order by continent, area desc