SQL Union – A Comprehensive Guide on the UNION Operator

While handling data in databases, we often tend to use the set operators in SQL, to retrieve data according to our requirements by combining two or more SELECT statements. In this article on SQL UNION, I will discuss the UNION operator used to retrieve data in the following sequence:

• What is the SQL UNION operator?
• Syntax:
  1. UNION Syntax
  2. UNION ALL Syntax
• Differences between UNION and UNION ALL
• SQL UNION Examples:
  1. UNION Operator example
  2. UNION ALL operator example
  3. UNION with SQL Aliases
  4. UNION with WHERE clause
  5. UNION ALL with WHERE clause
  6. UNION with JOINS
  7. UNION ALL with JOINS

Let us get started!

What is the SQL UNION operator? 

As the name suggests, this operator/ clause is used to combine the results of two or more SELECT statements. Here each SELECT statement used within the UNION statement must have the same number of columns in the same order. Also, all the columns present in the SELECT statements must have similar data types.

The UNION clause gives only unique values as output. Just in case, you want duplicate values, then you have to use the UNION ALL clause.

Moving on in this article on SQL UNION, let us understand the syntax.

SQL UNION Syntax

UNION Syntax

SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1
UNION
SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2;

UNION ALL Syntax

SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1
UNION ALL
SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2;

Moving on in this article, let us understand the differences between UNION and UNION ALL.

Differences between SQL UNION and UNION ALL

UNION	UNION ALL
Combines two or more result set and does not keep the duplicate values.	Combines two or more result set and keeps the duplicate values.
Syntax: UNION	Syntax: UNION ALL

Next, in this article on SQL UNION, let us understand the different ways to use this operator.

Examples of SQL UNION and UNION ALL

For your better understanding, I will be considering the following tables to show you different examples.

Employee Table

Projects Table

Let us get started with examples.

SQL UNION Examples

UNION Operator Example

Write a query to retrieve distinct cities from the Employees and Projects table.

SELECT City FROM Employees
UNION
SELECT City FROM Projects
ORDER BY City;

Output:

UNION ALL Operator Example

Write a query to retrieve cities from the Employees and Projects table. Here, duplicate values must be included.

 
SELECT City FROM Employees
UNION ALL
SELECT City FROM Projects
ORDER BY City; 

Output:

Next in this article, let us understand how to use the UNION clause with SQL Aliases.

UNION with SQL Aliases

SQL Aliases are used to give a table or a column a temporary name. So, let us write a query to list all the unique employees and projects.

SELECT 'Employee' AS Type, Name, City, Country
FROM Employees
UNION
SELECT 'Project', Name, City, Country
FROM Projects;

Output:

UNION with WHERE clause

Write a query to retrieve the distinct Indian cities and their postal codes from both the Employees and Projects table.

SELECT City, PostalCode, Country FROM Employees
WHERE Country='India'
UNION
SELECT City, PostalCode, Country FROM Projects
WHERE Country='India'
ORDER BY City;

Output:

UNION ALL with WHERE clause

Write a query to retrieve Indian cities and their postal codes from both the Employees and Projects table, where duplicate values are allowed

SELECT City, PostalCode, Country FROM Employees
WHERE Country='India'
UNION ALL
SELECT City, PostalCode, Country FROM Projects
WHERE Country='India'
ORDER BY City;

Output:

Moving forward in this article, let us understand how to use the UNION and UNION ALL clauses with JOINS. JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables. 

UNION with JOINS

The SQL UNION operator can be used with SQL JOINS to retrieve data from two different tables. I am going to consider the following table along with the Employees table for the example.

ProjectDetails table

SELECT  EmpID, Name, CostforProject
   FROM Employees
   LEFT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID
UNION
   SELECT  EmpID, Name, CostforProject
   FROM Employees
   RIGHT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID;

Output:

UNION ALL with JOINS

Write a query to retrieve the EmpID, Name and CostforProject from Employees and ProjectDetails table, where duplicate values are allowed.

SELECT  EmpID, Name, CostforProject
   FROM Employees
   LEFT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID
UNION ALL
   SELECT  EmpID, Name, CostforProject
   FROM Employees
   RIGHT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID;

Output:

By this, I come to the end of this article on SQL UNION. I hope you enjoyed reading this article on SQL UNION. We have seen the different ways to use the UNION  and UNION ALL commands to help you write queries. If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”SQL UNION” and I will get back to you.