MySQL UNION

In this article, we will discuss the syntax and useage of MySQL UNION operator.

In MySQL, the UNION operator is a set operator, which is used to merge all rows from two result sets into one result set.

There are three set operators: UNION, INTERSECT, and MINUS defined in SQL standard, but MySQL only supports UNION now.

UNION syntax

The UNION operator is used to combine two result sets of SELECT statements. The syntax of the UNION operator is as follows:

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement

Here:

  • UNION is a binary operator and it require two SELECT statements as operands.
  • In the two SELECT statements, the number and order columns must be the same.
  • UNION DISTINCT will remove duplicate rows and return a unique result set, and UNION ALL will return all rows from the two result sets.
  • The DISTINCT keyword in UNION DISTINCT can be omited.

UNION examples

Create table for testing

In the following examples, we create a, b and c tables as demonstrations.

Create table and insert test rows:

CREATE TABLE a (v INT);
CREATE TABLE b (v INT);
CREATE TABLE c (v INT);

INSERT INTO a VALUES (1), (2), (NULL), (NULL);
INSERT INTO b VALUES (2), (2), (NULL);
INSERT INTO c VALUES (3), (2);

The rows in table a:

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+
4 rows in set (0.00 sec)

The rows in b table:

+------+
| v    |
+------+
|    2 |
|    2 |
| NULL |
+------+
3 rows in set (0.00 sec)

The rows in c table:

+------+
| v    |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

UNION example

The following statement combins all rows from a and b tables using the UNION operator:

SELECT * FROM a
UNION
SELECT * FROM b;

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
+------+
3 rows in set (0.00 sec)

You can see that the output result set does not include duplicate rows, because the UNION operator remove all duplicate row as same as UNION DISTINCT.

UNION It is UNION DISTINCT shorthand.

You can also combine three tables using UNION operator.

SELECT * FROM a
UNION
SELECT * FROM b
UNION
SELECT * FROM c;

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
|    3 |
+------+
4 rows in set (0.00 sec)

UNION ALL

The following statement combins all rows from a and b tables using the UNION ALL operator:

SELECT * FROM a
UNION ALL
SELECT * FROM b;

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
|    2 |
|    2 |
| NULL |
+------+
7 rows in set (0.00 sec)

You can see that the output result set includes all rows from a and b tables.

UNION and UNION ALL

Let us see the following example:

SELECT * FROM a
UNION
SELECT * FROM b
UNION ALL
SELECT * FROM c;

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
|    3 |
|    2 |
+------+
5 rows in set (0.00 sec)

Here is the execution steps of this example:

  1. First, combine the rows from a and b tables using the UNION operator. It returns a unique result set from a and b tables.
  2. Second, combine the result set of the first step and c table using the UNION ALL operator.

UNION and ORDER BY

To sort the rows return from the UNION operator, you can use ORDER BY clause after UNION statement.

The following statement combins all rows from a and b tables using the UNION ALL operator and sorts the rows in ascending order:

SELECT * FROM a
UNION ALL
SELECT * FROM b
ORDER BY v;

+------+
| v    |
+------+
| NULL |
| NULL |
| NULL |
|    1 |
|    2 |
|    2 |
|    2 |
|    3 |
+------+
8 rows in set (0.01 sec)

UNION columns

The two SELECT statements of the UNION operator must have the same number of columns, otherwise an error will occur.

Let us try to run the following statement:

SELECT 1
UNION
SELECT 2, 3;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Because SELECT 1 have one column, but SELECT 2, 3 have two columns. The number of columns in the two statements are different, which leads to an error.

UNION column name

The column names for a UNION result set are taken from the column names of the first SELECT statement.

Let us take a look at the two result set of the UNION operator:

SELECT 1;

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

This first result set has only one column, and the column name is 1.

SELECT 2;

+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

This second result set has only one column, and the column name is 2.

Let us combine the two result sets using UNION operator:

SELECT 1 UNION SELECT 2;

+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

The column name for the UNION result set is as same as SELECT 1 because SELECT 1 is the first SELECT statement.

Now let us exchange the two result sets in UNION statement:

SELECT 2 UNION SELECT 1;

+---+
| 2 |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)

The column name for the UNION result set is as same as SELECT 2 because SELECT 2 is the first SELECT statement.

Then, if we want to use a column alias, we just need to set an alias for the column of the first SELECT statement， like this:

SELECT 2 AS c
UNION
SELECT 1;

+---+
| c |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)

Conclusion

In this article, you learned the syntax and use cases of the UNION operator. The following are the key points of the UNION operator:

  • The UNION operator is used to combine two result sets into one.
  • The UNION operator includes UNION DISTINCT and UNION ALL two algorithms, which UNION DISTINCT can be abbreviated UNION.
  • UNION removes duplicate rows in the two result set, and UNION ALL then retain all rows.
  • The two result sets in a UNION must have same number of columns.
  • The column names for a UNION result set are taken from the column names of the first SELECT statement.
  • You may be used ORDER BY to sort the result of a UNION.