集运算符

集运算符将多个查询块的中间结果组合成单个结果集。

通用语法

[ ( ] <query> [ ) ] { INTERSECT | { MINUS | EXCEPT } | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Copy

一般使用说明

  • 每个查询本身可以包含查询运算符，从而允许任意数量的查询表达式与集运算符相组合。

  • ORDER BY 和 LIMIT / FETCH 子句应用于集运算符的结果。

  • 使用这些运算符时：

    • 确保每个查询均选择相同数量的列。

    • 确保在来自不同源的行之间，每列的数据类型均保持一致。在 使用 UNION 运算符和转换不匹配的数据类型 部分中，一个示例说明了数据类型不匹配时的潜在问题和解决方案。

    • 通常，列的“含义”及数据类型应该匹配。以下内容不会产生预期结果：

      SELECT LastName, FirstName FROM employees
UNION ALL
SELECT FirstName, LastName FROM contractors;
      Copy

      使用星号指定表的所有列时，出错风险会增加，例如：

      SELECT * FROM table1
UNION ALL
SELECT * FROM table2;
      Copy

      如果表的列数相同，但列的顺序不同，查询结果有可能不正确。

    • 输出列的名称基于第一个查询的列名称。例如，请考虑以下查询：

      SELECT LastName, FirstName FROM employees
UNION ALL
SELECT FirstName, LastName FROM contractors;
      Copy

      此查询的行为与以下查询类似：

      SELECT LastName, FirstName FROM employees
UNION ALL
SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
      Copy

  • 集运算符的优先级与 ANSI 和 ISO SQL 标准相符：

    • UNION [ALL] 和 MINUS (EXCEPT) 运算符具有同等优先级。

    • INTERSECT 运算符的优先级高于 UNION [ALL] 和 MINUS (EXCEPT)。

    优先级相同的运算符按照从左到右的顺序进行处理。

    您可以使用括号来强制改变表达式的计算顺序。

    并非所有数据库供应商均遵循 ANSI/ISO 标准来确定集运算符的优先级。Snowflake 建议使用括号来指定计算顺序，尤其是在将代码从其他供应商移植到 Snowflake 时，或者是编写可能在其他数据库和 Snowflake 上执行的代码时。

示例的示例表

本主题中的示例使用以下示例表。两个表都有一个邮政编码列。一个表格记录了每个销售办事处的邮政编码，而另一个表格记录了每个客户的邮政编码。

CREATE OR REPLACE TABLE sales_office_zip_example(
  office_name VARCHAR,
  zip VARCHAR);

INSERT INTO sales_office_zip_example VALUES ('sales1', '94061');
INSERT INTO sales_office_zip_example VALUES ('sales2', '94070');
INSERT INTO sales_office_zip_example VALUES ('sales3', '98116');
INSERT INTO sales_office_zip_example VALUES ('sales4', '98005');

CREATE OR REPLACE TABLE customer_zip_example(
  customer VARCHAR,
  zip VARCHAR);

INSERT INTO customer_zip_example VALUES ('customer1', '94066');
INSERT INTO customer_zip_example VALUES ('customer2', '94061');
INSERT INTO customer_zip_example VALUES ('customer3', '98444');
INSERT INTO customer_zip_example VALUES ('customer4', '98005');
Copy

INTERSECT

返回一个查询的结果集中同样出现在另一个查询的结果集中的行，并消除重复项。

语法

SELECT ...
INTERSECT
SELECT ...
Copy

INTERSECT 运算符示例

查询 示例表 以查找同时拥有销售办事处和客户的邮政编码：

SELECT zip FROM sales_office_zip_example
INTERSECT
SELECT zip FROM customer_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 94061 |
| 98005 |
+-------+

MINUS、EXCEPT

返回第一个查询返回的行不是第二个查询也返回的行。

MINUS 和 EXCEPT 关键字具有相同含义，可以互换使用。

语法

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...
Copy

MINUS 运算符示例

查询 示例表 以查找在 sales_office_zip_example 表中却不在 customer_zip_example 表中的邮政编码：

SELECT zip FROM sales_office_zip_example
MINUS
SELECT zip FROM customer_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 98116 |
| 94070 |
+-------+

查询 示例表 以查找在 customer_zip_example 表中却不在 sales_office_zip_example 表中的邮政编码：

SELECT zip FROM customer_zip_example
MINUS
SELECT zip FROM sales_office_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 98444 |
| 94066 |
+-------+

UNION [ ALL ]

合并两个查询的结果集：

  • UNION 执行合并并消除重复项。

  • UNION ALL 执行合并，但不消除重复项。

默认值为 UNION（即消除重复项）。

语法

SELECT ...
UNION [ ALL ]
SELECT ...
Copy

UNION 运算符示例

以下示例使用 UNION 运算符。

使用 UNION 运算符组合两个查询的结果

使用 UNION 运算符组合 示例表 中两个查询的结果集：

SELECT office_name office_or_customer, zip FROM sales_office_zip_example
UNION
SELECT customer, zip FROM customer_zip_example
ORDER BY zip;
Copy
+--------------------+-------+
| OFFICE_OR_CUSTOMER | ZIP   |
|--------------------+-------|
| sales1             | 94061 |
| customer2          | 94061 |
| customer1          | 94066 |
| sales2             | 94070 |
| sales4             | 98005 |
| customer4          | 98005 |
| sales3             | 98116 |
| customer3          | 98444 |
+--------------------+-------+

使用 UNION 运算符和转换不匹配的数据类型

本示例演示了数据类型不匹配时使用 UNION 运算符的潜在问题，然后提供了解决方案。

首先创建表并插入一些数据：

CREATE OR REPLACE TABLE union_test1 (v VARCHAR);
CREATE OR REPLACE TABLE union_test2 (i INTEGER);

INSERT INTO union_test1 (v) VALUES ('Adams, Douglas');
INSERT INTO union_test2 (i) VALUES (42);
Copy

使用不同的数据类型（union_test1 中的 VARCHAR 值和 union_test2 中的 INTEGER 值）执行 UNION 操作：

SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;
Copy

此查询会返回错误：

100038 (22018): Numeric value 'Adams, Douglas' is not recognized

现在使用显式类型转换，将输入转换为兼容类型：

SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;
Copy
+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+
语言: 中文