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

UNION combines the result from multiple query blocks into a single
result set. This example uses SELECT statements:

mysql> SELECT 1, 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
mysql> SELECT 'a', 'b';
+---+---+
| a | b |
+---+---+
| a | b |
+---+---+
mysql> SELECT 1, 2 UNION SELECT 'a', 'b';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| a | b |
+---+---+

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-result-set

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-table

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-distinct-all

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-order-by-lim
  it

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-restrictions

o https://dev.mysql.com/doc/refman/8.0/en/union.html#union-8-0-versus-5
  -7

Result Set Column Names and Data Types

The column names for a UNION result set are taken from the column names
of the first query block. Example:

mysql> CREATE TABLE t1 (x INT, y INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES ROW(4,-2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES ROW(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> TABLE t1 UNION TABLE t2;
+------+------+
| x    | y    |
+------+------+
|    4 |   -2 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE t2 UNION TABLE t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    4 |   -2 |
+------+------+
2 rows in set (0.00 sec)

Selected columns listed in corresponding positions of each query block
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
result 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
query blocks. Consider the following, where the column length is not
constrained to the length of the value from the first SELECT statement:

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

TABLE and VALUES Statements in Unions

Beginning with MySQL 8.0.19, you can also use a TABLE statement or
VALUES statement in a UNION wherever you can employ the equivalent
SELECT statement. Assume that tables t1 and t2 are created and
populated as shown here:

CREATE TABLE t1 (x INT, y INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);

The preceding being the case, and disregarding the column names in the
output of the queries beginning with VALUES, all of the following UNION
queries yield the same result:

SELECT * FROM t1 UNION SELECT * FROM t2;
TABLE t1 UNION SELECT * FROM t2;
VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
SELECT * FROM t1 UNION TABLE t2;
TABLE t1 UNION TABLE t2;
VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);

To force the column names to be the same, wrap the query block on the
left-hand side in a SELECT statement, and use aliases, like this:

mysql> SELECT * FROM (TABLE t2) AS t(x,y) UNION TABLE t1;
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

UNION DISTINCT and UNION ALL

By default, duplicate rows are removed from UNION results. The optional
DISTINCT keyword has the same effect but makes it explicit. With the
optional ALL keyword, duplicate-row removal does not occur and the
result includes all matching rows from all queries in the union.

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.

In MySQL 8.0.19 and later, UNION ALL and UNION DISTINCT work the same
way when one or more TABLE statements, VALUES statements, or both, are
used in the union.

ORDER BY and LIMIT in Unions

To apply an ORDER BY or LIMIT clause to an individual SELECT statement,
parenthesize the SELECT statement and place the clause inside the
parentheses:

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

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, ORDER BY in this
context typically is used in conjunction with LIMIT, 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 has no effect in any case.

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:

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

Beginning with MySQL 8.0.19, you can use ORDER BY and LIMIT with TABLE
in unions in the same way as just shown, bearing in mind that TABLE
does not support a WHERE clause. This is also true for VALUES ROW(), as
shown in this example using the mysql client:

mysql> VALUES ROW(4,-2), ROW(5,9), ROW(-1,3) 
    -> UNION 
    -> VALUES ROW(1,2), ROW(3,4), ROW(-1,3) 
    -> ORDER BY column_0 DESC LIMIT 3;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        5 |        9 |
|        4 |       -2 |
|        3 |        4 |
+----------+----------+
3 rows in set (0.00 sec)

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 query block, and refer to the alias in the
ORDER BY clause. (You can also refer to the column in the ORDER BY
clause using its column position, but such use of column positions is
deprecated, and thus subject to eventual removal in a future MySQL
release.)

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
is permitted, but the second fails 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 query block one after the other, select an additional
column in each query block to use as a sort column and add an ORDER BY
clause that sorts on that column following the last query block:

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

To maintain sort order within individual 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 query
block each row comes from. Extra columns can provide other identifying
information as well, such as a string that indicates a table name.

UNION Restrictions

In a UNION, the SELECT statements have the following restrictions:

o HIGH_PRIORITY in the first SELECT has no effect. HIGH_PRIORITY in any
  subsequent SELECT produces a syntax error.

o Only the last SELECT statement can use an INTO clause. However, the
  entire UNION result is written to the INTO output destination.

As of MySQL 8.0.20, these two UNION variants containing INTO are
deprecated and you should expect support for them to be removed in a
future version of MySQL:

o In the trailing query block of a query expression, use of INTO before
  FROM produces a warning. Example:

... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;

o In a parenthesized trailing block of a query expression, use of INTO
  (regardless of its position relative to FROM) produces a warning.
  Example:

... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);

  Those variants are deprecated because they are confusing, as if they
  collect information from the named table rather than the entire query
  expression (the UNION).

UNION queries with an aggregate function in an ORDER BY clause are
rejected with ER_AGGREGATE_ORDER_FOR_UNION
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
#error_er_aggregate_order_for_union). Example:

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

UNION Handing in MySQL 8.0 Compared to MySQL 5.7

In MySQL 8.0, the parser rules for SELECT and UNION were refactored to
be more consistent (the same SELECT syntax applies uniformly in each
such context) and reduce duplication. Compared to MySQL 5.7, several
user-visible effects resulted from this work, which may require
rewriting of certain statements:

o NATURAL JOIN permits an optional INNER keyword (NATURAL INNER JOIN),
  in compliance with standard SQL.

o Right-deep joins without parentheses are permitted (for example, ...
  JOIN ... JOIN ... ON ... ON), in compliance with standard SQL.

o STRAIGHT_JOIN now permits a USING clause, similar to other inner
  joins.

o The parser accepts parentheses around query expressions. For example,
  (SELECT ... UNION SELECT ...) is permitted. See also [HELP
  parenthesized query expressions].

o The parser better conforms to the documented permitted placement of
  the SQL_CACHE and SQL_NO_CACHE query modifiers.

o Left-hand nesting of unions, previously permitted only in subqueries,
  is now permitted in top-level statements. For example, this statement
  is now accepted as valid:

(SELECT 1 UNION SELECT 1) UNION SELECT 1;

o Locking clauses (FOR UPDATE, LOCK IN SHARE MODE) are allowed only in
  non-UNION queries. This means that parentheses must be used for
  SELECT statements containing locking clauses. This statement is no
  longer accepted as valid:

SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;

  Instead, write the statement like this:

(SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);

URL: https://dev.mysql.com/doc/refman/8.0/en/union.html

