SQL UNION Example 9

SQL UNION Query

The SQL Union operator is used to combine the result set of two or more SELECT statements into a single result set. Union operator will select all the distinct records from all queries by removing the duplicate rows. 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 operator is

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

For this SQL Union operator 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

How to use the select statements along with Union clause to combine columns from two tables. The following SQL 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]
SQL Union Single Column 3

Combine Two Tables example

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

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

SQL Union on Multiple Columns example

In this example, we use this SQL Server Union to combine multiple columns. The following Server query combines the result set of Employees 2015, 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]
SQL Union Multiple Columns 5

How to use SQL Union with Order By Clause?

In general, 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.

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

SQL Union Where Clause example

How to use the UNION operator along with the where clause of the structured query language?. 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 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, 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
SQL UNION 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 Example 9

SQL Union Different columns

It 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 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

Union operator with same table

You can always use the SQL 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
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'
UNION With Same Table Example 11

Combine Different Number of Columns

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

Here, we are selecting Occupation as the extra column from 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.

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

Union 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 remove duplicate rows and selects distinct values only.

SQL Union Select into Statement

You can also use this inside a Select Into Statement. The following Query select and insert 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

Union Select into Statement 15

Comments are closed.