Postgresql 中文操作指南

9.23. Subquery Expressions #

此部分描述 PostgreSQL 中可用的符合 SQL 的子查询表达式。此部分中记录的所有表达式形式均返回布尔值（真/假）结果。

This section describes the SQL-compliant subquery expressions available in PostgreSQL. All of the expression forms documented in this section return Boolean (true/false) results.

9.23.1. EXISTS #

EXISTS (subquery)

EXISTS 的参数是任意的 SELECT 语句或 subquery。对子查询进行评估，以确定它是否返回任何行。如果它返回至少一行，则 EXISTS 的结果为“真”；如果子查询不返回任何行，则 EXISTS 的结果为“假”。

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is “true”; if the subquery returns no rows, the result of EXISTS is “false”.

子查询可以参考周围查询中的变量，这些变量在子查询的任何一次评估期间充当常量。

The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.

通常仅执行子查询足够长的时间以确定是否返回至少一行，而不是一直执行到完成。编写有副作用（例如调用序列函数）的子查询是不明智的；这些副作用是否发生可能是不可预测的。

The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has side effects (such as calling sequence functions); whether the side effects occur might be unpredictable.

由于结果只取决于是否返回任何行，而不取决于这些行的内容，因此子查询的输出列表通常并不重要。常见的编码约定是以 EXISTS(SELECT 1 WHERE …) 形式编写所有 EXISTS 测试。然而，有例外情况，例如使用 INTERSECT 的子查询。

Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant. A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE …). There are exceptions to this rule however, such as subqueries that use INTERSECT.

这个简单示例类似于对 col2 的内部联接，但是即使有几个匹配的 tab2 行，它也只为每个 tab1 行生成最多一个输出行：

This simple example is like an inner join on col2, but it produces at most one output row for each tab1 row, even if there are several matching tab2 rows:

SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

9.23.2. IN #

expression IN (subquery)

右侧是一个括起来的子查询，它必须只返回一列。对左表达式进行评估，并将其与子查询结果的每一行进行比较。如果找到任何相等的子查询行，则 IN 结果为“真”。如果没有找到相等的行（包括子查询不返回任何行的情况），则该结果为“假”。

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is “true” if any equal subquery row is found. The result is “false” if no equal row is found (including the case where the subquery returns no rows).

请注意，如果左表达式产生 NULL，或者如果没有相等的右侧值，且至少有一个右侧行产生 NULL，则 IN 构造的结果将为 NULL，而非假。这与 SQL 关于 NULL 值布尔组合的正常规则是一致的。

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false. This is in accordance with SQL’s normal rules for Boolean combinations of null values.

与 EXISTS 一样，假设子查询将被完整评估是不明智的。

As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor IN (subquery)

此形式 IN 的左侧是一个行构造函数，如 Section 4.2.13 中所述。右侧是一个加了括号的子查询，该子查询必须返回与左侧行中的表达式数量完全相同数量的列。左侧表达式在子查询结果的每一行上按行评估并比较。如果找到任何相等的子查询行，IN 的结果为“true”。如果找不到相等行，则结果为“false”（包括子查询未返回任何行的情况）。

The left-hand side of this form of IN is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of IN is “true” if any equal subquery row is found. The result is “false” if no equal row is found (including the case where the subquery returns no rows).

与往常一样，行中的 NULL 值按照 SQL 布尔表达式的正常规则组合。如果所有相应的成员都为非 NULL 且相等，则认为两行相等；如果任何相应的成员为非 NULL 且不相等，则行不相等；否则，该行比较的结果未知（NULL）。如果所有按行比较的结果要么不相等，要么为 NULL，并且至少有一个 NULL，则 IN 的结果为 NULL。

As usual, null values in the rows are combined per the normal rules of SQL Boolean expressions. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of that row comparison is unknown (null). If all the per-row results are either unequal or null, with at least one null, then the result of IN is null.

9.23.3. NOT IN #

expression NOT IN (subquery)

右侧是一个括起来的子查询，它必须只返回一列。对左表达式进行评估，并将其与子查询结果的每一行进行比较。如果仅找到不相等的子查询行（包括子查询不返回任何行的情况），则 NOT IN 结果为“真”。如果找到任何相等的行，则该结果为“假”。

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of NOT IN is “true” if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is “false” if any equal row is found.

注意，如果左侧表达式生成 null，或者如果不存在相等右侧值且至少有一个右侧行生成 null，NOT IN 构造的结果将是 null，而不是 true。这符合 SQL 关于 null 值布尔组合的正常规则。

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true. This is in accordance with SQL’s normal rules for Boolean combinations of null values.

与 EXISTS 一样，假设子查询将被完整评估是不明智的。

As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor NOT IN (subquery)

此形式 NOT IN 的左侧是一个行构造函数，如 Section 4.2.13 中所述。右侧是一个加了括号的子查询，该子查询必须返回与左侧行中的表达式数量完全相同数量的列。左侧表达式在子查询结果的每一行上按行评估并比较。如果仅找到不相等的子查询行（包括子查询未返回任何行的情况），NOT IN 的结果为“true”。如果找到任何相等行，则结果为“false”。

