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;