Postgresql 中文操作指南
9.24. Row and Array Comparisons #
本节描述了一些专用结构，用于在值组之间进行多次比较。这些形式在语法上与前一节的子查询形式相关，但并不涉及子查询。涉及数组子表达式的形式是 PostgreSQL 扩展；其余形式符合 SQL 标准。本节中记录的所有表达式形式都返回 Boolean（真/假）结果。
This section describes several specialized constructs for making multiple comparisons between groups of values. These forms are syntactically related to the subquery forms of the previous section, but do not involve subqueries. The forms involving array subexpressions are PostgreSQL extensions; the rest are SQL-compliant. All of the expression forms documented in this section return Boolean (true/false) results.
9.24.1. IN #
expression IN (value [, ...])右侧是一个括号中的表达式列表。如果左侧表达式的结果等于任何右侧表达式，则结果为“真”。这是下列表达式的简写：
The right-hand side is a parenthesized list of expressions. The result is “true” if the left-hand expression’s result is equal to any of the right-hand expressions. This is a shorthand notation for
expression = value1
OR
expression = value2
OR
...请注意，如果左侧表达式产生 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 expression 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.
9.24.2. NOT IN #
expression NOT IN (value [, ...])右侧是一个括号中的表达式列表。如果左侧表达式的结果不等于所有右侧表达式，则结果为“真”。这是下列表达式的简写：
The right-hand side is a parenthesized list of expressions. The result is “true” if the left-hand expression’s result is unequal to all of the right-hand expressions. This is a shorthand notation for
expression <> value1
AND
expression <> value2
AND
...请注意，如果左侧表达式产生 Null，或者没有相等的右侧值且至少有一个右侧表达式产生 Null，则 NOT 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 expression yields null, the result of the NOT IN construct will be null, not true as one might naively expect. This is in accordance with SQL’s normal rules for Boolean combinations of null values.
Tip
在所有情况下，x NOT IN y 等同于 NOT (x IN y)。但是，与 IN 相比，Null 值在 NOT IN 中绊倒新手的可能性更大。如果可能，最好以肯定的形式表达条件。
x NOT IN y is equivalent to NOT (x IN y) in all cases. However, null values are much more likely to trip up the novice when working with NOT IN than when working with IN. It is best to express your condition positively if possible.
9.24.3. ANY/SOME (array) #
expression operator ANY (array expression)
expression operator SOME (array expression)右侧是一个括号中的表达式，它必须产生一个数组值。左侧表达式将得到评估，并根据指定的 operator 与数组的每个元素进行比较，operator 必须产生一个 Boolean 结果。如果得到任何真结果，则 ANY 的结果为“真”。如果未找到任何真结果（包括数组中元素为零的情况），则结果为“假”。
The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array 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 array has zero elements).
如果数组表达式产生一个 Null 数组，则 ANY 的结果将为 Null。如果左侧表达式产生 Null，则 ANY 的结果通常为 Null（虽然非严格比较运算符可能会产生不同的结果）。此外，如果右侧数组包含任何 Null 元素，并且未得到任何真比较结果，则 ANY 的结果将为 Null，而不是假（同样，假定是一个严格比较运算符）。这符合 SQL 的 Null 值布尔组合的常规规则。
If the array expression yields a null array, the result of ANY will be null. If the left-hand expression yields null, the result of ANY is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of ANY will be null, not false (again, assuming a strict comparison operator). This is in accordance with SQL’s normal rules for Boolean combinations of null values.
SOME 是 ANY 的同义词。
SOME is a synonym for ANY.
9.24.4. ALL (array) #
expression operator ALL (array expression)右侧是一个括号中的表达式，它必须产生一个数组值。左侧表达式将得到评估，并根据指定的 operator 与数组的每个元素进行比较，operator 必须产生一个 Boolean 结果。如果所有比较都产生真（包括数组中元素为零的情况），则 ALL 的结果为“真”。如果找到任何假结果，则结果为“假”。
The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ALL is “true” if all comparisons yield true (including the case where the array has zero elements). The result is “false” if any false result is found.
如果数组表达式产生一个 Null 数组，则 ALL 的结果将为 Null。如果左侧表达式产生 Null，则 ALL 的结果通常为 Null（虽然非严格比较运算符可能会产生不同的结果）。此外，如果右侧数组包含任何 Null 元素，并且未得到任何假比较结果，则 ALL 的结果将为 Null，而不是真（同样，假定是一个严格比较运算符）。这符合 SQL 的 Null 值布尔组合的常规规则。
If the array expression yields a null array, the result of ALL will be null. If the left-hand expression yields null, the result of ALL is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no false comparison result is obtained, the result of ALL will be null, not true (again, assuming a strict comparison operator). This is in accordance with SQL’s normal rules for Boolean combinations of null values.
9.24.5. Row Constructor Comparison #
row_constructor operator row_constructor每一方都是行构造函数，如 Section 4.2.13 中所述。两个行构造函数必须具有相同数量的字段。给定的 operator 应用于每对相应字段。（由于字段可以是不同类型，这意味着可以针对每对字段选择不同的特定运算符。）所有选定的运算符都必须是一些 B 树运算符类中的成员，或成为 B 树运算符类中一个 = 成员的否定者，这意味着行构造函数比较仅在 operator 为 =、<>、<、⇐、> 或 >=，或者具有类似于其中一个的语义时才可能。
Each side is a row constructor, as described in Section 4.2.13. The two row constructors must have the same number of fields. The given operator is applied to each pair of corresponding fields. (Since the fields could be of different types, this means that a different specific operator could be selected for each pair.) All the selected operators must be members of some B-tree operator class, or be the negator of an = member of a B-tree operator class, meaning that row constructor comparison is only possible when the operator is =, <>, <, ⇐, >, or >=, or has semantics similar to one of these.
= 和 <> 的情况与其他情况略有不同。当所有相应的成员都不为 null 且相等时，这两行被视为相等；如果任意一个相应的成员不为 null 且不相等，则这两行不相等；否则，行比较的结果未知（为 null）。
The = and <> cases work slightly differently from the others. 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 the row comparison is unknown (null).
对于 <、⇐、> 和 >= 的情况，各行元素从左到右比较，一旦找到不相等或为 null 的元素对，则停止。如果这一对元素中的任一个为 null，则行比较的结果未知（为 null）；否则，这一对元素的比较决定了结果。例如，ROW(1,2,NULL) < ROW(1,3,0) 产生 true，而不是 null，因为第三对元素不被考虑。
For the <, ⇐, > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null); otherwise comparison of this pair of elements determines the result. For example, ROW(1,2,NULL) < ROW(1,3,0) yields true, not null, because the third pair of elements are not considered.
row_constructor IS DISTINCT FROM row_constructor此构造类似于 <> 行比较，但它不会对 null 输入产生 null。相反，任何 null 值都被认为与任何非 null 值不相等（不同），并且任何两个 null 值都被视为相等（不不同）。因此，结果将为 true 或 false，永远不会为 null。
This construct is similar to a <> row comparison, but it does not yield null for null inputs. Instead, any null value is considered unequal to (distinct from) any non-null value, and any two nulls are considered equal (not distinct). Thus the result will either be true or false, never null.
row_constructor IS NOT DISTINCT FROM row_constructor此构造类似于 = 行比较，但它不会对 null 输入产生 null。相反，任何 null 值都被认为与任何非 null 值不相等（不同），并且任何两个 null 值都被视为相等（不不同）。因此，结果将始终为 true 或 false，永远不会为 null。
This construct is similar to a = row comparison, but it does not yield null for null inputs. Instead, any null value is considered unequal to (distinct from) any non-null value, and any two nulls are considered equal (not distinct). Thus the result will always be either true or false, never null.
9.24.6. Composite Type Comparison #
record operator recordSQL 规范要求按行比较返回 NULL（如果结果依赖于比较两个 NULL 值或者 NULL 和非 NULL）。PostgreSQL 仅在比较两个行构造函数的结果（如 Section 9.24.5 中）或将行构造函数与子查询的输出进行比较时（如 Section 9.23 中）才会执行此操作。在其他比较两个复合类型值的环境中，两个 NULL 字段值被视为相等，NULL 被视为大于非 NULL。这样做对于复合类型的持续排序和索引行为是必需的。
The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors (as in Section 9.24.5) or comparing a row constructor to the output of a subquery (as in Section 9.23). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.
对每一方进行评估，并按行进行比较。当 operator 为 =、<>、<、⇐、> 或 >=，或具有类似于其中一个的语义时，允许进行复合类型比较。（具体来说，如果运算符是 B 树运算符类中的成员，或者是一个 B 树运算符类中 = 成员的否定者，则可以是一个行的比较运算符。）上述运算符的默认行为与行构造函数的 IS [ NOT ] DISTINCT FROM 相同（请参阅 Section 9.24.5）。
Each side is evaluated and they are compared row-wise. Composite type comparisons are allowed when the operator is =, <>, <, ⇐, > or >=, or has semantics similar to one of these. (To be specific, an operator can be a row comparison operator if it is a member of a B-tree operator class, or is the negator of the = member of a B-tree operator class.) The default behavior of the above operators is the same as for IS [ NOT ] DISTINCT FROM for row constructors (see Section 9.24.5).
为了支持匹配不具有默认 B 树运算符类的元素的行，定义了下列运算符用于复合类型比较：*=、*<>、*<、*⇐、*> 和 *>=。这些运算符比较两行的内部二进制表示。即使两行的比较与等式运算符为 true，两行也可能具有不同的二进制表示。这些比较运算符下的行的排序是确定的，但没有任何其他意义。这些运算符在内嵌视图中用于内部操作，并且可能对其他专门目的有用，例如复制和 B 树重复数据删除（请参阅 Section 67.4.3）。不过，它们并不打算一般用于编写查询。
To support matching of rows which include elements without a default B-tree operator class, the following operators are defined for composite type comparison: *=, *<>, *<, *⇐, *>, and *>=. These operators compare the internal binary representation of the two rows. Two rows might have a different binary representation even though comparisons of the two rows with the equality operator is true. The ordering of rows under these comparison operators is deterministic but not otherwise meaningful. These operators are used internally for materialized views and might be useful for other specialized purposes such as replication and B-Tree deduplication (see Section 67.4.3). They are not intended to be generally useful for writing queries, though.