SQL Server中SQL Union vs Union All

最新推荐文章于 2024-05-16 16:25:12 发布

culuo4781

最新推荐文章于 2024-05-16 16:25:12 发布

阅读量332

点赞数

文章标签：数据库 mysql java sql python

原文链接：https://www.sqlshack.com/sql-union-vs-union-all-in-sql-server/

版权

This article explains to the SQL Union and vs Union All operators in SQL Server. We will also explore the difference between these two operators along with various use cases.

本文向SQL Union解释了SQL Server中SQL Union和vs Union All。我们还将探讨这两种运算符之间的区别以及各种用例。

SQL Union操作员概述 (SQL Union Operator Overview)

In the relational database, we stored data into SQL tables. Sometimes we need to Select data from multiple tables and combine result set of all Select statements. We use the SQL Union operator to combine two or more Select statement result set.

在关系数据库中，我们将数据存储到SQL表中。有时我们需要从多个表中选择数据，并合并所有Select语句的结果集。我们使用SQL Union运算符组合两个或多个Select语句结果集。

SQL Union运算符的语法 (The syntax for the SQL Union operator)

SELECT column1, Column2 ...Column (N) FROM tableA
UNION
SELECT column1, Column2 ...Column (N) FROM tableB;

We need to take care of following points to write a query with the SQL Union Operator.

我们需要注意以下几点，以便使用SQL Union Operator编写查询。

Both the Select statement must have the same number of columns
两个Select语句的列数必须相同
Columns in both the Select statement must have compatible data types
两个Select语句中的列都必须具有兼容的数据类型
Column Order must also match in both the Select statement
在两个Select语句中，列顺序也必须匹配
Group By and Group By和Having clause with each Select statement. It is not possible to use them with the result set Haveing子句。无法将它们与结果集一起使用
Order By clause with individual Select statement. We can use it with result set generated from the Union of both Select statements Order By子句。我们可以将其与从两个Select语句的并集生成的结果集一起使用

In the following screenshot, we can understand the SQL UNION operator using a Venn diagram.

在下面的屏幕截图中，我们可以使用维恩图理解SQL UNION运算符。

Table A having values 1,2,3,4
具有值1,2,3,4的表A

CREATE TABLE TableA(
    ID INT
);
 Go
INSERT INTO TableA
VALUES(1),(2),(3),(4);

Table B having values 3,4,5,6
表B的值为3、4、5、6

CREATE TABLE TableB(
    ID INT
);
 Go
INSERT INTO TableB
VALUES(3),(4),(5),(6);

If we use SQL Union operator between these two tables, we get the following output.

如果在这两个表之间使用SQL Union运算符，则会得到以下输出。

SELECT ID
  FROM TableA
UNION
SELECT ID
  FROM TableB;

Output: 1, 2, 3,4,5,6

输出：1、2、3、4、5、6

SQL Union vs Union All - SQL Union operator

In my example, TableA and TableB both contain value 3 and 4. In the output, we do not get duplicate values. We get only one row for each duplicate value. It performs a DISTINCT operation across all columns in the result set.

在我的示例中，TableA和TableB都包含值3和4。在输出中，我们没有得到重复的值。每个重复值仅获得一行。它对结果集中的所有列执行DISTINCT操作。

Let look at this with another example. For this example, I created two tables Employee_F and Employee_M in sample database AdventureWorks2017 database.

让我们再看另一个例子。对于此示例，我在示例数据库AdventureWorks2017数据库中创建了两个表Employee_F和Employee_M 。

Execute following script for Employee_F table

对Employee_F表执行以下脚本

SELECT TOP 5 [NationalIDNumber], 
              [LoginID], 
              [JobTitle], 
              [BirthDate], 
              [MaritalStatus], 
              [Gender]
INTO [AdventureWorks2017].[HumanResources].[Employee_F]
FROM [AdventureWorks2017].[HumanResources].[Employee]
WHERE MaritalStatus = 'S';

Execute following script for Employee_M table

对Employee_M表执行以下脚本

SELECT TOP 5 [NationalIDNumber], 
             [LoginID], 
             [JobTitle], 
             [BirthDate], 
             [MaritalStatus], 
             [Gender]
INTO [AdventureWorks2017].[HumanResources].[Employee_M]
FROM [AdventureWorks2017].[HumanResources].[Employee]
WHERE MaritalStatus = 'M';

Both the tables do not contains any duplicate rows in each other tables. Let us execute following UNION statement.

这两个表在彼此的表中都不包含任何重复的行。让我们执行以下UNION语句。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F];UNION 
SELECT [NationalIDNumber]
      ,[LoginID]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      FROM [AdventureWorks2017].[HumanResources].[Employee_Temp]

