集运算符 - UNION (Transact-SQL)Set Operators - UNION (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

将两个查询的结果连接到一个结果集中。Concatenates the results of two queries into a single result set. 可控制结果集是否包含重复行:You control whether the result set includes duplicate rows:

  • UNION ALL - 包括重复行。UNION ALL - Includes duplicates.
  • UNION - 排除重复行。UNION - Excludes duplicates.

UNION 操作不同于 JOIN 操作 :A UNION operation is different from a JOIN:

  • UNION 连接两个查询中的结果集。A UNION concatenates result sets from two queries. 但 UNION 不会从两个表收集的列中创建单独的行。But a UNION does not create individual rows from columns gathered from two tables.
  • JOIN 比较两个表中的列,以创建由两个表中的列组成的结果行。A JOIN compares columns from two tables, to create result rows composed of columns from two tables.

下面列出了使用 UNION 合并两个查询结果集的基本规则:The following are basic rules for combining the result sets of two queries by using UNION:

  • 所有查询中的列数和列的顺序必须相同。The number and the order of the columns must be the same in all queries.

  • 数据类型必须兼容。The data types must be compatible.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

{ <query_specification> | ( <query_expression> ) }   
{ UNION [ ALL ]   
  { <query_specification> | ( <query_expression> ) } 
  [ ...n ] }

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

<query_specification> | ( <query_expression> ):查询规范或查询表达式,用于返回要与另一个查询规范或查询表达式所返回的数据合并的数据。<query_specification> | ( <query_expression> ) Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. 属于 UNION 运算的列定义不一定要相同,但必须可通过隐式转换实现兼容。The definitions of the columns that are part of a UNION operation don't have to be the same, but they must be compatible through implicit conversion. 如果数据类型不同,则根据数据类型优先级规则确定所产生的数据类型。When data types differ, the resulting data type is determined based on the rules for data type precedence. 如果类型相同,但精度、确定位数或长度不同,那么结果以相同的表达式合并规则为依据。When the types are the same but differ in precision, scale, or length, the result is based on the same rules for combining expressions. 有关详细信息,请参阅精度、小数位数和长度 (Transact-SQL)For more information, see Precision, Scale, and Length (Transact-SQL).

xml 数据类型的列必须相等。Columns of the xml data type must be equal. 所有的列必须类型化为 XML 架构或是非类型化的。All columns must be either typed to an XML schema or untyped. 如果要类型化,这些列必须类型化为相同的 XML 架构集合。If typed, they must be typed to the same XML schema collection.

UNIONUNION
指定合并多个结果集并将其作为单个结果集返回。Specifies that multiple result sets are to be combined and returned as a single result set.

ALLALL
将全部行都纳入结果,包括重复行。Incorporates all rows into the results, including duplicates. 如果未指定该参数,则删除重复行。If not specified, duplicate rows are removed.

示例Examples

A.A. 使用简单 UNIONUsing a simple UNION

在下面的示例中,结果集同时包含 ProductModelIDName 表中的 ProductModelGloves 列中的内容。In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.

-- Uses AdventureWorks  
  
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL  
DROP TABLE dbo.Gloves;  
GO  
-- Create Gloves table.  
SELECT ProductModelID, Name  
INTO dbo.Gloves  
FROM Production.ProductModel  
WHERE ProductModelID IN (3, 4);  
GO  
  
-- Here is the simple union.  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves  
ORDER BY Name;  
GO  

B.B. 将 SELECT INTO 与 UNION 一起使用Using SELECT INTO with UNION

在下面的示例中,第二个 SELECT 语句中的 INTO 子句指定,ProductResults 表保留 ProductModelGloves 表中选定列的并集的最终结果集。In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the selected columns of the ProductModel and Gloves tables. Gloves 表是在第一个 SELECT 语句中创建。The Gloves table is created in the first SELECT statement.

-- Uses AdventureWorks  
  
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL  
DROP TABLE dbo.ProductResults;  
GO  
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL  
DROP TABLE dbo.Gloves;  
GO  
-- Create Gloves table.  
SELECT ProductModelID, Name  
INTO dbo.Gloves  
FROM Production.ProductModel  
WHERE ProductModelID IN (3, 4);  
GO  
  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
INTO dbo.ProductResults  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves;  
GO  
  
SELECT ProductModelID, Name   
FROM dbo.ProductResults;  

C.C. 将 ORDER BY 与两个 SELECT 语句的 UNION 一起使用Using UNION of two SELECT statements with ORDER BY

在 UNION 子句中使用的某些参数的顺序非常重要。The order of certain parameters used with the UNION clause is important. 下面的示例通过两个 UNION 语句说明 SELECT 的错误用法和正确用法(在这两个语句的输出中将重命名一个列)。The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.

-- Uses AdventureWorks  
  
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL  
DROP TABLE dbo.Gloves;  
GO  
-- Create Gloves table.  
SELECT ProductModelID, Name  
INTO dbo.Gloves  
FROM Production.ProductModel  
WHERE ProductModelID IN (3, 4);  
GO  
  
/* INCORRECT */  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
ORDER BY Name  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves;  
GO  
  
/* CORRECT */  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves  
ORDER BY Name;  
GO  

D.D. 使用三个 SELECT 语句的 UNION 来说明 ALL 和括号的作用Using UNION of three SELECT statements to show the effects of ALL and parentheses

下列示例使用 UNION 来组合具有相同 5 行数据的三个表的结果。The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. 第一个示例使用 UNION ALL 显示重复记录,返回所有的 15 行。The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. 第二个示例使用不带 UNIONALL,删除三个 SELECT 语句的组合结果中的重复行,返回 5 行。The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.

第三个示例将 ALL 用于第一个 UNION,并用括号将没有使用 ALL 的第二个 UNION 括起来。The third example uses ALL with the first UNION and parentheses enclose the second UNION that isn't using ALL. 第二个 UNION 因位于括号内而首先得到处理,并返回 5 行,这是因为未使用 ALL 选项,重复行遭删除。The second UNION is processed first because it's in parentheses, and returns 5 rows because the ALL option isn't used and the duplicates are removed. 通过使用 SELECT 关键字将这 5 行与第一个 UNION ALL 的结果组合在一起。These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. 下面的示例不删除两个 5 行结果集之间的重复行。This example doesn't remove the duplicates between the two sets of five rows. 最终结果有 10 行。The final result has 10 rows.

-- Uses AdventureWorks  
  
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL  
DROP TABLE dbo.EmployeeOne;  
GO  
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL  
DROP TABLE dbo.EmployeeTwo;  
GO  
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL  
DROP TABLE dbo.EmployeeThree;  
GO  
  
SELECT pp.LastName, pp.FirstName, e.JobTitle   
INTO dbo.EmployeeOne  
FROM Person.Person AS pp JOIN HumanResources.Employee AS e  
ON e.BusinessEntityID = pp.BusinessEntityID  
WHERE LastName = 'Johnson';  
GO  
SELECT pp.LastName, pp.FirstName, e.JobTitle   
INTO dbo.EmployeeTwo  
FROM Person.Person AS pp JOIN HumanResources.Employee AS e  
ON e.BusinessEntityID = pp.BusinessEntityID  
WHERE LastName = 'Johnson';  
GO  
SELECT pp.LastName, pp.FirstName, e.JobTitle   
INTO dbo.EmployeeThree  
FROM Person.Person AS pp JOIN HumanResources.Employee AS e  
ON e.BusinessEntityID = pp.BusinessEntityID  
WHERE LastName = 'Johnson';  
GO  
-- Union ALL  
SELECT LastName, FirstName, JobTitle  
FROM dbo.EmployeeOne  
UNION ALL  
SELECT LastName, FirstName ,JobTitle  
FROM dbo.EmployeeTwo  
UNION ALL  
SELECT LastName, FirstName,JobTitle   
FROM dbo.EmployeeThree;  
GO  
  
SELECT LastName, FirstName,JobTitle  
FROM dbo.EmployeeOne  
UNION   
SELECT LastName, FirstName, JobTitle   
FROM dbo.EmployeeTwo  
UNION   
SELECT LastName, FirstName, JobTitle   
FROM dbo.EmployeeThree;  
GO  
  
SELECT LastName, FirstName,JobTitle   
FROM dbo.EmployeeOne  
UNION ALL  
(  
SELECT LastName, FirstName, JobTitle   
FROM dbo.EmployeeTwo  
UNION  
SELECT LastName, FirstName, JobTitle   
FROM dbo.EmployeeThree  
);  
GO  
  

示例:Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) and 并行数据仓库Parallel Data Warehouse

