SQL UNION 和 UNION ALL 有什么区别？

What is the difference between UNION and UNION ALL?

提问by Brian G

What is the difference between UNIONand UNION ALL?

UNION和 和有UNION ALL什么区别？

回答by Jim Harte

UNIONremoves duplicate records (where all columns in the results are the same), UNION ALLdoes not.

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

There is a performance hit when using UNIONinstead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

使用UNION代替 会降低性能UNION ALL，因为数据库服务器必须做额外的工作来删除重复的行，但通常您不想要重复的行（尤其是在开发报告时）。

UNION Example:

联合示例：

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

Result:

结果：

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

UNION ALL example:

联合所有示例：

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

Result:

结果：

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

回答by Bhaumik Patel

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

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

• UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

• UNION ALL does not remove duplicates, and it therefore faster than UNION.

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

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

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

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

Example: If we have two tables, 1) Employee and 2) Customer

示例：如果我们有两个表，1) Employee 和 2) Customer

1. Employee table data:

1. 员工表数据：

1. Customer table data:

2. 客户表数据：

1. UNION Example (It removes all duplicate records):

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

1. UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):

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

回答by mathewbutler

UNIONremoves duplicates, whereas UNION ALLdoes not.

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

In order to remove duplicates the result set must be sorted, and this mayhave an impact on the performance of the UNION, depending on the volume of data being sorted, and the settings of various RDBMS parameters ( For Oracle PGA_AGGREGATE_TARGETwith WORKAREA_SIZE_POLICY=AUTOor SORT_AREA_SIZEand SOR_AREA_RETAINED_SIZEif WORKAREA_SIZE_POLICY=MANUAL).

为了删除重复项，必须对结果集进行排序，这可能会对 UNION 的性能产生影响，具体取决于排序的数据量以及各种 RDBMS 参数的设置（对于 Oracle PGA_AGGREGATE_TARGETwith WORKAREA_SIZE_POLICY=AUTOor SORT_AREA_SIZEand SOR_AREA_RETAINED_SIZEif WORKAREA_SIZE_POLICY=MANUAL）。

Basically, the sort is faster if it can be carried out in memory, but the same caveat about the volume of data applies.

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

Of course, if you need data returned without duplicates then you mustuse UNION, depending on the source of your data.

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

I would have commented on the first post to qualify the "is much less performant" comment, but have insufficient reputation (points) to do so.

我会评论第一篇文章来限定“性能低得多”的评论，但没有足够的声誉（点数）来这样做。

回答by Michiel Overeem

In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.

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

回答by George Mauer

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.

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

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

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

回答by Ihor Vorotnov

You can avoid duplicates and still run much faster than UNION DISTINCT (which is actually same as UNION) by running query like this:

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

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

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

Notice the AND a!=Xpart. This is much faster then UNION.

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

回答by Peter Perhá?

Just to add my two cents to the discussion here: one could understand the UNIONoperator as a pure, SET-oriented UNION - e.g. set A={2,4,6,8}, set B={1,2,3,4}, A UNION B = {1,2,3,4,6,8}

只是在这里的讨论中加上我的两分钱：人们可以将UNION运算符理解为纯粹的面向 SET 的 UNION - 例如 set A={2,4,6,8}, set B={1,2,3,4 }, A UNION B = {1,2,3,4,6,8}

When dealing with sets, you would not want numbers 2 and 4 appearing twice, as an element either isor is notin a set.

当套打交道，你不希望号2和4出现两次，作为一个元素或者是或不是一组。

In the world of SQL, though, you might want to see all the elements from the two sets together in one "bag" {2,4,6,8,1,2,3,4}. And for this purpose T-SQL offers the operator UNION ALL.

但是，在 SQL 世界中，您可能希望将两个集合中的所有元素放在一个“袋子”{2,4,6,8,1,2,3,4} 中。为此，T-SQL 提供了运算符UNION ALL.

回答by DotNetGuy

UNION
The UNIONcommand is used to select related information from two tables, much like the JOINcommand. However, when using the UNIONcommand all selected columns need to be of the same data type. With UNION, only distinct values are selected.

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

UNION ALL
The UNION ALLcommand is equal to the UNIONcommand, except that UNION ALLselects all values.

UNION ALL
的UNION ALL命令等于UNION命令，不同之处在于UNION ALL选择的所有值。

The difference between Unionand Union allis that Union allwill not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

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

A UNIONstatement effectively does a SELECT DISTINCTon the results set. If you know that all the records returned are unique from your union, use UNION ALLinstead, it gives faster results.

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

回答by Jakub ?turc

Not sure that it matters which database

不确定哪个数据库很重要

UNIONand UNION ALLshould work on all SQL Servers.

UNION并且UNION ALL应该适用于所有 SQL Server。

You should avoid of unnecessary UNIONs they are huge performance leak. As a rule of thumb use UNION ALLif you are not sure which to use.

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

回答by DBA

UNION - results in distinctrecords

while

UNION ALL - results in all the records including duplicates.

UNION - 产生不同的记录，

而

UNION ALL - 产生包括重复在内的所有记录。

Both are blocking operators and hence I personally prefer using JOINS over Blocking Operators(UNION, INTERSECT, UNION ALL etc. ) anytime.

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

To illustrate why Union operation performs poorly in comparison to Union All checkout the following example.

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

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'

Following are results of UNION ALL and UNION operations.

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

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

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

Using UNION results in Distinct Sortoperations in the Execution Plan. Proof to prove this statement is shown below:

使用 UNION 会导致执行计划中的不同排序操作。证明此陈述的证明如下所示：