UNION is used to combine the results from multiple SELECT
statements into a single result set.
 
Syntax
------ 
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
 
Description
----------- 
UNION is used to combine the results 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 they don't, the type and length of the columns in the
result take into account the values returned by all of the
SELECTs, so there is no need for explicit casting. Note that
currently this is not the case for recursive CTEs - see
MDEV-12325.
 
Table names can be specified as db_name.tbl_name. This
permits writing UNIONs which involve multiple databases. See
Identifier Qualifiers for syntax details.
 
UNION queries cannot be used with aggregate functions.
 
ALL/DISTINCT
 
The ALL keyword causes duplicate rows to be preserved. The
DISTINCT keyword (the default if the keyword is omitted)
causes duplicate rows to be removed by the results.
 
UNION ALL and UNION DISTINCT can both be present in a query.
In this case, UNION DISTINCT will override any UNION ALLs to
its left.
 
Until MariaDB 10.1.1, all UNION ALL statements required the
server to create a temporary table. Since MariaDB 10.1.1,
the server can in most cases execute UNION ALL without
creating a temporary table, improving performance (see
MDEV-334).
 
ORDER BY and LIMIT
 
Individual SELECTs can contain their own ORDER BY and LIMIT
clauses. In this case, the individual queries need to be
wrapped between parentheses. However, this does not affect
the order of the UNION, so they only are useful to limit the
record read by one SELECT.
 
The UNION can have global ORDER BY and LIMIT clauses, which
affect the whole resultset. If the columns retrieved by
individual SELECT statements have an alias (AS), the ORDER
BY must use that alias, not the real column names.
 
HIGH_PRIORITY
 
Specifying a query as HIGH_PRIORITY will not work inside a
UNION. If applied to the first SELECT, it will be ignored.
Applying to a later SELECT results in a syntax error:
 
ERROR 1234 (42000): Incorrect usage/placement of
'HIGH_PRIORITY'
 
SELECT ... INTO ...
 
Individual SELECTs cannot be written INTO DUMPFILE or INTO
OUTFILE. If the last SELECT statement specifies INTO
DUMPFILE or INTO OUTFILE, the entire result of the UNION
will be written. Placing the clause after any other SELECT
will result in a syntax error.
 
If the result is a single row, SELECT ... INTO @var_name can
also be used.
 

Parentheses
 
From MariaDB 10.4.0, parentheses can be used to specify
precedence. Before this, a syntax error would be returned.
 
Examples
-------- 
UNION between tables having different column names:
 
(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);
 
Specifying the UNION's global order and limiting total
rows:
 
(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;
 
Adding a constant row:
 
(SELECT 'John Doe' AS name, 'john.doe@example.net' AS
email)
UNION
(SELECT name, email FROM customers);
 
Differing types:
 
SELECT CAST('x' AS CHAR(1)) UNION SELECT REPEAT('y',4);
+----------------------+
| CAST('x' AS CHAR(1)) |
+----------------------+
| x |
| yyyy |
+----------------------+
 
Returning the results in order of each individual SELECT by
use of a sort column:
 
(SELECT 1 AS sort_column, e_name AS name, email FROM
employees)
UNION
(SELECT 2, c_name AS name, email FROM customers) ORDER BY
sort_column;
 
Difference between UNION, EXCEPT and INTERSECT:
 
CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(3),(4),(5),(6);
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 1 |
| 2 |
+------+
 
SELECT i FROM seqs WHERE i =3;
 
+------+
| i |
+------+
| 3 |
+------+
 
Parentheses for specifying precedence, from MariaDB 10.4.0
 
CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (b INT);
CREATE OR REPLACE TABLE t3 (c INT);
 
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (5),(6);
INSERT INTO t3 VALUES (1),(6);
 
((SELECT a FROM t1) UNION (SELECT b FROM t2)) INTERSECT
(SELECT c FROM t3);
+------+
| a |
+------+
| 1 |
| 6 |
+------+
 
(SELECT a FROM t1) UNION ((SELECT b FROM t2) INTERSECT
(SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+------+
 


URL: https://mariadb.com/kb/en/union/