## TABLE in Unions 

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

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 VALUES  on the left hand side in a SELECT  and use aliases, like this: 

## UNION DISTINCT and UNION ALL 

SELECT * FROM (VALUES ROW(4,-2), ROW(5,9)) AS t(x,y) UNION TABLE t2; SELECT * FROM (VALUES ROW(4,-2), ROW(5,9)) AS t(x,y) UNION VALUES ROW(4,-2),ROW(5,9); 

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 the SELECT  statements. 

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 are used in the union. 

## ORDER BY and LIMIT in Unions 

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

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION 

