# **UNION**

## **Grammar Description**

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

## **Grammar Structure**

```sql
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:

```text
(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:

```text
(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**

```text
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 |
+------+
```

```text
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 |
+------+
```