SQL Server中的UNION与UNION ALL

问题

有时需要将来自多个表或视图的数据合并到一个综合的数据集中。这可能是针对同一数据库中具有相似数据的表，或者可能需要跨数据库甚至跨服务器组合相似数据。

在本文中，我们将介绍如何使用UNION与UNION ALL命令以及它们之间的区别。

解

在SQL Server中，您可以使用UNION或UNION ALL运算符将多个数据集组合为一个综合数据集。它们的工作方式以及返回的最终结果集有很大的不同，但是基本上，这些命令将具有相似结构的多个数据集合并到一个组合的数据集中。

 

UNION

此操作将使您可以将多个数据集合并为一个数据集，并删除所有存在的重复项。基本上，它在结果集中的所有列上执行DISTINCT操作。 

UNION ALL

再次执行此操作，您可以将多个数据集连接到一个数据集中，但是不会删除任何重复的行。因为这不会删除重复的行，所以此过程更快，但是如果您不希望重复的记录，则需要使用UNION运算符。

UNION数据规则

• 每个查询的列数必须相同
• 每列必须具有兼容的数据类型
• 最终结果集的列名取自第一个查询
• ORDER BY和COMPUTE子句只能针对整个结果集发出，而不能在每个单独的结果集中发出
• GROUP BY和HAVING子句只能为每个单独的结果集发出，而不能为整体结果集发出

提示：如果所有查询中的列都不完全相同，请使用默认值或NULL值，例如：

SELECT firstName, lastName, company FROM businessContacts
UNION
SELECT firstName, lastName, NULL FROM nonBusinessContacts
or
SELECT firstName, lastName, createDate FROM businessContacts
UNION ALL
SELECT firstName, lastName, getdate() FROM nonBusinessContacts

UNION vs. UNION ALL示例

让我们看一下这些命令如何工作以及它们如何不同的一些简单示例。正如您将看到的，最终结果集将有所不同，但是有关SQL Server如何实际完成该过程的信息有些有趣。

UNION ALL

在第一个示例中，我们对AdventureWorks数据库中的Employee表使用UNION ALL运算符。这可能不是您要执行的操作，但这有助于说明这两个运算符的区别。

表dbo.Employee中有290行。

SELECT * FROM HumanResources.Employee
UNION ALL
SELECT * FROM HumanResources.Employee
UNION ALL
SELECT * FROM HumanResources.Employee

　　

运行此查询时，结果集将包含870行。这是290行返回了3次。只是将一个数据集放到另一个数据集之上。

这是此查询的执行计划。我们可以看到该表被查询了3次，并且SQL Server执行了“连接”步骤来连接所有数据。

UNION

在下一个示例中，我们再次从AdventureWorks数据库中对Employee表使用UNION运算符。

SELECT * FROM HumanResources.Employee
UNION
SELECT * FROM HumanResources.Employee
UNION
SELECT * FROM HumanResources.Employee

运行此查询时，结果集有290行。即使我们将数据合并了三遍，UNION运算符也删除了重复的记录，因此仅返回了290个唯一行。

这是此查询的执行计划。我们可以看到SQL Server首先查询了两个表，然后执行了合并联接操作以合并前两个表，然后又执行了另一个合并联接并查询了查询中的第三个表。因此，我们可以看到，与UNION ALL相比，要获得此结果集还需要执行更多的工作。

在聚集索引列上排序的UNION vs.UNION ALL示例

如果我们更进一步，并使用“聚簇索引”列对数据进行SORT，我们将获得这些执行计划。从中我们可以看到，尽管最终结果集仍然包含UNION ALL的870行和UNION ALL的290行，但SQL Server使用的执行计划对于这些操作中的每一个都是相同的。

 

 

在聚集索引列上排序的UNION ALL执行计划

对聚集索引列进行排序的UNION执行计划

在非索引列上排序的UNION vs.UNION ALL示例

这是另一个做同样事情的例子，但是这次是对非索引列进行SORT。如您所见，这两个查询的执行计划再次相同，但是这次不是使用MERGE JOIN，而是使用了CONCATENATION和SORT操作。

UNION ALL执行计划，排序时带有非索引列

具有非索引列排序的UNION执行计划