Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

SQL UNION Query

The SQL Union query combines 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 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 is

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

For this SQL Server Union query example, we use two tables (Employees 2015 & Employees 2016) present in our [SQL Tutorial] Database.

SQL UNION Example 1

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

SQL UNION Example 2

SQL Union Single Column example 1

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]
SQL UNION Example 3

Union Different Tables example 2

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

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

SQL Union Multiple Columns example

In this example, we use this SQL Server Union to combine multiple columns. The following SQL Server query 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]
SQL UNION Example 5

SQL Union 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
SQL UNION Example 6

SQL Union Where Clause example

How to use the SQL Server UNION operator along with the where clause? 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 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
SQL UNION Example 7

Union Where Clause 2

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

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

SQL Union Where Clause 3

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

-- 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
SQL UNION Example 9

SQL Union Different columns

The SQL Server 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 the statements to add your 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]
SQL UNION Example 10

Union with 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'
SQL UNION Example 11

SQL Union Different Number of Columns

This example demonstrates the common errors encountered while writing the Union. 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]
SQL UNION Example 12

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

SQL UNION Example 13

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

SQL Union 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'
SQL UNION Example 14

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

SQL UNION Example 15

Filed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy