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:
- The number of columns must be same in all the queries.
- The column data types should compatible with each other.
- 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:
|
1 2 3 4 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
Great information, Could you please share the sample database for SQL SERVER.
Thank you.