When working with databases, it's common to need to combine data from multiple tables into a single result set. There are several ways to achieve this, but one of the most powerful and flexible techniques is the SQL UNION operator. This article explores the various use cases for the SQL UNION operator, how it works, and why it's often the best way to combine SQL queries.

What is SQL UNION?

The SQL UNION operator allows one to combine the results of two or more SELECT statements into a single result set. Each SELECT statement can select different columns from different tables or even different databases. As a result, columns are merged into a single result set.

The basic syntax for the SQL UNION operator is as follows:

In this example, the SQL UNION operator combines the results of two SELECT statements that select the same columns from two different tables.

Use Cases for SQL UNION

There are many situations in which a person might want to use the SQL UNION operator.

Here are a few examples:

Combining Data from Multiple Tables

Most of the time, SQL UNION is used to integrate data from more than one table. For example, if a person has two tables with information on customers, they may wish to merge them into a single result set to obtain a full list of all customers. This is seen by the following SQL query.

In this example, the SQL UNION operator combines the results of two SELECT statements that select customer information from two different tables.

Merging Results from Similar Queries

Another common use case for SQL UNION is to merge the results of similar queries. For example, if a developer has two SELECT statements that select data from different tables but have similar criteria, they can combine them using the SQL UNION operator.

In this example, the SQL UNION operator combines the results of two SELECT statements that select order information from two different tables. The criteria for each SELECT statement is the same, and the resulting data is merged into a single result set.

Combining Data from Multiple Databases

In some cases, developers might need to combine data from multiple databases. The SQL UNION operator makes it easy to do this. Here's an example:

In this example, the SQL UNION operator combines the results of two SELECT statements that select customer information from two different databases.

How SQL UNION Works

When a person uses the SQL UNION operator, the database engine runs each SELECT query independently and then combines the results into a single result set.

Have a look at the example given below:

Consider two tables, "Employees" and "Customers" in a retail database. The Employees table contains the following columns: "employee_id", "employee_name", "department", "salary", and "hire_date". The Customers table contains the following columns: "customer_id", "customer_name", "address", "email", and "phone_number".

Now suppose a developer wants to retrieve a list of all the employees and customers in the database, along with their respective IDs and names. Without UNION, they would need to run two separate SELECT statements, one for each table, and then merge the results manually in a spreadsheet or other tool.

But with UNION, they can combine the two SELECT statements into a single query that returns all the data they need in one go. Here's an example of how to use UNION to accomplish this:

This query returns a result set with three columns: "employee_id", "employee_name", and "record_type". The "record_type" column is added to indicate whether each row comes from the "Employees" table or the "Customers" table. Note that the column names and data types in the two SELECT statements must match for the UNION to work properly.

Another use case for UNION is when a developer want to combine results from two different queries that have the same structure, but different criteria. For example, suppose they want to retrieve a list of all employees who earn more than $50,000 per year and all customers who live in California. They could write two separate SELECT statements, like this:

But how do they combine the results into a single list? Once again, UNION comes to the rescue:

This query gives back a result set with five columns: "employee_id," "employee_name," "department," "salary," and "address." Notice that the SELECT queries have been slightly changed to make sure that the column names and data types match. Also, notice how NULL values are used to make sure that each SELECT operation returns the same amount of columns.

By utilizing UNION, a developer may combine two searches that would ordinarily need separate SELECT statements and blending of results by hand. This may save their time and make their code simpler to understand and keep up-to-date.

Union of Tables with Different Columns

One of the best things about the SQL UNION operator is that it may merge two or more tables with columns of various numbers or kinds. But each SELECT query must have the same amount of columns, and the data types must be the same. In this situation, just the columns that are in all of the SELECT queries will be in the result set. If one of the tables is missing a column, it will show up as NULL in the result set.

Let's say one has two tables: one keeps the information about customers, and the other contains information about orders. The customer_id, first_name, and last_name are the three fields in the customer's table. There are three columns in the orders database, too: order_id, customer_id, and order_date. By using the UNION operator, one can merge these two tables and get all of the first and last names and order dates for each client.

The first SELECT query gets the first and last names of all customers and creates two NULL columns with the names order_id and order_date to match the second SELECT statement. The second SELECT query gets the order_id and order_date columns from the orders database and adds two NULL columns with the names first_name and last_name to match the first SELECT statement. The UNION operation brings together the sets of results and gives the following output:

Note that the output includes only the columns that appear in both SELECT statements. Also, the order of the columns is determined by the first SELECT statement. In this case, the first SELECT statement retrieves the first_name and last_name columns, so they appear first in the output.

Using UNION ALL Operator

In some cases, the developer may want to include all the rows from two or more tables, even if they are duplicates. In this case, they can use the UNION ALL operator instead of the UNION operator. The UNION ALL operator works similarly to the UNION operator, but it does not remove duplicate rows.

Suppose they have two tables, one that stores information about customers and another that store information about employees. Both tables have three columns: id, name, and email. Developers can combine these two tables using the UNION ALL operator to retrieve all the rows from both tables.

The UNION ALL operator combines the result sets from both SELECT statements and returns the following output:

Note that the output includes all the rows from both tables, even if there are duplicates.

Conclusion

SQL UNION is an effective way to combine data from several SQL tables. It lets a developer aggregate data from several tables into a single set of results that they can then analyze or process further. The nicest thing about SQL UNION is that it is straightforward to use and lets one integrate data from several sources fast and effectively. SQL UNION is a tool that any SQL developer should have, no matter how much experience they have.

Visit Cogent University to read more such informative articles and learn.

‍