首页 / 知识

关于SQL：union和union all有什么区别？

2023-04-14 13:49:00

What is the difference between UNION and UNION ALL?

UNION和UNION ALL有什么区别？

UNION删除重复记录(结果中的所有列都相同)，UNION ALL不删除。

在使用UNION而不是UNION ALL时，性能会受到影响，因为数据库服务器必须做额外的工作来删除重复的行，但通常不需要重复的行(尤其是在开发报表时)。

联合实例：

1	SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

结果：

1
2
3
4
5
6

+-----+
| bar |
+-----+
| foo |
+-----+
1 ROW IN SET (0.00 sec)

Union All示例：

1	SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

结果：

1
2
3
4
5
6
7

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 ROWS IN SET (0.00 sec)

union和union都将两个不同的sql的结果连接起来。他们处理重复的方式不同。

union对结果集执行distinct，从而消除任何重复的行。
union all不会删除重复项，因此比union快。

Note: While using this commands all selected columns need to be of the same data type.

示例：如果我们有两个表，1)员工和2)客户

员工表数据：

enter image description here

客户表数据：

enter image description here

联合示例(它删除所有重复记录)：

enter image description here

union all示例(它只是连接记录，而不是消除重复项，因此比union更快)：

enter image description here

UNION删除重复项，而UNION ALL不删除重复项。

为了删除重复项，必须对结果集进行排序，这可能会影响联合的性能，具体取决于要排序的数据量以及各种RDBMS参数的设置(对于Oracle PGA_AGGREGATE_TARGET，如果WORKAREA_SIZE_POLICY=AUTO或SORT_AREA_SIZE和SOR_AREA_RETAINED_SIZE，如果WORKAREA_SIZE_POLICY=MANUAL)。

基本上，如果可以在内存中进行排序，那么排序速度会更快，但是同样的关于数据量的警告也适用。

当然，如果需要返回不带重复项的数据，则必须使用union，具体取决于数据的来源。

我本来会在第一篇文章上发表评论来限定"表现差得多"的评论，但没有足够的声誉(分数)来这样做。

在Oracle中：union不支持blob(或clob)列类型，union all支持。

The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.

来自http://zengin.wordpress.com/2007/07/31/union-vs-union-all/

通过运行以下查询，可以避免重复，并且运行速度仍然比union distinct(实际上与union相同)快得多：

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

注意AND a!=X部分。这比联合要快得多。

我在这里的讨论中加上两点：我们可以把UNION操作符理解为一个纯的、面向集的联合，例如，set a=2,4,6,8，set b=1,2,3,4，a union b=1,2,3,4,6,8

在处理集合时，您不希望数字2和4出现两次，因为元素要么在集合中，要么不在集合中。

不过，在SQL世界中，您可能希望在一个"包"2、4、6、8、1、2、3、4中看到两个集合中的所有元素。为此，T-SQL提供了操作符UNION ALL。

联合UNION命令用于从两个表中选择相关信息，很像JOIN命令。但是，当使用UNION命令时，所有选定的列都需要具有相同的数据类型。使用UNION时，只选择不同的值。

联合所有UNION ALL命令等于UNION命令，但UNION ALL选择所有值除外。

UNION和UNION ALL的区别在于UNION ALL不会消除重复的行，相反，它只是从所有符合查询具体信息的表中提取所有行，并将它们组合到一个表中。

UNION语句有效地对结果集执行SELECT DISTINCT。如果您知道从联合返回的所有记录都是唯一的，请使用UNION ALL，它将提供更快的结果。

Not sure that it matters which database

UNION和UNION ALL应该在所有SQL服务器上工作。

您应该避免不必要的UNION，它们是巨大的性能泄漏。根据经验，如果您不确定要使用哪个，请使用UNION ALL。

联合-导致不同的记录，而union all-生成包括重复项在内的所有记录。

两者都是阻塞操作符，因此我个人更喜欢随时使用连接而不是阻塞操作符(union、intersect、union all等)。

为了说明与union all签出相比，union操作的性能较差的原因，请参阅以下示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE #T1 (DATA VARCHAR(10))

INSERT INTO #T1
SELECT 'abc'
UNION ALL
SELECT 'bcd'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'def'
UNION ALL
SELECT 'efg'

CREATE TABLE #T2 (DATA VARCHAR(10))

INSERT INTO #T2
SELECT 'abc'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'efg'

enter image description here

以下是Union All和Union操作的结果。

