SQL UNION: The Best Way to Combine SQL Queries

What Is UNION in SQL?

The UNION operator is used to combine the data from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results being combined.

To understand this operator, let’s get an insight into its syntax.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Syntax for Using the SQL UNION Operator

• The number of columns being retrieved by each SELECT command, within the UNION, must be the same.
• The columns in the same position in each SELECT statement should have similar data types. For example, “char” and “varchar” are identical data types.
• The columns must be in the correct order in the SELECT statements.

Let’s apply this operator to different tables’ columns.

Using UNION on a Single Field

Let’s use the following table, “Employee_dept,” as an example:

Another table, “Manager,” as an example: 

To determine which cities the employees and managers belong to from the two tables above, we’ll use the following query:

This will result in the following:

This shows that no copies are present in the result. The result column's name is “City,” as the result takes up the first SELECT statement’s column names.

Our Free Courses with Certificate

Introduction to SQL	Enroll Now!
SQL Injection For Beginners	Enroll Now!
Fundamentals of Database: What is SQL?	Enroll Now!
PostgreSQL: Become an SQL developer	Enroll Now!
Introduction to the Fundamentals of Databases	Enroll Now!

Using UNION on Multiple Fields

We can apply UNION on multiple columns and can also order the results using the ORDER BY operator in the end.

To retrieve the name and department of each employee and manager from the two sample tables above, you’ll use the following code: 

This will result in the following:

The result is sorted according to the “Dept_ID.”

We can also filter the rows being retrieved by each SELECT statement. Let’s see how this is done.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Using the Where Clause With the UNION Operator

We can use the WHERE clause in either one or both of the SELECT statements to filter out the rows being combined.

• To retrieve employee and manager names and salaries that exceed 60,000 from the “Employee_dept” and “Manager” tables, we’ll input the following: 

This will result in the following:

• We can also use the WHERE clause in only one of the SELECT statements in the UNION.

To find the names and addresses of all managers in the dataset and all the employees having “Dept_ID” equal to 1003:

This will result in the following:

Using UNION With Aliases

SQL aliases are temporary names given to tables or columns. These aliases exist only for the duration of the query they are being used in. We use the “AS” operator to create aliases.

For example, we’ll use the following query to retrieve all the employees’ and managers’ information, and we’ll categorize them according to their roles.

In the above query, we’ve created a temporary column labeled as “Type,” which will be used to categorize the information as employee or manager information.

The following is the result of the above query:

The managers are labeled as “Manager” and their subordinates as “Employee” in the temporary “Type” column of the UNION result.

Aliases help in creating organized table results.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

UNION ALL Operator

The UNION operator does not allow any duplicates. We can use the UNION ALL operator if we want duplicates to be present in the combination of two or more SELECT statements.

This SQL operator follows the same rules as the UNION operator, except for the use of the UNION ALL keyword instead of the UNION keyword in the syntax.

For example, if we want the list of all cities (including duplicates) from our “Employee_dept” and “Manager” tables, we’ll use the following query:

This will result in the following:

As we can see, the result contains all cities, including all duplicates.

With this, we reach the end of this article about the UNION operator.

Next Steps

If we remember all the rules and the syntax of the SQL UNION operator, combining query results becomes an easy task. We can achieve all kinds of results and retrieve very useful information using this knowledge.

Now that you know how to use the UNION operator, it is time for you to start querying and manipulating all kinds of datasets to retrieve useful information and patterns from them and move forward in your journey to becoming an SQL expert. If you liked this article and want to get certified, check out our Full Stack Developer - MERN Stack, as it covers everything you need to know about SQL.

Do you have any questions for us? Ask them in the comments section of this “SQL UNION: The Best Way to Combine SQL Queries” article, and we’ll have our experts in the field answer them for you.