SQL 中的关联子查询

Mark Raasveldt

2023-05-26 · 9 分钟

SQL 中的子查询是一种强大的抽象，它允许将简单的查询用作可组合的构建块。它们允许您将复杂问题分解为更小的部分，从而使编写、理解和维护大型复杂查询变得更容易。

DuckDB 使用最先进的子查询去关联优化器，使子查询能够非常高效地执行。因此，用户可以自由使用子查询来创建富有表现力的查询，而无需担心手动将子查询重写为连接。欲了解更多信息，请跳至性能部分。

子查询的类型

SQL 子查询主要有两种形式：作为表达式的子查询和作为表的子查询。用作表达式的子查询可以在 SELECT 或 WHERE 子句中使用。用作表的子查询可以在 FROM 子句中使用。在这篇博客文章中，我们将重点讨论用作表达式的子查询。未来的博客文章将讨论作为表的子查询。

作为表达式的子查询有三种形式。

标量子查询
EXISTS
IN/ANY/ALL

所有子查询都可以是关联的或非关联的。非关联子查询是独立于外部查询的查询。关联子查询是包含来自外部查询的表达式的子查询。关联子查询可以看作是参数化子查询。

非关联标量子查询

非关联标量子查询只能返回单个值。该常量值随后被替换并在查询中使用。举例说明其用途——假设我们想在数据集中选择所有最短的航班。我们可以运行以下查询来获取最短航班距离

SELECT min(distance)
FROM ontime;

min(distance)
31.0

我们可以手动获取这个距离，并将其用在 WHERE 子句中，以获取该航线上的所有航班。

SELECT uniquecarrier, origincityname, destcityname, flightdate
FROM ontime
WHERE distance = 31.0;

uniquecarrier	origincityname	destcityname	flightdate
AS	Petersburg, AK	Wrangell, AK	2017-01-15
AS	Wrangell, AK	Petersburg, AK	2017-01-15
AS	Petersburg, AK	Wrangell, AK	2017-01-16

然而——这需要我们在查询中硬编码常量。通过将第一个查询用作子查询，我们可以将最小距离计算作为查询的一部分。

SELECT uniquecarrier, origincityname, destcityname, flightdate
FROM ontime
WHERE distance = (
     SELECT min(distance)
     FROM ontime
);

关联标量子查询

尽管非关联子查询功能强大，但它们有一个严格的限制：只能返回单个值。通常，我们想要做的是参数化查询，以便每行可以返回不同的值。

例如，假设我们想找到每个航空公司的所有最短航班。我们可以使用以下参数化查询找到特定航空公司的最短航班

PREPARE min_distance_per_carrier AS
SELECT min(distance)
FROM ontime
WHERE uniquecarrier = ?;

我们可以执行这个预处理语句来获取特定航空公司的最短距离。

EXECUTE min_distance_per_carrier('UA');

min(distance)
67.0

如果我们想将这个参数化查询用作子查询，我们需要使用关联子查询。关联子查询允许我们通过引用来自外部查询的列，将参数化查询用作标量子查询。我们可以使用以下查询获取每个航空公司的最短航班集合

SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime AS ontime_outer
WHERE distance = (
     SELECT min(distance)
     FROM ontime
     WHERE uniquecarrier = ontime_outer.uniquecarrier
);

uniquecarrier	origincityname	destcityname	flightdate	distance
AS	Wrangell, AK	Petersburg, AK	2017-01-01	31.0
NK	Fort Lauderdale, FL	Orlando, FL	2017-01-01	177.0
VX	Las Vegas, NV	Los Angeles, CA	2017-01-01	236.0

注意来自外部关系（ontime_outer）的列是如何在查询内部使用的。这就是将子查询变为关联子查询的原因。外部关系中的列（ontime_outer.uniquecarrier）是子查询的参数。逻辑上，对于 ontime 中存在的每一行，子查询都会执行一次，其中该行中列的值被替换为参数。

为了更清楚地说明关联子查询本质上是参数化查询，我们可以使用 DuckDB 的宏创建一个包含该查询的标量宏。

CREATE MACRO min_distance_per_carrier(param) AS (
     SELECT min(distance)
     FROM ontime
     WHERE uniquecarrier = param
);

然后我们可以在原始查询中使用该宏，就像它是一个函数一样。

SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime AS ontime_outer
WHERE distance = min_distance_per_carrier(ontime_outer.uniquecarrier);

这与将关联子查询置于查询内部得到的结果相同，但更简洁，因为我们可以更有效地将查询分解为多个片段。

EXISTS

EXISTS 可用于检查给定子查询是否有任何结果。当用作关联子查询时，这非常强大。例如，如果我们想获取每条航线上最后一次飞行的航班，我们可以使用 EXISTS。

我们可以使用以下查询获取给定航线上在特定日期之后的所有航班列表

PREPARE flights_after_date AS
SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime
WHERE origin = ? AND dest = ? AND flightdate > ?;

EXECUTE flights_after_date('LAX', 'JFK', DATE '2017-05-01');

uniquecarrier	origincityname	destcityname	flightdate	distance
AA	Los Angeles, CA	New York, NY	2017-08-01	2475.0
AA	Los Angeles, CA	New York, NY	2017-08-02	2475.0
AA	Los Angeles, CA	New York, NY	2017-08-03	2475.0

现在，为了获取航线上的最后一次航班，我们需要找到没有更晚航班存在的航班。

SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime AS ontime_outer
WHERE NOT EXISTS (
     SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
     FROM ontime
     WHERE origin = ontime_outer.origin
       AND dest = ontime_outer.dest
       AND flightdate > ontime_outer.flightdate
);