enter image description here

union语句有效地对结果集执行select distinct。如果您知道从联合返回的所有记录都是唯一的，请使用union all，它将提供更快的结果。

使用union会在执行计划中产生不同的排序操作。证明此声明的证据如下：

enter image description here

union用于从两个表中选择不同的值其中，as union all用于选择所有值，包括表中的重复项

(来自Microsoft SQL Server联机丛书)

联合[全部]

Specifies that multiple result sets are to be combined and returned as a single result set.

所有

Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

如果对结果应用类似于DISTINCT的重复行，则UNION将花费太长时间。

1
2
3

SELECT * FROM Table1
UNION
SELECT * FROM Table2

相当于：

1
2
3
4

SELECT DISTINCT * FROM (
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2) DT

A side effect of applying DISTINCT over results is a sorting operation on results.

UNION ALL结果将显示为结果的任意顺序，但UNION结果将显示为应用于结果的ORDER BY 1, 2, 3, ..., n (n = column number of Tables)结果。当没有任何重复的行时，您可以看到这个副作用。

用维恩图很好地理解。

这是指向源的链接。有一个很好的描述。

enter image description here

我加了一个例子，

Union，它与distinct->合并的速度较慢，因为它需要比较(在Oracle SQL Developer中，选择查询，按F10查看成本分析)。

union all，它在合并时没有使用distinct->更快。

1
2
3

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;

和

1
2
3

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;

UNION将两个结构兼容表的内容合并到一个组合表中。

差异：

UNION和UNION ALL的区别在于UNION will省略了重复记录，而UNION ALL将包含重复记录。

UNION结果集按升序排序，而UNION ALL结果集不排序

UNION对其结果集执行DISTINCT，以便消除任何重复行。而UNION ALL不会删除重复项，因此比UNION快。

注：由于UNION要求服务器做额外的删除工作，所以UNION ALL的性能通常比UNION好。因此，在确定不会有任何重复的情况下，或者在没有重复的情况下，出于性能原因，建议使用UNION ALL。

假设您有两张桌子，老师和学生

两者都有4列，名称不同，如下所示

1	Teacher - ID(INT), Name(VARCHAR(50)), Address(VARCHAR(50)), PositionID(VARCHAR(50))

enter image description here

1	Student- ID(INT), Name(VARCHAR(50)), Email(VARCHAR(50)), PositionID(INT)

enter image description here

可以对列数相同的两个表应用union或union all。但它们有不同的名称或数据类型。

当对2个表应用UNION操作时，它忽略所有重复的条目(表中行的所有列值与另一个表相同)。这样地

1
2
3

SELECT * FROM Student
UNION
SELECT * FROM Teacher

结果是

enter image description here

在2个表上应用UNION ALL操作时，返回所有重复的条目(如果两个表中一行的任何列值之间存在差异)。这样地

1
2
3

SELECT * FROM Student
UNION ALL
SELECT * FROM Teacher

产量 enter image description here

性能：

显然，union all的性能比union好，因为它们执行额外的任务来删除重复的值。您可以通过在mssql上按ctrl+l检查从执行估计时间开始的时间。

还有一件事我想补充一下-

联合：结果集按升序排序。

union all:-结果集未排序。附加了两个查询输出。

简单来说，union和union all的区别在于union将省略重复记录，而union all将包含重复记录。

SQL中union与union all的区别

SQL中的union是什么？

union运算符用于组合两个或多个数据集的结果集。

1
2
3

Each SELECT statement WITHIN UNION must have the same NUMBER OF COLUMNS
The COLUMNS must also have SIMILAR DATA types
The COLUMNS IN each SELECT statement must also be IN the same ORDER

并集与并集

union和union都用于组合两个或多个查询结果。

union命令从两个表中选择不同的和相关的信息，这将消除重复的行。

另一方面，unionall命令从两个表中选择所有值，显示所有行。

如果没有ORDER BY，UNION ALL可以按原样返回行，而UNION会使您等到查询结束后，才立即给出整个结果集。这可以在超时情况下起到不同的作用——UNION ALL保持连接的活动，就像以前一样。

因此，如果您有一个超时问题，并且没有排序，而副本也不是问题，那么UNION ALL可能会很有帮助。

重要！Oracle和MySQL之间的区别：假设T1 T2之间没有重复的行，但它们各自有重复的行。示例：T1从2017年开始销售，T2从2018年开始销售。

1
2
3
4
5