The left-hand side of this form of NOT IN is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of NOT IN is “true” if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is “false” if any equal row is found.

与往常一样，行中的 null 值根据 SQL 布尔表达式的正常规则组合。如果所有成员都为非空且相等，则两行被认为相等；如果任何相应的成员为非空且不相等，则两行不相等；否则，该行比较的结果未知（null）。如果所有按行结果都为不相等或 null（其中至少有一个为 null），则 NOT IN 的结果为 null。

9.23.4. ANY/SOME #

expression operator ANY (subquery)
expression operator SOME (subquery)

右侧是一个带括号的子查询，它必须返回恰好一列。左侧表达式经过计算，并使用指定的 operator 与子查询结果的每一行进行比较，该 operator 必须生成一个布尔结果。如果获得任何真结果，ANY 的结果为“true”。如果没有找到任何真结果（包括子查询没有返回任何行的情况），结果为“false”。

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ANY is “true” if any true result is obtained. The result is “false” if no true result is found (including the case where the subquery returns no rows).

SOME 是 ANY 的同义词。IN 等效于 = ANY。

SOME is a synonym for ANY. IN is equivalent to = ANY.

注意，如果不存在任何成功并且至少有一个右侧行针对算子的结果生成 null，则 ANY 构造的结果将为 null，而不是 false。这符合 SQL 关于 null 值布尔组合的正常规则。

Note that if there are no successes and at least one right-hand row yields null for the operator’s result, the result of the ANY construct will be null, not false. This is in accordance with SQL’s normal rules for Boolean combinations of null values.

与 EXISTS 一样，假设子查询将被完整评估是不明智的。

As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)

此形式 ANY 的左侧是一个行构造函数，如 Section 4.2.13 中所述。右侧是一个加了括号的子查询，该子查询必须返回与左侧行中的表达式数量完全相同数量的列。左侧表达式使用给定的 operator 在子查询结果的每一行上按行评估并比较。如果比较对任何子查询行返回 true，ANY 的结果为“true”。如果比较对每个子查询行（包括子查询未返回任何行的情况）返回 false，则结果为“false”。如果没有与子查询行的比较返回 true，并且至少有一个比较返回 NULL，则结果为 NULL。

The left-hand side of this form of ANY is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given operator. The result of ANY is “true” if the comparison returns true for any subquery row. The result is “false” if the comparison returns false for every subquery row (including the case where the subquery returns no rows). The result is NULL if no comparison with a subquery row returns true, and at least one comparison returns NULL.

有关行构造函数比较的含义，请参阅 Section 9.24.5。

See Section 9.24.5 for details about the meaning of a row constructor comparison.

9.23.5. ALL #

expression operator ALL (subquery)

右侧是一个带括号的子查询，它必须返回恰好一列。左侧表达式经过计算，并使用指定的 operator 与子查询结果的每一行进行比较，该 operator 必须生成一个布尔结果。如果所有行都生成 true（包括子查询没有返回任何行的情况），ALL 的结果为“true”。如果找到任何 false 结果，结果为“false”。如果没有与子查询行的比较返回 false，且至少有一个比较返回 NULL，则结果为 NULL。

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ALL is “true” if all rows yield true (including the case where the subquery returns no rows). The result is “false” if any false result is found. The result is NULL if no comparison with a subquery row returns false, and at least one comparison returns NULL.

NOT IN 等效于 <> ALL。

NOT IN is equivalent to <> ALL.

与 EXISTS 一样，假设子查询将被完整评估是不明智的。

As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor operator ALL (subquery)

此形式 ALL 的左侧是一个行构造函数，如 Section 4.2.13 中所述。右侧是一个加了括号的子查询，该子查询必须返回与左侧行中的表达式数量完全相同数量的列。左侧表达式使用给定的 operator 在子查询结果的每一行上按行评估并比较。如果比较对所有子查询行（包括子查询未返回任何行的情况）返回 true，ALL 的结果为“true”。如果比较对任何子查询行返回 false，则结果为“false”。如果没有与子查询行的比较返回 false，并且至少有一个比较返回 NULL，则结果为 NULL。

The left-hand side of this form of ALL is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given operator. The result of ALL is “true” if the comparison returns true for all subquery rows (including the case where the subquery returns no rows). The result is “false” if the comparison returns false for any subquery row. The result is NULL if no comparison with a subquery row returns false, and at least one comparison returns NULL.

有关行构造函数比较的含义，请参阅 Section 9.24.5。

See Section 9.24.5 for details about the meaning of a row constructor comparison.

9.23.6. Single-Row Comparison #

row_constructor operator (subquery)

左侧是一个行构造函数，如 Section 4.2.13 中所述。右侧是一个加了括号的子查询，该子查询必须返回与左侧行中的表达式数量完全相同数量的列。而且，该子查询不能返回多于一行。（如果返回零行，则结果为 null。）左侧在单一子查询结果行上按行评估并比较。

The left-hand side is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. Furthermore, the subquery cannot return more than one row. (If it returns zero rows, the result is taken to be null.) The left-hand side is evaluated and compared row-wise to the single subquery result row.

有关行构造函数比较的含义，请参阅 Section 9.24.5。

See Section 9.24.5 for details about the meaning of a row constructor comparison.