E.E. 使用简单 UNIONUsing a simple UNION

在下面的示例中,结果集同时包含 FactInternetSalesDimCustomer 表中的 CustomerKey 列中的内容。In the following example, the result set includes the contents of the CustomerKey columns of both the FactInternetSales and DimCustomer tables. 由于未使用 ALL 关键字,因此重复行会从结果中排除。Since the ALL keyword isn't used, duplicates are excluded from the results.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
UNION   
SELECT CustomerKey   
FROM DimCustomer   
ORDER BY CustomerKey;  

F.F. 将 ORDER BY 与两个 SELECT 语句的 UNION 一起使用Using UNION of two SELECT statements with ORDER BY

当 UNION 语句中的任何 SELECT 语句包含 ORDER BY 子句时,该子句应置于所有 SELECT 语句之后。When any SELECT statement in a UNION statement includes an ORDER BY clause, that clause should be placed after all SELECT statements. 下面的示例通过两个 SELECT 语句说明 UNION 的错误用法和正确用法(在这两个语句中使用 ORDER BY 对一个列排序)。The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is ordered with ORDER BY.

-- Uses AdventureWorks  
  
-- INCORRECT  
SELECT CustomerKey   
FROM FactInternetSales    
ORDER BY CustomerKey  
UNION   
SELECT CustomerKey   
FROM DimCustomer  
ORDER BY CustomerKey;  
  
