MariaDB UNION Operator Explained

In MariaDB, the UNION operator combines the results from multiple SELECT statements into a single result set.

Syntax

The official syntax goes like this:

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

From MariaDB 10.4.0, parentheses can be used to specify precedence.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

+-----------+-------------+
| TeacherId | TeacherName |
+-----------+-------------+
|         1 | Warren      |
|         2 | Ben         |
|         3 | Cathy       |
|         4 | Cathy       |
|         5 | Bill        |
|         6 | Bill        |
+-----------+-------------+

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|         1 | Faye        |
|         2 | Jet         |
|         3 | Spike       |
|         4 | Ein         |
|         5 | Warren      |
|         6 | Bill        |
+-----------+-------------+

We can use the UNION operator to return all teachers and students:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Warren      |
| Ben         |
| Cathy       |
| Bill        |
| Faye        |
| Jet         |
| Spike       |
| Ein         |
+-------------+
8 rows in set (0.003 sec)

By default, the UNION operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default. So the above results contain just one each of Warren, Cathy and Bill. This is despite the fact that the combined tables actually contain two Warrens, two Cathys, and three Bills (there are two teachers called Cathy, a teacher and a customer called Warren, and two called Bill, as well as one student called Bill).

Here’s an example that explicitly uses the DISTINCT operator:

SELECT TeacherName FROM Teachers
UNION DISTINCT
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Warren      |
| Ben         |
| Cathy       |
| Bill        |
| Faye        |
| Jet         |
| Spike       |
| Ein         |
+-------------+
8 rows in set (0.004 sec)

So we get the same result that we got without the DISTINCT operator.

Include Duplicates

We can use the ALL keyword to include duplicate values in the results:

SELECT TeacherName FROM Teachers
UNION ALL
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Warren      |
| Ben         |
| Cathy       |
| Cathy       |
| Bill        |
| Bill        |
| Faye        |
| Jet         |
| Spike       |
| Ein         |
| Warren      |
| Bill        |
+-------------+
12 rows in set (0.002 sec)

This time we got twelve rows instead of the eight that we got in our first example.

We can see that both Cathys were returned and all three Bills were returned.