Distinguishing Between UNION and UNION ALL Operations

1. Overview

In SQL, the UNION and UNION ALL operations combine the results of two or more SELECT queries into a single result set. Understanding the differences between these operations is crucial for optimizing database queries and ensuring accurate data retrieval.

In this article, we’ll explore the UNION and UNION ALL operations, discussing their syntax, use cases, and key differences. Furthermore, we’ll also look at practical examples using a sample database.

2. What is UNION Operation

The UNION operation in SQL combines the results of two or more SELECT queries into a single distinct result set. It removes duplicate rows from the final result, ensuring each row appears only once.

The basic syntax for the UNION operation is:

SELECT column1, column2, column3
FROM table1
UNION
SELECT column1, column2, column3
FROM table2;

In this syntax, the UNION operation combines the results of two SELECT queries on table1 and table2. The result set will include unique rows from both tables, eliminating duplicates.

2.1. Simple Example Using a Single Table

For this article, we’ll use the Department and Faculty tables in the Baeldung University database schema. For simplicity, let’s start with two different queries on the Department table.

First, let’s query to select departments with code starting with ‘C‘ or ‘M’:

SELECT name AS department_name
 FROM Department
 WHERE code LIKE 'C%' OR code LIKE 'M%';
+------------------------+
| department_name        |
|------------------------|
| Computer Science       |
| Mechanical Engineering |
| Civil Engineering      |
| Mathematics            |
+------------------------+
SELECT 4
Time: 0.006s

Next, we query to select departments with code starting with only ‘M‘:

SELECT name AS department_name
 FROM Department
 WHERE code LIKE 'M%';
+------------------------+
| department_name        |
|------------------------|
| Mechanical Engineering |
| Mathematics            |
+------------------------+
SELECT 2
Time: 0.005s

Now, let’s combine these two queries using the UNION operation:

SELECT name AS department_name
 FROM Department
 WHERE code LIKE 'C%'OR code LIKE 'M%'
 UNION
 SELECT name AS department_name
 FROM Department
 WHERE code LIKE 'M%';
+------------------------+
| department_name        |
|------------------------|
| Mechanical Engineering |
| Mathematics            |
| Civil Engineering      |
| Computer Science       |
+------------------------+
SELECT 4
Time: 0.004s

The result of this query is a distinct list of department names where codes start with ‘C‘ or ‘M‘. Let’s observe that the UNION operation removes the duplicate rows (Mechanical Engineering and Mathematics) that appeared in both queries, ensuring each row appears only once in the final result set.

2.2. Combining Results from Multiple Tables

Now, let’s demonstrate a more complex use case where UNION is used to bring together results from multiple tables using aliases for the columns to make the data set consistent. This is one of the key advantages of UNION.

Suppose we want to create a combined list of all departments and faculty names, identifying the type of entity (Department or Faculty):

SELECT name AS entity_name, 'Department' AS entity_type
 FROM Department
 UNION
 SELECT name AS entity_name, 'Faculty' AS entity_type
 FROM Faculty;
+--------------------------------+-------------+
| entity_name                    | entity_type |
|--------------------------------+-------------|
| Computer Science               | Department  |
| Mathematics                    | Department  |
| Mechanical Engineering         | Department  |
| Electronics and Communications | Department  |
| Anubha Gupta                   | Faculty     |
| Peter Pan                      | Faculty     |
| Civil Engineering              | Department  |
+--------------------------------+-------------+
SELECT 7
Time: 0.011s

The result of this query is a distinct list of all department and faculty names with their types.  We should note that Computer Science is not duplicated in the result even though it appears twice in the Department table. This example demonstrates how UNION can be used to merge results from different tables.

3. What is UNION ALL Operator

The UNION ALL operation in SQL combines the results of two or more SELECT queries into a single result set. However, unlike UNION, it does not remove duplicates.

The basic syntax for the UNION ALL operation is:

SELECT column1, column2, column3
FROM table1
UNION ALL
SELECT column1, column2, column3
FROM table2;

In this syntax, the UNION ALL operation combines the results of two SELECT queries on table1 and table2. The result set will include all rows from both tables, including duplicates.

3.1. Combining Results from Multiple Tables

For UNION ALL, suppose we want to create a combined list of all departments and faculty names, including duplicates, we use the queries:

SELECT name AS entity_name, 'Department' AS entity_type
 FROM Department
 UNION ALL
 SELECT name AS entity_name, 'Faculty' AS entity_type
 FROM Faculty;
+--------------------------------+-------------+
| entity_name                    | entity_type |
|--------------------------------+-------------|
| Computer Science               | Department  |
| Electronics and Communications | Department  |
| Mechanical Engineering         | Department  |
| Civil Engineering              | Department  |
| Mathematics                    | Department  |
| Anubha Gupta                   | Faculty     |
| Anubha Gupta                   | Faculty     |
| Anubha Gupta                   | Faculty     |
| Peter Pan                      | Faculty     |
| Peter Pan                      | Faculty     |
+--------------------------------+-------------+
SELECT 10
Time: 0.005s

The result of this query is a list of all department and faculty names with their types, including duplicates.

4. Key Differences Between UNION and UNION ALL

Let’s look at the key differences between the UNION and UNION ALL operations:

5. Conclusion

In this article, we’ve covered the UNION and UNION ALL operations in SQL, discussing their syntax, use cases, and key differences.

By understanding these operations and their appropriate use cases, we can optimize SQL queries for various data retrieval needs.