-- CORRECT   
USE AdventureWorksPDW2012;  
  
SELECT CustomerKey   
FROM FactInternetSales    
UNION   
SELECT CustomerKey   
FROM DimCustomer   
ORDER BY CustomerKey;  

G.G. 将 WHERE 和 ORDER BY 与两个 SELECT 语句的 UNION 一起使用Using UNION of two SELECT statements with WHERE and ORDER BY

下面的示例通过两个 SELECT 语句说明 UNION 的错误用法和正确用法(在这两个语句中需要 WHERE 和 ORDER BY)。The following example shows the incorrect and correct use of UNION in two SELECT statements where WHERE and ORDER BY are needed.

-- Uses AdventureWorks  
  
-- INCORRECT   
SELECT CustomerKey   
FROM FactInternetSales   
WHERE CustomerKey >= 11000  
ORDER BY CustomerKey   
UNION   
SELECT CustomerKey   
FROM DimCustomer   
ORDER BY CustomerKey;  
  
-- CORRECT  
USE AdventureWorksPDW2012;  
  
SELECT CustomerKey   
FROM FactInternetSales   
WHERE CustomerKey >= 11000  
UNION   
SELECT CustomerKey   
FROM DimCustomer   
ORDER BY CustomerKey;  

H.H. 使用三个 SELECT 语句的 UNION 来说明 ALL 和括号的作用Using UNION of three SELECT statements to show effects of ALL and parentheses

下面的示例使用 UNION 合并同一个表的结果,以展示使用 UNION 时 ALL 和括号的效果。The following examples use UNION to combine the results of the same table to demonstrate the effects of ALL and parentheses when using UNION.

第一个示例使用 UNION ALL 显示重复记录,并三次返回源表中的每一行。The first example uses UNION ALL to show duplicated records and returns each row in the source table three times. 第二个示例使用不带 ALLUNION,删除三个 SELECT 语句的组合结果中的重复行,仅返回源表中的非重复行。The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements and returns only the unduplicated rows from the source table.

第三个示例将 ALL 用于第一个 UNION,并用括号将没有使用 ALL 的第二个 UNION 括起来。The third example uses ALL with the first UNION and parentheses enclosing the second UNION that isn't using ALL. 首先处理第二个 UNION,因为它位于括号中。The second UNION is processed first because it is in parentheses. 仅从表返回非重复行,因为未使用 ALL 选项,重复行遭删除。It returns only the unduplicated rows from the table because the ALL option isn't used and duplicates are removed. 通过使用 UNION ALL 关键字将这些行与第一个 SELECT 的结果合并在一起。These rows are combined with the results of the first SELECT by using the UNION ALL keywords. 下面的示例不删除两个结果集之间的重复行。This example doesn't remove the duplicates between the two sets.

-- Uses AdventureWorks  
  
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION ALL   
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION ALL   
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer;  
  
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION   
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION   
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer;  
  
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION ALL  
(  
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
UNION   
SELECT CustomerKey, FirstName, LastName  
FROM DimCustomer  
);  

另请参阅See Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
SELECT 示例 (Transact-SQL)SELECT Examples (Transact-SQL)