UNION和UNION ALL有什么区别？

UNION和UNION ALL有什么区别？

1523495 次浏览
小开

UNION和UNION ALL之间的基本区别是联合操作从结果集中消除重复行，但联合all在加入后返回所有行。

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

小开
最佳答案

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

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

要识别重复项，记录必须是可比较的类型以及兼容的类型。这将取决于SQL系统。例如，系统可能会截断所有长文本字段以使短文本字段进行比较（MS Jet），或者可能拒绝比较二进制字段（ORACLE）

UNION示例：

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

结果：

+-----+| bar |+-----+| foo |+-----+1 row in set (0.00 sec)

UNION ALL示例：

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

结果：

+-----+| bar |+-----+| foo || foo |+-----+2 rows in set (0.00 sec)
小开

不确定哪个数据库很重要

UNION和UNION ALL应该适用于所有SQL服务器。

你应该避免不必要的UNION，它们是巨大的性能泄漏。作为一个经验法则，如果你不确定使用哪个，请使用UNION ALL。

小开

在ORACLE： UNION中不支持BLOB（或CLOB）列类型，UNION ALL支持。

小开

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

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

基本上，如果可以在内存中执行，排序会更快，但关于数据量的相同警告适用。

当然，如果您需要返回没有重复的数据，那么您必须使用UNION，具体取决于您的数据来源。

我会对第一篇文章发表评论，以限定“性能差得多”的评论，但没有足够的声誉（积分）这样做。

小开

您可以通过运行如下查询来避免重复，并且仍然比UNION DISTINCT（实际上与UNION相同）运行得更快：

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

注意AND a!=X部分。这比UNION快得多。

小开

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

联合所有
UNION ALL命令等于UNION命令，只是UNION ALL选择所有值。

Union和Union all之间的区别在于Union all不会消除重复行，相反，它只是从所有符合查询细节的表中提取所有行并将它们组合成一个表。

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

小开

UNION和UNION ALL都连接两个不同SQL的结果。它们处理重复项的方式不同。

  • UNION对结果集执行DISTINCT，消除任何重复行。

  • UNION ALL不会删除重复项，因此比UNION更快。

备注：使用此命令时，所有选定的列都需要具有相同的数据类型。

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

  1. 员工表数据：

输入图片描述

  1. 客户表数据：

输入图片描述

  1. UNION示例（它删除所有重复记录）：

输入图片描述

  1. UNION ALL示例（它只是连接记录，而不是消除重复项，所以它比UNION快）：

输入图片描述

小开

只是为了在这里的讨论中增加我的两分钱：人们可以将UNION运算符理解为一个纯粹的、面向SET的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 ALL不会删除重复记录。但是需要检查要处理的大量数据，并且列和数据类型必须相同。

由于Union内部使用“不同”行为来选择行，因此在时间和性能方面成本更高。喜欢

select project_id from t_projectunionselect project_id from t_project_contact

这给了我2020年的记录

另一方面

select project_id from t_projectunion allselect project_id from t_project_contact

给我超过17402行

在优先权方面，两者具有相同的优先权。

小开

（来自MicrosoftSQLServer Book Online）

工会[全体]

指定要组合多个结果集并作为单个结果集返回。

所有

将所有行合并到结果中。这包括重复行。如果未指定，则删除重复行。

UNION将花费太长时间，因为像DISTINCT这样的重复行查找应用于结果。

SELECT * FROM Table1UNIONSELECT * FROM Table2

相当于：

SELECT DISTINCT * FROM (SELECT * FROM Table1UNION ALLSELECT * FROM Table2) DT

在结果上应用DISTINCT的副作用是结果上的排序操作。

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

小开

还有一件事我想补充一下

联盟：-结果集按升序排序。

联盟所有：-结果集未排序。两个查询输出只是附加。

小开

如果没有ORDER BY，UNION ALL可能会自动返回行，而UNION会让您等到查询的最后才立即向您提供整个结果集。这可以在超时情况下产生影响-UNION ALL可以让连接保持活动状态。

因此，如果您有超时问题，并且没有排序，并且重复不是问题，那么UNION ALL可能会很有帮助。

小开

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

  • 区别：

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

Union结果集按升序排序，而UNION ALL结果集未排序

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

注意：#0的性能通常优于#1，因为#1要求服务器做删除任何重复项的额外工作。因此，在确定不会有任何重复项的情况下，或者有重复项不是问题的情况下，出于性能原因，建议使用#0。

小开

假设你有两个表教师和学生

4列不同的名称是这样的

Teacher - ID(int), Name(varchar(50)), Address(varchar(50)), PositionID(varchar(50))

在此处输入图片描述

Student- ID(int), Name(varchar(50)), Email(varchar(50)), PositionID(int)

在此处输入图片描述

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

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

SELECT * FROM StudentUNIONSELECT * FROM Teacher

结果将是

在此处输入图片描述

当您对2个表应用UNION ALL操作时，它返回所有具有重复的条目（如果2个表中一行的任何列值之间存在任何差异）。像这样

SELECT * FROM StudentUNION ALLSELECT * FROM Teacher

输出输入图片描述

性能：

显然联合所有的性能比联盟好，因为他们做了额外的任务来删除重复的值。您可以通过按MSSQL的ctrl+l从预计执行时间中检查

小开

UNION-结果为不同条记录

而

UNION ALL-导致所有记录，包括重复记录。

两者都是阻塞运算符，因此我个人更喜欢在任何时候使用JOINS而不是阻塞运算符（UNION、INTERSECT、UNION ALL等）。

为了说明为什么Union操作与Union All相比表现不佳，请查看以下示例。

CREATE TABLE #T1 (data VARCHAR(10))
INSERT INTO #T1SELECT 'abc'UNION ALLSELECT 'bcd'UNION ALLSELECT 'cde'UNION ALLSELECT 'def'UNION ALLSELECT 'efg'

CREATE TABLE #T2 (data VARCHAR(10))
INSERT INTO #T2SELECT 'abc'UNION ALLSELECT 'cde'UNION ALLSELECT 'efg'

在此处输入图片描述

以下是UNION ALL和UNION操作的结果。

在此处输入图片描述

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

使用UNION会导致执行计划中的独特排序操作。证明此语句的证据如下所示：

在此处输入图片描述

小开

我举个例子，

联盟，它与不同的合并->速度较慢，因为它需要比较（在OracleSQL开发人员中，选择查询，按F10查看成本分析）。

联合所有，它正在合并而没有明显的-->更快。

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

和

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dualUNION ALLSELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;
小开

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

SELECT T1.YEAR, T1.PRODUCT FROM T1
UNION ALL
SELECT T2.YEAR, T2.PRODUCT FROM T2

在ORACLE UNION ALL中，从两个表中获取所有行。MySQL中也会发生同样的事情。

但是：

SELECT T1.YEAR, T1.PRODUCT FROM T1
UNION
SELECT T2.YEAR, T2.PRODUCT FROM T2

在甲骨文中，UNION从两个表中获取所有行，因为t1和t2之间没有重复值。另一方面，在mysql中，结果集的行会更少，因为表t1和表t2中会有重复行！

小开

UNION ALL也适用于更多数据类型。例如，当尝试合并空间数据类型时。例如： 

select a.SHAPE from tableA aunionselect b.SHAPE from tableB b

会把

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

union all不会。


提交答案