Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

13.2.9.3 UNION Syntax

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

UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements. For example, consider the following:

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

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

  • Only the last SELECT statement can use INTO OUTFILE. (However, the entire UNION result is written to the file.)

  • HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION. If you specify it for the first SELECT, it has no effect. If you specify it for any subsequent SELECT statements, a syntax error results.

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

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

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

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

A statement without parentheses is equivalent to one parenthesized as just shown.

This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY. (Alternatively, refer to the column in the ORDER BY using its column position. However, use of column positions is deprecated.)

Also, if a column to be sorted is aliased, the ORDER BY clause must refer to the alias, not the column name. The first of the following statements will work, but the second will fail with an Unknown column 'a' in 'order clause' error:

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

To additionally maintain sort order within individual SELECT results, add a secondary column to the ORDER BY clause:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

Use of an additional column also enables you to determine which SELECT each row comes from. Extra columns can provide other identifying information as well, such as a string that indicates a table name.

UNION queries with an aggregate function in an ORDER BY clause are rejected with an ER_AGGREGATE_ORDER_FOR_UNION error. Example:

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Sait Karalar on September 30, 2010
To select all records in different tables with table name
select *, 'table1' as tblName from table1 UNION ALL SELECT *, 'table2' as tblName FROM table2;

If your tables has different fields than

select field_1, field_2, 'table1' as tblName from table1 UNION SELECT field_3, field_4, 'table2' as tblName FROM table2;

These selects and unions "ALL" records in two different table and ads a tblName FIELD as table name at the end such that you may know the records comes from which table.

If you want unique records, do not use tblName, since it differs each row from other table and you never get unique record. No need to use UNION ALL in this case.

Note that, tblName and any other fix data should be added to end in select.
  Posted by Lalit P on December 20, 2011
There is an issue was in the way that MySQL parses executes the queries with UNION. When you do a UNION of two tables in MySQL, the MySQL engine just appends the second data-set to the first data set without matching the column names or warning if the data types of corresponding columns are not same.
Although the official documentation does talk about being data-type agnostic, it doesn’t mention about column name being in same order in all the queries.

Example:
Lets take this example of a table.
CREATE TABLE users (
id SMALLINT NULL,
first_name VARCHAR(50) NULL,
last_name VARCHAR(50) NULL,
points SMALLINT NULL,
level SMALLINT NULL
);
+------+------------+-----------+--------+-------+
| id | first_name | last_name | points | level |
+------+------------+-----------+--------+-------+
| 1 | Lalit | Patel | 5 | 50 |
| 2 | Chuck | Norris | 10 | 100 |
+------+------------+-----------+--------+-------+
2 rows in set (0.00 sec)

The following query runs without any error and it gives the result set given below. Note that it takes the column names in the order they were specified in the first query, but mixes the values up.

(SELECT id, first_name, last_name, level, points FROM users WHERE id = 1)
UNION
(SELECT id, last_name, first_name, points, level FROM users WHERE id = 2);

+------+------------+-----------+-------+--------+
| id | first_name | last_name | level | points |
+------+------------+-----------+-------+--------+
| 1 | Lalit | Patel | 50 | 5 |
| 2 | Norris | Chuck | 10 | 100 |
+------+------------+-----------+-------+--------+
2 rows in set (0.00 sec)

If you mix up the order of the column names, the resulting data you get will be incorrect or at least not what you would be expecting in your code. MySQL will return the result as long as the number of columns are same in both the queries. A bit too much forgiving.

  Posted by Goran Ambrosic on October 26, 2012
I had one more example uf usage of an union feature.
We have to split a couple of our big database tables (>2TB) between the two servers/instances.
After the split of the tables, the structure of the tables remains the same.

First I took the data (in PHP) from one instance and I've build the result as a union (string) and put it to another instance to get finally the merged results.

Here the explanational (dummy) SQL:

SELECT
DATE_FORMAT(DATE_ADD(TIMESTAMP, INTERVAL 45 MINUTE) , '%Y-%m-%d %H:00:00') AS TIMESTAMP
, ROUND(SUM(E_Z_EVU), 3) AS E_Z_EVU
FROM (
SELECT '2012-09-01 08:00:00' AS TIMESTAMP, 0.0317 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:00:00' AS TIMESTAMP, 0.0316 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:15:00' AS TIMESTAMP, 0.0321 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:15:00' AS TIMESTAMP, 0.0319 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:30:00' AS TIMESTAMP, 0.0320 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:30:00' AS TIMESTAMP, 0.0319 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:45:00' AS TIMESTAMP, 0.0323 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:40:00' AS TIMESTAMP, 0.0322 AS E_Z_EVU
) A
UNION
SELECT TIMESTAMP, E_Z_EVU
FROM archive_DB.SOME_TABLE
WHERE TIMESTAMP BETWEEN '2012-08-31 09:00:00' AND '2012-09-01 08:59:59'
GROUP BY DATE_FORMAT(DATE_ADD(TIMESTAMP, INTERVAL 45 MINUTE), '%Y-%m-%d %H:00:00')
ORDER BY TIMESTAMP

and here the example output:
+---------------------+---------+
| TIMESTAMP | E_Z_EVU |
+---------------------+---------+
| 2012-08-31 09:00:00 | 0.0044 |
| 2012-08-31 09:15:00 | 0.0034 |
| 2012-08-31 10:15:00 | 0.0039 |
| 2012-08-31 11:15:00 | 0.0181 |
| 2012-08-31 12:15:00 | 0.04 |
| 2012-08-31 13:15:00 | 0.0527 |
| 2012-08-31 14:15:00 | 0.0605 |
| 2012-08-31 15:15:00 | 0.0566 |
| 2012-08-31 16:15:00 | 0.0156 |
| 2012-08-31 17:15:00 | 0.0195 |
| 2012-08-31 18:15:00 | 0.0112 |
| 2012-08-31 19:15:00 | 0.001 |
| 2012-08-31 20:15:00 | 0 |
| 2012-08-31 21:15:00 | 0 |
| 2012-08-31 22:15:00 | 0 |
| 2012-08-31 23:15:00 | 0 |
| 2012-09-01 08:00:00 | 0.256 |
+---------------------+---------+

  Posted by Ilan Hazan on November 7, 2012
By using UNION ALL one can optimize the MySQL IN() Comparison Operations Which Include the Indexed Field.
See http://www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/
  Posted by Gianluigi Zanettini on August 21, 2014
Please be aware that if you don't add parenthesis to the query at all, the LIMIT clause will apply to the query as a WHOLE.

SELECT * FROM tbl1 WHERE user_id (1,2) UNION SELECT * FROM tbl2 WHERE user_id > 1000 LIMIT 0,50 => this will extract 2 plus 48 records.

As another thest, I run the same but with LIMIT 1,50 => this extracted 1 plus 49 records.
Sign Up Login You must be logged in to post a comment.