Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
The following are basic rules for combining the result sets of two queries by using UNION:
Transact-SQL Syntax Conventions
{ <query_specification> | ( <query_expression> ) } UNION [ ALL ] <query_specification | ( <query_expression> ) [ UNION [ ALL ] <query_specification> | ( <query_expression> ) [ ...n ] ]
Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation do not 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 determined based on the same rules for combining expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
Columns of the xml data type must be equivalent. All columns must be either typed to an XML schema or untyped. If typed, they must be typed to the same XML schema collection.
Specifies that multiple result sets are to be combined and returned as a single result set.
Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.
ProductModelID
Name
ProductModel
Gloves
USE AdventureWorks ; 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 -- Here is the simple union. USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ; GO
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 designated columns of the ProductModel and Gloves tables. Note that the Gloves table is created in the first SELECT statement.
INTO
SELECT
ProductResults
USE AdventureWorks ; GO 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 USE AdventureWorks ; GO SELECT ProductModelID, Name INTO ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ; GO SELECT * FROM dbo.ProductResults ;
The order of certain parameters used with the UNION clause is important. 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.
UNION
USE AdventureWorks ; 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 /* INCORRECT */ USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves ; GO /* CORRECT */ USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ; GO
The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. 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.
UNION ALL
ALL
The third example uses ALL with the first UNION and parentheses enclose the second UNION that is not using ALL. The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. This does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.
USE AdventureWorks ; GO IF OBJECT_ID ('EmployeeOne', 'U') IS NOT NULL DROP TABLE EmployeeOne ; GO IF OBJECT_ID ('EmployeeTwo', 'U') IS NOT NULL DROP TABLE EmployeeTwo ; GO IF OBJECT_ID ('EmployeeThree', 'U') IS NOT NULL DROP TABLE EmployeeThree ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeOne FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeTwo FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeThree FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO -- Union ALL SELECT LastName, FirstName FROM EmployeeOne UNION ALL SELECT LastName, FirstName FROM EmployeeTwo UNION ALL SELECT LastName, FirstName FROM EmployeeThree ; GO SELECT LastName, FirstName FROM EmployeeOne UNION SELECT LastName, FirstName FROM EmployeeTwo UNION SELECT LastName, FirstName FROM EmployeeThree ; GO SELECT LastName, FirstName FROM EmployeeOne UNION ALL ( SELECT LastName, FirstName FROM EmployeeTwo UNION SELECT LastName, FirstName FROM EmployeeThree ) ; GO
ORDER BY