UNION

Grammar Description

The UNION operator allows you to merge two or more query result sets into one result set.

Grammar Structure

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

Syntax Description

`UNION` and `UNION ALL`

Using the UNION operator to combine the result sets of two or more queries, the following conditions are required:

The number and order of columns that appear in all SELECT statements must be the same.
The data type of the column must be the same or convertible.

With UNION ALL, duplicate rows (if available) will remain in the result. Because UNION ALL does not need to deal with duplicates.

`UNION` with `ORDER BY`, `LIMIT`

When using the ORDER BY or LIMIT clause to classify or limit all UNION results, a single SELECT statement should be parenthesed and ORDER BY or LIMIT is placed behind the last one.

For example:

(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);

or:

(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;

<!--The second example needs to be confirmed and cannot take effect for the time being->

Example

CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);

mysql> SELECT id FROM t1 UNION SELECT id FROM t2;
+------+
| id |
+------+
| 4 |
| 1 |
| 2 |
| 3 |
+------+

mysql> SELECT id FROM t1 UNION ALL SELECT id FROM t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 2 |
| 3 |
| 4 |
+------+

drop table t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9),ROW(10,1),ROW(11,2),ROW(13,5);
drop table t2;
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4),ROW(11,2),ROW(10,3),ROW(15,8);

mysql> (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);
+------+
| a |
+------+
| 10 |
| 11 |
+------+