Both the tables do not have duplicate rows. Therefore, we get all records from both tables in the output of SQL Union operator. It contains ten records in the output.

两个表都没有重复的行。因此，我们从SQL Union运算符的输出中从两个表中获取所有记录。它在输出中包含十个记录。

SQL Union vs Union All - Example of SQL Union operator

Let us create another table that contains duplicate rows from both the tables.

让我们创建另一个表，其中包含两个表中的重复行。

SELECT TOP 5 [NationalIDNumber], 
             [LoginID], 
             [JobTitle], 
             [BirthDate], 
             [MaritalStatus], 
             [Gender]
INTO [AdventureWorks2017].[HumanResources].[Employee_All]
FROM [AdventureWorks2017].[HumanResources].[Employee];

Now, we will use the SQL UNION operator between three tables. We should still get ten records because [Employee_All] contains records that already exist in Employee_M and Employee_F table.

现在，我们将在三个表之间使用SQL UNION运算符。我们仍然应该获得十条记录，因为[Employee_All]包含Employee_M和Employee_F表中已经存在的记录。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F]
UNION
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM
[AdventureWorks2017].[HumanResources].[Employee_All]

In the following image, you can see a UNION of these three tables do not contain any duplicate values.

在下图中，您可以看到这三个表的UNION不包含任何重复值。

SQL Union All运算符概述 (SQL Union All Operator Overview)

The SQL Union All operator combines the result of two or more Select statement similar to a SQL Union operator with a difference. The only difference is that it does not remove any duplicate rows from the output of the Select statement.

SQL Union All运算符将两个或多个与SQL Union运算符相似的Select语句的结果组合在一起，但有所不同。唯一的区别是它不会从Select语句的输出中删除任何重复的行。

SQL Union All运算符的语法 (The syntax for SQL Union All operator)

SELECT column1, Column2 ...Column (N) FROM tableA
Union All
SELECT column1, Column2 ...Column (N) FROM tableB;

Let us rerun the previous examples with SQL Union All operator.

让我们使用SQL Union All运算符重新运行前面的示例。

SELECT ID
  FROM TableA
UNION All
SELECT ID
  FROM TableB;

In the following image, you can see Output of both SQL Union vs Union All operators. SQL Union All return output of both Select statements. It does not remove any overlapping rows.

在下图中，您可以看到SQL Union和Union All运算符的输出。 SQL Union All返回两个Select语句的输出。它不会删除任何重叠的行。

If the tables do not have any overlapping rows, SQL Union All output is similar to SQL Union operator.

如果表没有任何重叠的行，则SQL Union All的输出类似于SQL Union运算符。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION All
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F]

We can see following output of SQL Union All output of Employee_M and Employee_F tables.

我们可以看到Employee_M和Employee_F表SQL Union All输出。

Now, rerun the query with three tables Employee_M and Employee_F and Employee_All tables. We got 10 records in output of SQL Union between these three tables. Each table contains 5 records. We should get 15 rows in the output of Union All operator on these tables.

现在，使用三个表Employee_M和Employee_F和Employee_All表重新运行查询。这三个表之间SQL Union输出中有10条记录。每个表包含5条记录。在这些表上的Union All运算符的输出中，我们应该获得15行。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION All
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM
[AdventureWorks2017].[HumanResources].[Employee_All]

SQL Union与Union All运算符 (SQL Union Vs Union All Operator)

Union	Union All
It combines the result set from multiple tables with eliminating the duplicate records	It combines the result set from multiple tables without eliminating the duplicate records
It performs a distinct on the result set.	It does not perform distinct on the result set
We need to specify Union operator	We need to specify Union All Operator
SQL Union All gives better performance in query execution in comparison to SQL Union	It gives better performance in comparison with SQL Union Operator

联盟	联合所有
它结合了多个表的结果集并消除了重复的记录	它结合了来自多个表的结果集，而不会消除重复的记录
它对结果集执行不同的操作。	它在结果集上表现不佳
我们需要指定联合运算符	我们需要指定Union All Operator
与SQL Union相比，SQL Union All在查询执行方面提供了更好的性能	与SQL Union Operator相比，它具有更好的性能

SQL Union和Union All运算符中的执行计划差异 (Execution plan difference in SQL Union vs Union All operator)

We get better query performance once we combine the result set of Select statement with SQL Union All operator. We can look at the difference using execution plans in SQL Server.

将Select语句的结果集与SQL Union All运算符组合在一起后，我们将获得更好的查询性能。我们可以使用SQL Server中的执行计划来查看差异。

Note: In this article, I am using ApexSQL Plan, a free SQL query execution plan viewer to generate an execution plan of Select statements.

