SQL UNION Query

The SQL Union query combines the result set of two or more SELECT statements into a single result set. Union in SQL will select all the distinct records from all queries.

The following are the basic rules for SQL Server Union operator:

1. The number of columns must be the same in all the queries.
2. The column data types should be compatible with each other.
3. Columns order must be the same in all the queries.

SQL Union Syntax

The syntax behind the SQL Server Union is

-- SQL Server UNION Syntax
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table1
UNION
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table2

For this SQL Server Union query example, we use two tables (Employees 2015 & Employees 2016) present in our [SQL Tutorial] Database.

From the below screenshot you can observe that [Employees 2015] table have seven records

And [Employees 2016] table has four records. Remember that all these records are distinct

SQL Union Single Column example 1

How to use Union to select (union, or combine) columns from different tables. The following SQL Query will combine the result set of EmpID from Employees 2015 and Employees 2016, and display the result

-- SQL Server UNION Example
USE [SQL Tutorial]
GO
SELECT [EmpID] FROM [Employees 2015]
UNION
SELECT [EmpID] FROM [Employees 2016]

OUTPUT

SQL Union Different Tables example 2

In this Union Operator example, we are combining the Education column from both those tables.

-- SQL Server UNION Example
SELECT [Education] FROM [Employees 2015]
UNION
SELECT [Education] FROM [Employees 2016]

OUTPUT

SQL Union Multiple Columns example

In this example, we use this SQL Server Union to combine multiple columns.

The following SQL Server query combine the result set of Employees 2015, the result set of Employees 2016, and display the result

-- SQL Server UNION Example
SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
UNION
SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]

OUTPUT

SQL Union Order By Clause example

In SQL Server, you can use the Order By Clause along with this UNION operator. Here, Order By Clause will sort those query result based on the yearly income in descending order.

-- SQL Server UNION Example
SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
UNION
SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]
  ORDER BY YearlyIncome DESC

OUTPUT

SQL Union Where Clause example

How to use the SQL Server UNION operator along with the where clause? In this example, we are combining two statements:

• The first result set selects all the rows from Employees 2015 whose yearly income is greater than or equal to 70000
• The second result set selects the records from Employees 2016 whose yearly income is less than 70000

-- SQL UNION Query with Where Condition  
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE YearlyIncome >= 70000
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]
  WHERE YearlyIncome < 70000
  ORDER BY YearlyIncome DESC

OUTPUT

Union Where Clause 2

This query returns all the records from the 2015 table whose Occupation is either professional or management. And, returns records from 2016 whose occupation is Clerical

-- SQL UNION Query with Where Condition  
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE Occupation = 'Professional' OR
		Occupation = 'Management'
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]
  WHERE Occupation = 'Clerical'
  ORDER BY Occupation DESC

OUTPUT

SQL Union Where Clause 3

You can also use different columns in the Where clause. Here, the first query returns records whose yearly income is greater than or equal to 70000. The second query returns employees whose Occupation is either professional or management.

-- SQL UNION Query with Where Condition  
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE YearlyIncome >= 70000
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]
  WHERE Occupation = 'Professional' OR
		Occupation = 'Management'
  ORDER BY Occupation DESC

OUTPUT

SQL Union Different columns

The SQL Server also allows you to use a different column name from different tables. As long as they are of the same data type, there will be no issue. Remember, the result will take the first query column name. However, you can use ALIAS columns on both the statements to add your column name.

The following SQL Query will combine the result of Education from 2015 and the Occupation column from 2016.

-- SQL Server UNION Example
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [YearlyIncome], [Sales]
  FROM [Employees 2015]
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Occupation], [YearlyIncome], [Sales]
  FROM [Employees 2016]

OUTPUT

Union with same table

You can always use Union operator to self-join or self-combine the table. In the below query

• The first query returns all the employees whose income is greater than or equal to 80000
• The second query selects employees whose occupation is professional

-- SQL Server UNION Example
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation],
	  [YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE YearlyIncome >= 80000
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation], 
	  [YearlyIncome], [Sales]
  FROM [Employees 2015]
  WHERE Occupation = 'Professional'

OUTPUT

SQL Union Different Number of Columns

This example demonstrates the common errors encountered while writing the Union. Let us see what will happen when we perform union operation on the unequal length of columns.

Here, we are selecting Occupation as the extra column from Employees 2016 table

-- SQL UNION Query 
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], 
	  [YearlyIncome], [Sales]
  FROM [Employees 2015]
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], [Occupation], -- Extra
	  [YearlyIncome], [Sales]
  FROM [Employees 2016]

OUTPUT

Hope you can read and understand the message.

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Now, let us change the query to select an equal number of columns

From the above screenshot, you can observe that it is returning 14 records because there are duplicate records in Employees 2016. And our Union operator selects distinct records only.

SQL Union Select into Statement

You can also use this UNION inside a Select Into Statement. The following Query select and insert the Union result into the Duplicate Emp table.

-- SQL Server UNION Example
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], 
	  [YearlyIncome], [Sales]
  INTO [SQL Tutorial].[dbo].[DuplicateEmp]
  FROM [Employees 2015]
UNION
SELECT [EmpID]
      ,[FirstName] + ' ' +[LastName] AS Name
	  ,[Education], 
	  [YearlyIncome], [Sales]
  FROM [Employees 2016]
  WHERE Occupation = 'Professional'

OUTPUT

Let me show you the data that we inserted into the Duplicate Emp table