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 same in all the queries.
2. The column data types should compatible with each other.
3. Columns order must be 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 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 six records

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

SQL Union example

The following SQL Query will combine the result set of Employees 2015, result set of Employees 2016, and display the result

SQL CODE

-- UNION SQL Example
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2015]
  UNION
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]

OUTPUT

SQL Union Query along with Where

The following SQL Query will display, How to use the SQL UNION operator along with the where clause. In this example, we are combining two statement:

• First result set will select all the records from Employees 2015 whose yearly income is greater than or equal to 70000
• Second result set will select the records from Employees 2016 whose yearly income is less than 70000

SQL CODE

-- SQL UNION Query with Where Condition  
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2015]
  WHERE [YearlyIncome] >= 70000 
  UNION
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]
  WHERE [YearlyIncome] < 70000

OUTPUT

Distinct records using SQL Union Query

The following SQL Query will display all the common errors encountered while writing the SQL Union query. For this, We are going to use two tables (Employees and Employees 2016) present in our [SQL Tutorial] Database.

From the below screenshot you can observe that, [Employee] table have 7 columns, and 14 rows.

Let us see what will happen, when we perform union operation on unequal length of columns.

T-SQL CODE

-- SQL UNION Query 
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[Employee]
UNION 
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]

OUTPUT

Hope you can read the message. Now, let us change the query to select equal number of columns

T-SQL CODE

-- SQL Server UNION Query with Where Condition  
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employee]
UNION 
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]

OUTPUT

From the above screenshot you can observe that, it is returning 15 records because, except 1 record (John, Miller) remaining three records in Employees 2016 are also present in Employee table. And our Union operator select distinct records only.

Thank You for Visiting Our Blog