跳到主要内容

子查询

子查询是用括号包裹的查询表达式，作为更大外层查询的一部分出现。子查询通常基于 SELECT ... FROM，但在 Goose 中，像 PIVOT 这样的其他查询结构也可以作为子查询出现。

标量子查询​

标量子查询是只返回单个值的子查询。凡是可以使用表达式的地方都可以使用它。如果标量子查询返回多于一个值，会抛出错误（除非将 scalar_subquery_error_on_multiple_rows 设为 false，此时会随机选择一行）。

考虑下列表：

成绩表​

gradecourse
7Math
9Math
8CS
CREATE TABLE grades (grade INTEGER, course VARCHAR);
INSERT INTO grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

我们可以执行以下查询来获得最低分：

SELECT min(grade) FROM grades;
min(grade)
7

在 WHERE 子句中使用标量子查询后，我们就能找出该分数对应的课程：

SELECT course FROM grades WHERE grade = (SELECT min(grade) FROM grades);
course
Math

ARRAY 子查询​

返回多个值的子查询可以用 ARRAY 包裹，将所有结果收集为一个列表。

SELECT ARRAY(SELECT grade FROM grades) AS all_grades;
all_grades
[7, 9, 8]

子查询比较：ALL、ANY 与 SOME​

在标量子查询一节中，我们使用等值比较运算符（=）将一个标量表达式直接与子查询进行比较。 这种直接比较仅适用于标量子查询。

通过指定量词，标量表达式也可以与返回多行的单列子查询进行比较。可用量词有 ALL、ANY 和 SOME。其中 ANY 与 SOME 等价。

ALL​

ALL 量词表示：当_比较运算符左侧表达式_与_比较运算符右侧子查询_返回的每个值进行逐一比较时，若这些比较结果全部为 true，则整体比较结果为 true：

SELECT 6 <= ALL (SELECT grade FROM grades) AS adequate;

返回：

adequate
true

因为 6 小于或等于子查询返回的每个值（7、8、9）。

但是，下面这个查询

SELECT 8 >= ALL (SELECT grade FROM grades) AS excellent;

返回

excellent
false

因为 8 并不大于或等于子查询结果 7。因此，并非所有比较都为 true，所以 >= ALL 的整体结果为 false。

ANY​

ANY 量词表示：只要逐项比较中至少有一项为 true，整体比较结果就为 true。 例如：

SELECT 5 >= ANY (SELECT grade FROM grades) AS fail;

返回

fail
false

因为子查询中没有任何结果小于或等于 5。

量词 SOME 可替代 ANY：ANY 与 SOME 可互换。

EXISTS​

EXISTS 运算符用于测试子查询中是否存在任意行。若子查询返回一条或多条记录则为 true，否则为 false。EXISTS 通常在相关子查询中最有用，可用于表达半连接（semijoin）操作；当然也可用于非相关子查询。

例如，我们可以用它来判断某门课程是否存在成绩记录：

SELECT EXISTS (FROM grades WHERE course = 'Math') AS math_grades_present;
math_grades_present
true
SELECT EXISTS (FROM grades WHERE course = 'History') AS history_grades_present;
history_grades_present
false

上述示例中的子查询利用了 Goose 的 FROM-first syntax：可以省略 SELECT *。在其他 SQL 系统中，子查询通常要求写 SELECT 子句，但在 EXISTS 与 NOT EXISTS 子查询里它并无实际作用。

NOT EXISTS​

NOT EXISTS 运算符用于测试子查询中是否不存在任何行。若子查询返回空结果则为 true，否则为 false。NOT EXISTS 通常在相关子查询中最有用，可用于表达反连接（antijoin）操作。例如，找出没有兴趣条目的 Person：

CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE interest (PersonId BIGINT, topic VARCHAR);

INSERT INTO Person VALUES (1, 'Jane'), (2, 'Joe');
INSERT INTO interest VALUES (2, 'Music');

SELECT *
FROM Person
WHERE NOT EXISTS (FROM interest WHERE interest.PersonId = Person.id);
idname
1Jane

Goose 会自动识别 NOT EXISTS 查询何时表达的是反连接操作。你不需要手动把这类查询改写成 LEFT OUTER JOIN ... WHERE ... IS NULL。

IN 运算符​

IN 运算符用于检查左侧表达式是否包含在子查询结果或右侧表达式集合（RHS）中。如果该表达式存在于 RHS 中，IN 返回 true；如果不存在且 RHS 不含 NULL，返回 false；如果不存在但 RHS 含 NULL，返回 NULL。

我们可以像使用 EXISTS 一样使用 IN 运算符：

SELECT 'Math' IN (SELECT course FROM grades) AS math_grades_present;
math_grades_present
true

相关子查询​

到目前为止，这里展示的子查询都属于非相关子查询：子查询本身完全自包含，不依赖父查询即可运行。子查询还有另一类，称为相关子查询。对于相关子查询，子查询会使用父查询中的值。

从概念上讲，相关子查询会对父查询中的每一行执行一次。一个直观的理解方式是：相关子查询像一个应用到源数据集每一行上的函数。

例如，假设我们想找出每门课程的最低分，可以这样写：

SELECT *
FROM grades grades_parent
WHERE grade =
    (SELECT min(grade)
     FROM grades
     WHERE grades.course = grades_parent.course);
gradecourse
7Math
8CS

该子查询使用了父查询中的一列（grades_parent.course）。从概念上看，可将其视为一个函数，而相关列就是该函数的参数：

SELECT min(grade)
FROM grades
WHERE course = ?;

现在对每一行执行这个函数时，Math 会返回 7，CS 会返回 8。然后再将该结果与当前行的 grade 比较。因此，行 (Math, 9) 会被过滤掉，因为 9 <> 7。

将子查询每一行作为 Struct 返回​

在 SELECT 子句中直接使用子查询名称（不引用具体列）时，子查询的每一行都会变成一个 struct，其字段对应子查询的各列。例如：

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;
t
{'x': 41, 'y': hello}
{'x': 42, 'y': hello}
{'x': 43, 'y': hello}