Postgresql 中文操作指南

7.4. Combining Queries (UNION, INTERSECT, EXCEPT) #

可以使用集合运算并集、交集和差集来组合两个查询的结果。语法如下：

The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

这里 query1 和 query2 是使用任何已在此点之前讨论过的特性的查询。

where query1 and query2 are queries that can use any of the features discussed up to this point.

UNION 有效地将 query2 的结果追加到 query1 的结果（尽管无法保证这是行实际返回的顺序）。此外，除非使用 UNION ALL，否则会以与 DISTINCT 相同的方式从其结果中消除重复行。

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

INTERSECT 返回 query1 和 query2 结果中同时存在的行。重复的行将被全部消除，但使用 INTERSECT ALL 除外。

INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used.

EXCEPT 返回 query1 结果中但不在 query2 结果中的行。（两个查询之间有时候称为 difference。）同样，也消除了重复行，但使用 EXCEPT ALL 除外。

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used.

为计算两个查询的并集、交集或差集，两个查询必须“并集兼容”，这意味着它们返回相同数量的列，且相应列具有 Section 10.5 中描述的兼容数据类型。

In order to calculate the union, intersection, or difference of two queries, the two queries must be “union compatible”, which means that they return the same number of columns and the corresponding columns have compatible data types, as described in Section 10.5.

集合运算可以组合起来，例如：

Set operations can be combined, for example

query1 UNION query2 EXCEPT query3

等效于

which is equivalent to

(query1 UNION query2) EXCEPT query3

如这里所示，你可以使用括号来控制运算顺序。如果不用括号，UNION 和 EXCEPT 将从左到右关联，但 INTERSECT 绑定要比这两个运算符更紧密。因此

As shown here, you can use parentheses to control the order of evaluation. Without parentheses, UNION and EXCEPT associate left-to-right, but INTERSECT binds more tightly than those two operators. Thus

query1 UNION query2 INTERSECT query3

表示

means

query1 UNION (query2 INTERSECT query3)

你也可以用括号将单独的 query 括起来。这一点很重要，如果 query 需要使用后续部分所讨论的任何从句时，如 LIMIT。如果不带括号，你将得到一个语法错误，或者该从句将理解为应用于集合运算的输出，而不是它的输入之一。例如：

You can also surround an individual query with parentheses. This is important if the query needs to use any of the clauses discussed in following sections, such as LIMIT. Without parentheses, you’ll get a syntax error, or else the clause will be understood as applying to the output of the set operation rather than one of its inputs. For example,

SELECT a FROM b UNION SELECT x FROM y LIMIT 10

被接受，但它表示：

is accepted, but it means

(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

而不是

not

SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)