How to Use the UNION and UNION ALL Operators in MySQL

08/04/2021

Contents

In this article, you will learn how to use the UNION and UNION ALL operators in MySQL.

Using the UNION and UNION ALL operators in MySQL

The UNION and UNION ALL operators in MySQL allow us to combine the results of two or more SELECT statements into a single result set.

The UNION operator

The UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes any duplicate rows that appear in the result set. Here’s the basic syntax of the UNION operator:

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

In this example, we are combining the results of two SELECT statements from the “table1” and “table2” tables. The result set will contain unique rows from both tables.

The UNION ALL operator

The UNION ALL operator is similar to the UNION operator, but it includes all rows, including duplicates. Here’s the basic syntax of the UNION ALL operator:

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

In this example, we are combining the results of two SELECT statements from the “table1” and “table2” tables. The result set will contain all rows from both tables, including duplicates.

Example of using UNION and UNION ALL

Let’s look at an example to see how we can use the UNION and UNION ALL operators in MySQL. Suppose we have two tables: “employees” and “sales”. Here are the sample data for both tables:

employees
+----+-----------+------------+
| id | name      | department |
+----+-----------+------------+
| 1  | John Doe  | IT         |
| 2  | Jane Smith| HR         |
| 3  | Bob Brown | IT         |
+----+-----------+------------+

sales
+----+-----------+-------+
| id | name      | sales |
+----+-----------+-------+
| 1  | John Doe  | 1000  |
| 2  | Jane Smith| 2000  |
| 3  | Bob Brown | 3000  |
+----+-----------+-------+

We can use the UNION operator to combine the “name” and “sales” columns from both tables into a single result set, as follows:

SELECT name, NULL as department, NULL as sales
FROM employees
UNION
SELECT name, NULL as department, sales
FROM sales;

The result set will look like this:

+-----------+------------+-------+
| name      | department | sales |
+-----------+------------+-------+
| John Doe  | IT         | NULL  |
| Jane Smith| HR         | NULL  |
| Bob Brown | IT         | NULL  |
| John Doe  | NULL       | 1000  |
| Jane Smith| NULL       | 2000  |
| Bob Brown | NULL       | 3000  |
+-----------+------------+-------+

Notice that the “department” column is NULL for the second SELECT statement, and the “sales” column is NULL for the first SELECT statement.

We can use the UNION ALL operator to include all rows from both tables, including duplicates, as follows:

SELECT name, department, NULL as sales
FROM employees
UNION ALL
SELECT name, NULL as department, sales
FROM sales;

The result set will look like this:

+-----------+------------+-------+
| name      | department | sales |
+-----------+------------+-------+
| John Doe  | IT         | NULL  |
| Jane Smith| HR         | NULL  |
| Bob Brown | IT         | NULL  |
| John Doe  | NULL       | 1000  |
| Jane Smith| NULL       | 2000  |
| Bob Brown | NULL       | 3000  |
+-----------+------------+-------+
#MySQL