注意 ：在本文中，我使用的是ApexSQL Plan ，这是一个免费的SQL查询执行计划查看器， 用于生成Select语句的执行计划。

SQL Union Operator的执行计划 (The execution plan for the SQL Union Operator)

We can click on Sort operator, and it shows Distinct – True.

我们可以单击Sort运算符，它显示Distinct – True 。

It gets the data individual Select statement
它获取数据单独的Select语句
SQL Server does a Concatenation for all of the data returned by Select statements
SQL Server对Select语句返回的所有数据进行串联
It performs a distinct operator to remove duplicate rows
它执行一个独特的运算符以删除重复的行

SQL Union All运算符的执行计划 (The execution plan for SQL Union All operator)

In the execution plan of both SQL Union vs Union All, we can see the following difference.

在SQL Union和Union All的执行计划中，我们可以看到以下区别。

SQL Union contains a Sort operator having cost 53.7% in overall batch operators
SQL Union包含一个排序运算符，该运算符在整体批处理运算符中的成本为53.7％
Sort operator could be more expensive if we work with large data sets
如果我们处理大型数据集，则排序运算符可能会更昂贵

SQL Union vs Union All子句中的Order By子句 (Order By clause in SQL Union vs Union All clause)

We cannot use the Order by clause with each Select statement. SQL Server can perform a sort in the final result set only.

我们不能在每个Select语句中使用Order by子句。 SQL Server只能在最终结果集中执行排序。

Let’s try to use Order by with each Select statement.

让我们尝试对每个Select语句使用Order by。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
Order by  [JobTitle]
 
UNION 
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F]
Order by  [JobTitle]
 
UNION 
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM
[AdventureWorks2017].[HumanResources].[Employee_All]
Order by  [JobTitle]

We get the following error message. It gives a “incorrect syntax” error message.

我们收到以下错误消息。它给出了“语法错误”的错误消息。

The valid query to sort result using Order by clause in SQL Union operator is as follows.

在SQL Union运算符中使用Order by子句对结果进行排序的有效查询如下。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F]
UNION
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_All]
ORDER BY [JobTitle];

We get the following output with result set sorted by JobTitle column.

我们得到以下输出，结果集按JobTitle列排序。

Select语句中SQL Union和Union All的组合 (Combination of SQL Union vs Union All in a Select statement)

We can use SQL Union vs Union All in a Select statement. Suppose we want to perform the following activities on our sample tables.

我们可以在Select语句中使用SQL Union vs Union All。假设我们要在示例表上执行以下活动。

Result Set A= UNION between [Employee_F] and [Employee_All]
结果集A = [Employee_F]和[Employee_All]之间的UNION
Result Set= Union All between [Employee_M] and Result Set A
结果集= [Employee_M]与结果集A之间的所有并集

In the following query, we use parentheses to perform Union between [Employee_F] and [Employee_All] tables. SQL Server runs the query inside parentheses and then performs Union All between result set and [Employee_M] table.

在以下查询中，我们使用括号在[Employee_F]和[Employee_All]表之间执行联合。 SQL Server在括号内运行查询，然后在结果集和[Employee_M]表之间执行“全部并集”。

SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_M]
UNION All
( -- Parentheses
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_F]
UNION 
SELECT [NationalIDNumber], 
       [LoginID], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender]
FROM [AdventureWorks2017].[HumanResources].[Employee_All]
)

We can understand it easily with execution plan. In the following screenshot, we can see the Actual Execution plan.

用执行计划容易理解。在下面的屏幕截图中，我们可以看到实际执行计划。

Step 1: Concatenation data (SQL Union) between Employee_F and Employee_All table.

步骤1 ： Employee_F和Employee_All表之间的串联数据（SQL Union）。

Step 2: Concatenation data (SQL Union All) between Employee_M and Step 1 output.

步骤2 ： Employee_M和步骤1输出之间的串联数据（SQL Union All）。

Combination of SQL Union and SQL Union All in a Select statement

结论 (Conclusion)

In this article, we compared SQL Union vs Union All operator and viewed examples with use cases. I hope you found this article helpful. Feel free to provide feedback in the comments below.

在本文中，我们比较了SQL Union和Union All运算符，并查看了带有用例的示例。希望本文对您有所帮助。请随时在下面的评论中提供反馈。

翻译自: https://www.sqlshack.com/sql-union-vs-union-all-in-sql-server/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server中SQL Union vs Union All

This article explains to the SQL Union and vs Union All operators in SQL Server. We will also explore the difference between these two operators along with various use cases. 本文向SQL Union解释了SQL...
复制链接

扫一扫