Snowpark Migration Accelerator： Union

描述

将两个子查询合并为一个查询。Databricks SQL 提供三种集运算符，可用于组合查询：

  • EXCEPT - Retrieves all rows from the first query that do not appear in the second query

  • INTERSECT - Returns only the rows that appear in both queries

  • UNION - Combines the results of two or more queries into a single result set

Databricks SQL Language Reference UNION (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-setops.html)

Set operators enable you to combine multiple queries into a single result. For more details, see Snowflake SQL Language Reference UNION.

语法

subquery1 { { UNION [ ALL | DISTINCT ] |
              INTERSECT [ ALL | DISTINCT ] |
              EXCEPT [ ALL | DISTINCT ] } subquery2 } [...] }

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

示例源模式

设置数据

Databricks

CREATE TEMPORARY VIEW number1(c) AS VALUES (3), (1), (2), (2), (3), (4);

CREATE TEMPORARY VIEW number2(c) AS VALUES (5), (1), (1), (2);

Snowflake

CREATE TEMPORARY TABLE number1(c int);
INSERT INTO number1 VALUES (3), (1), (2), (2), (3), (4);

CREATE TEMPORARY TABLE number2(c int);
INSERT INTO number2 VALUES (5), (1), (1), (2);

模式代码

Databricks

-- EXCEPT (MINUS) Operator:
SELECT c FROM number1 EXCEPT SELECT c FROM number2;

SELECT c FROM number1 MINUS SELECT c FROM number2;

-- EXCEPT ALL (MINUS ALL) Operator:
SELECT c FROM number1 EXCEPT ALL (SELECT c FROM number2);

SELECT c FROM number1 MINUS ALL (SELECT c FROM number2);

-- INTERSECT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT DISTINCT Operator:
(SELECT c FROM number1) INTERSECT DISTINCT (SELECT c FROM number2);

-- INTERSECT ALL Operator:
(SELECT c FROM number1) INTERSECT ALL (SELECT c FROM number2);

-- UNION Operator:
(SELECT c FROM number1) UNION (SELECT c FROM number2);

-- UNION DISTINCT Operator:
(SELECT c FROM number1) UNION DISTINCT (SELECT c FROM number2);

-- UNION ALL Operator:
SELECT c FROM number1 UNION ALL (SELECT c FROM number2);

EXCEPT (MINUS) 运算符： EXCEPT 运算符，也称为 MINUS，用于移除在第二个查询的结果集中出现的第一个查询中的行。它只返回在第一个查询中存在但在第二个查询中不存在的唯一行。

c

3

4

EXCEPT ALL (MINUS ALL) 运算符：移除重复记录

c

3

3

4

INTERSECT 运算符： 只返回在两个结果集中出现的行，消除重复行。它比较两个或多个 SELECT 语句的结果，并只返回匹配的记录。仅返回出现在两个结果集中的行，消除重复行。

c

1

2

INTERSECT DISTINCT 运算符： 只返回出现在两个结果集中的唯一行，消除任何重复行。仅返回两个查询中出现的唯一行，从而消除结果集中的任何重复行。

c

1

2

INTERSECT ALL 运算符： 返回来自多个查询的所有匹配行，包括重复行。与删除重复项的标准 INTERSECT 运算符不同，INTERSECTALL 在最终结果集中保留重复行。返回两个结果集中出现的所有行，包括重复行。与删除重复行的 INTERSECT 不同，INTERSECTALL 会根据重复行在两个集合中的频率来保留重复行。

c

1

2

2

UNION 运算符： UNION 运算符将两个或多个 SELECT 语句的结果合并到一个结果集中。默认情况下，它会从合并的结果集中移除重复行。UNION 运算符将两个或多个 SELECT 语句的结果合并到一个结果集中。它从合并的结果中移除重复行。

c

1

3

5

4

2

** UNION DISTINCT 运算符：** UNION DISTINCT 运算符合并两个或多个结果集，并从最终输出中移除所有重复的行。它只返回所有组合查询中的唯一行。UNION DISTINCT 运算符合并来自两个或多个查询的行，同时从最终结果集中移除任何重复行。

c

1

3

5

4

2

UNION ALL 运算符： UNION ALL 运算符在不移除重复记录的情况下合并来自两个或多个查询的行。与 UNION 运算符不同，UNION ALL 保留所有行，包括重复行，这使得其执行速度更快，因为它不需要执行重复检查。此运算符合并了两个或多个 SELECT 语句的结果，并包括重复行在内的所有行。与删除重复行的 UNION 不同，UNIONALL 保留所有SELECT 语句中的所有行。

c

3

1

2

2

3

4

5

1

1

2

Snowflake

-- EXCEPT (MINUS) Operator
SELECT c FROM number1 EXCEPT SELECT c FROM number2;

SELECT c FROM number1 MINUS SELECT c FROM number2;

-- EXCEPT ALL (MINUS ALL) Operator:
SELECT number1.c FROM number1
LEFT JOIN number2
    ON number1.c = number2.c
WHERE number2.c IS NULL;
-- ** MSC-WARMING - MSC-S000# - EXCEPT ALL IS TRANSFORMED TO A LEFT JOIN. **

SELECT number1.c FROM number1
LEFT JOIN number2
    ON number1.c = number2.c
WHERE number2.c IS NULL;
-- ** MSC-WARMING - MSC-S000# - MINUS ALL IS TRANSFORMED TO A LEFT JOIN. **

-- INTERSECT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT DISTINCT Operator:
(SELECT c FROM number1) INTERSECT (SELECT c FROM number2);

-- INTERSECT ALL Operator:
SELECT DISTINCT number1.c FROM number1
INNER JOIN number2
    ON number1.c = number2.c;
-- ** MSC-WARMING - MSC-S000# - INTERSECT ALL IS TRANSFORMED TO A INNER JOIN. **

-- UNION Operator:
(SELECT c FROM number1) UNION (SELECT c FROM number2);

-- UNION DISTINCT Operator:
(SELECT c FROM number1) UNION DISTINCT (SELECT c FROM number2);

-- UNION ALL Operator:
SELECT c FROM number1 UNION ALL (SELECT c FROM number2);

EXCEPT (MINUS) 运算符：移除重复记录

EXCEPT 运算符（也称为 MINUS）比较两个查询，并只返回第一个查询中出现但在第二个查询中未出现的唯一记录。它消除结果集中的重复行。

c

3

4

EXCEPT ALL (MINUS ALL) 运算符：移除重复行

c

3

3

4

INTERSECT 运算符：

c

1

2

INTERSECT DISTINCT 运算符：

c

1

2

INTERSECT ALL 运算符：

c

1

2

2

UNION 运算符：

c

1

3

5

4

2

UNION DISTINCT 运算符：

c

1

3

5

4

2

UNION ALL 运算符：

c

3

1

2

2

3

4

5

1

1

2

已知问题

无相关 EWIs