SQL UNION: The Best Way to Combine SQL Queries

Data from multiple tables is required to retrieve useful information in real-world applications most of the time. SQL provides several tools to accomplish this, and one such tool is the SQL UNION operator. This is used to combine the results of two select commands performed on columns from different tables.

In this article on the SQL UNION operator, we’ll be covering the following topics:

  • What is UNION in SQL?
  • Syntax for Using the SQL UNION Operator
  • Using UNION on a Single Field
  • Using UNION on Multiple Fields
  • Using the Where Clause With the UNION Operator
  • Using UNION With Aliases
  • UNION ALL Operator

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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.

Union_illustration

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

Syntax for Using the SQL UNION Operator

SELECT column_1, column_2,...column_n

FROM table_1

UNION

SELECT column_1, column_2,...column_n

FROM table_2;

  • 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:

Employee_dept_table

Another table, “Manager,” as an example: 

/Manager_table

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

single_field-SQL_Union

This will result in the following:

single_field_output.

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.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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: 

union_with_order_by

This will result in the following:

union_with_order_by_output.

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.

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: 

with_where_clause-SQL_Union

This will result in the following:

with_where_clause_output

  • 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:

where_in_only_one_select.

This will result in the following:

where_in_only_one_select_output

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.

with_alias-SQL_Union

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:

with_alias_output

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.

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.

union_all_illustration-SQL_Union.

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:

union_all-SQL_Union.

This will result in the following:

union_all_output

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.

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

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 Business Analyst Master’s Program, 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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.