Menu

MySQL UNION

Learn MySQL UNION syntax, duplicate handling, result ordering, and the INTERSECT and EXCEPT operators added in MySQL 8.0.31.

Updated on

In MySQL, UNION combines rows from two or more query results into one result set. By default, it removes duplicate rows; UNION ALL keeps them.

MySQL 8.0.31 and later also support INTERSECT and EXCEPT. INTERSECT returns rows present in both query results; EXCEPT returns rows from the left query that are absent from the right. Both remove duplicates by default and accept ALL to retain duplicate occurrences. In mixed expressions, MySQL evaluates INTERSECT before UNION or EXCEPT; use parentheses when the intended grouping should be explicit. MySQL uses EXCEPT; it does not support the MINUS spelling used by Oracle. See the MySQL set-operations reference and the MySQL 8.0.31 release notes.

For example, INTERSECT keeps a value found in both queries, while EXCEPT keeps a value only from the left query:

SELECT 1 AS value
INTERSECT
SELECT 1 AS value;

SELECT 1 AS value
EXCEPT
SELECT 2 AS value;

Both queries return the single row 1. These operators are available in MySQL 8.0.31 and later; use UNION for earlier versions.

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)

In the global ORDER BY, refer to the combined result’s output columns rather than a source-table name. If you see Error 1250 for this restriction, see MySQL Error 1250: UNION ORDER BY Cannot Reference Source Tables.

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.

For a checklist and examples that fix this failure, see MySQL Error 1222: UNION SELECTs Return Different Column Counts.

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.