Understanding and Using UNION in MySQL 8.0

1. Purpose of UNION

UNION merges the result sets of two or more SELECT statements into a single result set. MySQL 8.0 adds new capabilities that are demonstrated below.

2. UNION Syntax and Rules

Recall

UNION requires at least two SELECT statements.

Each SELECT must return the same number of columns with compatible data types; column names or aliases must match.

Implicit type conversion is allowed, but using identical data types avoids performance issues.

Example tables:

create table t1 (id int, name varchar(20));
insert into t1 values(1,"爱可生"),(2,"开源"),(3,"社区"),(5,"MySQL"),(4,"张");
create table t2 (id int, name varchar(20));
insert into t2 values(3,"中国"),(11,"技术"),(15,"开源"),(2,"社区"),(1,"伟大");

-- SELECT from each table
select id from t1;
select id from t2;

-- UNION DISTINCT (default) removes duplicates
select id from t1 union distinct select id from t2;

-- UNION (same as DISTINCT) – duplicates already removed
select id from t1 union select id from t2;

-- UNION ALL keeps duplicates
select id from t1 union all select id from t2;

When using UNION, only one ORDER BY clause is allowed, and it must appear after the final SELECT. The ORDER BY can reference only columns that exist in the combined result set.

New Features in MySQL 8.0.19

UNION can now be used inside TABLE or VALUES statements, provided the underlying SELECT is valid.

TABLE statements support ORDER BY and LIMIT, but not WHERE.

ORDER BY cannot reference qualified column names; use column aliases defined in the first SELECT.

Examples:

// Using TABLE with UNION
mysql> table t1 union select * from t2;

// Using VALUES with UNION (column names become generic)
mysql> values row(15,'开源') union select * from t2;

// Combining VALUES and TABLE
mysql> values row(15,'开源'),row(13,'北京') union table t2;

// Defining column names for VALUES
mysql> select * from (values row(15,'开源'),row(13,'北京')) AS t(c1,c2) union table t2;

3. Comparison Between MySQL 8.0 and 5.7

MySQL 8.0 refactors the parser for SELECT and UNION, making the behavior more consistent and reducing duplication. Notable differences include:

Optional NATURAL INNER JOIN is now allowed.

Right‑deep JOIN syntax without parentheses is supported.

STRAIGHT_JOIN now permits a USING clause.

Parentheses around query expressions are accepted, e.g., (SELECT ... UNION SELECT ...) .

Top‑level UNION nesting is allowed, e.g., (SELECT 1 UNION SELECT 1) UNION SELECT 1 .

SELECT statements with locking clauses must be parenthesized, e.g., (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE) .

4. Reference Documentation

Official MySQL documentation for UNION: https://dev.mysql.com/doc/refman/8.0/en/union.html