uniquecarrier	origincityname	destcityname	flightdate	distance
AA	Daytona Beach, FL	Charlotte, NC	2017-02-27	416.0
EV	Abilene, TX	Dallas/Fort Worth, TX	2017-02-15	158.0
EV	Dallas/Fort Worth, TX	Durango, CO	2017-02-13	674.0

IN / ANY / ALL

IN 可用于检查子查询返回的结果中是否存在给定值。例如，我们可以使用以下查询获取数据集中执行航班超过 250 000 次的所有航空公司的列表

SELECT uniquecarrier
FROM ontime
GROUP BY uniquecarrier
HAVING count(*) > 250000;

然后我们可以使用 IN 子句获取这些航空公司执行的所有航班。

SELECT *
FROM ontime
WHERE uniquecarrier IN (
     SELECT uniquecarrier
     FROM ontime
     GROUP BY uniquecarrier
     HAVING count(*) > 250000
);

如果我们不想计算每个航空公司执行的航班总数，而是计算给定航线上的航班总数，那么关联子查询在这里会很有用。我们可以使用以下查询选择所有由在给定航线上执行至少 1000 次航班的航空公司执行的航班。

SELECT *
FROM ontime AS ontime_outer
WHERE uniquecarrier IN (
     SELECT uniquecarrier
     FROM ontime
     WHERE ontime.origin = ontime_outer.origin
       AND ontime.dest = ontime_outer.dest
     GROUP BY uniquecarrier
     HAVING count(*) > 1000
);

ANY 和 ALL 是 IN 的泛化。IN 检查值是否存在于子查询返回的集合中。这等同于 = ANY(...)。ANY 和 ALL 运算符可用于执行其他比较运算符（例如 >, <, <>）。上述查询可以改写为以下形式的 ANY。

SELECT *
FROM ontime AS ontime_outer
WHERE uniquecarrier = ANY (
     SELECT uniquecarrier
     FROM ontime
     WHERE ontime.origin = ontime_outer.origin
       AND ontime.dest = ontime_outer.dest
     GROUP BY uniquecarrier
     HAVING count(*) > 1000
);

性能

标量子查询逻辑上执行一次，而关联子查询逻辑上执行每行一次。因此，很自然会认为关联子查询非常昂贵，出于性能原因应避免使用。

尽管这在许多 SQL 系统中是事实，但在 DuckDB 中并非如此。在 DuckDB 中，子查询总是被去关联的。DuckDB 使用最先进的子查询去关联算法，如 Unnesting Arbitrary Queries 论文中所述。这使得所有子查询都可以被去关联并作为单个、更高效的查询执行。

在 DuckDB 中，关联不意味着性能下降。

如果我们使用 EXPLAIN 查看关联标量子查询的查询计划，我们可以看到该查询已被转换为哈希聚合，然后是哈希连接。这使得查询能够非常高效地执行。

EXPLAIN SELECT uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime AS ontime_outer
WHERE distance = (
     SELECT min(distance)
     FROM ontime
     WHERE uniquecarrier = ontime_outer.uniquecarrier
);

┌───────────────────────────┐
│         HASH_JOIN         │ 
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │ 
│      uniquecarrier =      │ 
│       uniquecarrier       ├──────────────┐
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         SEQ_SCAN          ││       HASH_GROUP_BY       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           ontime          ││       uniquecarrier       │
└───────────────────────────┘│       min(distance)       │
                             └─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐
                             │         SEQ_SCAN          │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │           ontime          │
                             └───────────────────────────┘

当我们比较 DuckDB 中此查询的运行时间与 Postgres 和 SQLite 中的运行时间时，我们可以看到子查询去关联所带来的巨大性能差异。在 2017 年的 ontime 数据集（大约 ~400 万行）上运行上述查询时，我们得到以下性能结果

DuckDB	Postgres	SQLite
0.06 s	>48 小时	>48 小时

由于 Postgres 和 SQLite 不对子查询进行去关联，因此查询不仅是逻辑上，而且是实际地对每一行执行一次。结果，子查询在这些系统中执行了400 万次，这花费了大量时间。

在这种情况下，可以手动去关联查询并生成以下 SQL

SELECT ontime.uniquecarrier, origincityname, destcityname, flightdate, distance
FROM ontime
JOIN (
     SELECT uniquecarrier, min(distance) AS min_distance
     FROM ontime
     GROUP BY uniquecarrier
  ) AS subquery 
  ON ontime.uniquecarrier = subquery.uniquecarrier
 AND distance = min_distance;

通过手动执行去关联，SQLite 和 Postgres 的性能显著提高。然而，这两个系统仍然比 DuckDB 慢 30 倍以上。

DuckDB	Postgres	SQLite
0.06 s	1.98 s	2.81 s

请注意，尽管可以通过重写 SQL 手动去关联某些子查询，但这并非总是可行的。正如 Unnesting Arbitrary Queries 论文中所述，SQL 中不存在的特殊连接类型对于去关联任意查询是必要的。

在 DuckDB 中，这些特殊连接类型将由系统自动生成以去关联所有子查询。事实上，DuckDB 不支持执行未去关联的子查询。所有子查询都将在 DuckDB 执行它们之前被去关联。

结论

子查询是一个非常强大的工具，它允许您将任意查询转换为即时函数。当与 DuckDB 强大的子查询去关联结合使用时，它们可以极其高效地执行，使以前无法处理的查询不仅成为可能，而且速度快。

本文中

子查询的类型

SQL 中的关联子查询

子查询的类型

非关联标量子查询

关联标量子查询

EXISTS

IN / ANY / ALL

性能

结论

本文中

近期文章

DuckLake 0.2

通过 GitHub 发现 DuckDB 用例