SQL UNION Query

The SQL Union query is used to combine 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 Union operator in SQL:

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

SQL Union Syntax

The basic syntax behind the Union in SQL Server is as shown below:

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

In this article, we will show you, How to write SQL Union query with an example. For this, We are going to use two tables (Employees 2015 and 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 have four records. Remember that, all these records are distinct

SQL Union – Single Column example 1

In this example, we will show you, 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 – Single Column example 2

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

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

OUTPUT

SQL Union example – Multiple Columns

In this example, we use this UNION to combine multiple columns.

The following Query will 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 with 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 along with Where Clause

In this example, we will show you, How to use the SQL Server UNION operator along with the where clause. In this example, we are combining two statement:

• 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

SQL Union with Where Clause 2

This query returns all the records from 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 with Where Clause 3

You can also use different columns in the Where clause. Here, 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 with Different columns

The SQL Server also allows you to use a different column name from different tables. As long as they are of 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 own 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

SQL Union with the 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 Query – Different Number of Columns

This example demonstrates the common errors encountered while writing the Union in SQL server. 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 in 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