SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

UNION is implemented in MySQL 4.0.0.

UNION is used to combine the result from many SELECT statements into one result set.

The columns listed in the select_expression portion of the SELECT should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned.

The SELECT commands are normal select commands, but with the following restrictions:

* Only the last SELECT command can have INTO OUTFILE.

If you don't use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you will get all matching rows from all the used SELECT statements.

The DISTINCT keyword is an optional word (introduced in MySQL 4.0.17) that does nothing. But it is required by the SQL standard.

If you want to use an ORDER BY for the total UNION result, you should use parentheses:

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

Note: You cannot mix UNION ALL and UNION DISTINCT in the same query yet. If you use ALL for one UNION then it is used for all of them.

The types and lengths of the columns in the result set of a UNION take into acccount the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT were used to determine result types and lengths. This could result in value truncation if, for example, the second SELECT retrieved longer values than the first SELECT:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| b |
+---------------+

That limitation has been removed as of MySQL 4.1.1:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+

back to MySQL reference