SQL UNION Example 9

SQL UNION Query

The SQL Union operator combines the result set of two or more SELECT statements into a single result set. The Union operator will return all the distinct records from all queries by removing the duplicate rows. The following are the basic rules for SQL Server Union operator:

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

Syntax

The syntax behind the SQL Server Union operator is

SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table1
UNION
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table2

For this demo, we use two tables (Employees 2015 & Employees 2016) present in our Database.

First Table Rows 1

And the final result set of a [Employees 2016] table has four records. Remember that all these records are distinct because it removes duplicate rows in the select statement.

Second Table Data 2

SQL Union on a Single Column

This example shows how to use the select statements and the Union clause to combine columns from two tables. The following union Query will combine the result set of EmpID from Employees 2015 and Employees 2016.

SELECT [EmpID] FROM [Employees 2015]
UNION
SELECT [EmpID] FROM [Employees 2016]
Single Column from Two Tables 3

Combine Two Tables example

In this SQL Server Union Operator example, we combine the Education column from both those tables using the select statement.

SELECT [Education] FROM [Employees 2015]
UNION
SELECT [Education] FROM [Employees 2016]
Two Different Tables 4

SQL Server Union on Multiple Columns example

In this example, we use this to combine multiple columns. For example, the following Server query combines the result set of Employees 2015 and the result set of Employees 2016 and displays the result.

SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2015]
UNION
SELECT [EmpID], [FirstName], [LastName]
      ,[Education], [Occupation]
      ,[YearlyIncome], [Sales]
  FROM [Employees 2016]
Use Multiple Columns 5

How to use SQL Union with Order By Clause?

In general, you can use the Order By Clause along with this. The Order By Clause will sort those query results based on the yearly income in descending order.

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
SQL Union Order By Clause 6

SQL Server Union Where Clause Example

How to use this along with the where clause of the structured query language? In this example, we are combining two statements:

  • The first result set return 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 income is less than 70000
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
SQL Union Where Clause 7

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

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
Example 8

How to use SQL Union Operator with Where Clause?

You can also use different cols in the Where. 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.

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
SQL Union Operator with Where Clause 9

Union Different columns

It also allows you to use a different column name from different tables. There will be no issue if they are of the same data type. Remember, the result will take the first query column name. However, you can use ALIAS columns on both statements to add your column name.

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

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]
Combine Different Columns Example 10

With the same table

You can always use the 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 prints employees whose occupation is professional
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'
With Same Table Example 11

Combine Different Number of Columns

This example demonstrates the common errors encountered while writing this operator. Next, let us see what will happen when we perform this Union operation on the unequal length or the different number of columns.

Here, we select Occupation as the extra column from the Employees 2016 table.

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]
Messages
--------
Msg 205, Level 16, State 1, Line 2
All queries combined using aUNION, INTERSECT or EXCEPT operator 
must have an equal number of expressions in their target lists.

I hope you can read and understand the message. Now, let us change the query to select an equal number of cols

Error Msg 205 : Different Number of Columns 13

From the above screenshot, it is returning 14 records because there are duplicate records in Employees 2016. And our operator removes duplicate rows and selects distinct values only.

Union Select into Statement

You can also use this inside a Select Into Statement. For example, the following Query inserts the result into the Duplicate Emp table.

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'
Messages
--------
(14 row(s) affected)

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

SQL Union Select into Statement 15

Comments are closed.