Interpretation of new features | MySQL 8.0's improvements to UNION

Author: Zhang Wei

A member of the DBA team of Acsen Beijing Branch, responsible for MySQL daily problem handling and DMP product maintenance. Love technology and open source databases, love sports, reading, movies, flowers and trees.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

1. The role of UNION:

UNION can combine the results of multiple SELECT query statements into a result set, and some new functions have been added in MySQL 8.0. Let's take a look.

Second, the pattern display of the UNION operator:

1. Review the past

• A Union must consist of two or more SELECT statements in order to use a Union connection.
• Each query in Union must contain the same number of columns or aggregate functions, and the column names or aliases must be consistent.
• Column data types must be compatible, i.e. implicit type conversions are possible, but SQL performance issues may arise, it is recommended that the data types be the same.

An example is as follows:

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,"伟大");
   
mysql> select id from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
+------+
5 rows in set (0.00 sec)
  
mysql> select id from t2;
+------+
| id   |
+------+
|    3 |
|   11 |
|   15 |
|    2 |
|    1 |
+------+
5 rows in set (0.00 sec)
  
// DISTINCT: 删除结果集中重复的数据。
mysql> select id from t1 union distinct select id from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|   11 |
|   15 |
+------+
7 rows in set (0.00 sec)
  
// 默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
mysql> select id from t1 union select id from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|   11 |
|   15 |
+------+
7 rows in set (0.00 sec)
  
  
// ALL: 返回包括重复数据在内的所有结果集。
mysql> select id from t1 union all select id from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|    3 |
|   11 |
|   15 |
|    2 |
|    1 |
+------+
10 rows in set (0.00 sec)

When combining queries with Union, only one order by clause can be used to sort the result set, and it must appear after the last occurrence of the SELECT statement. Because Union does not allow sorting of partial result sets, only the final retrieved result set can be sorted.

Note: Due to the multi-table combined query, the table fields may not be the same. Therefore, the retrieved common fields need to be used when sorting the result set. The retrieved field id must exist in the result set.

An example is as follows:

2. Know the new

• Beginning with MySQL version 8.0.19, UNION can be used not only in SELECT statements, but also in TABLE or VALUES statements, as long as the equivalent SELECT statement can be used.
• ORDER BY and LIMIT can be used in TABLE, but WHERE clauses are not supported.
• ORDER BY cannot use a reference containing tablename.columnname. Instead, provide column aliases in the first SELECT statement and reference the aliases in ORDER BY.
• If the column to be sorted has an alias, the ORDER BY clause must refer to the alias, not the column name.

An example is as follows:

// 新增 table 语句的使用，由于取的是全表，对于单一字段的去重就不便使用了
mysql> table t1 union select * from t2;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 爱可生    |
|    2 | 开源      |
|    3 | 社区      |
|    5 | MySQL     |
|    4 | 张        |
|    3 | 中国      |
|   11 | 技术      |
|   15 | 开源      |
|    2 | 社区      |
|    1 | 伟大      |
+------+-----------+
10 rows in set (0.01 sec)
   
// 新增 values 语句的使用，但会导致字段名失效
mysql> values row(15,'开源') union select * from t2;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|       15 | 开源     |
|        3 | 中国     |
|       11 | 技术     |
|        2 | 社区     |
|        1 | 伟大     |
+----------+----------+
5 rows in set (0.01 sec)
  
// 使用table 语句和values 语句的结合
mysql> values row(15,'开源'),row(13,'北京') union table t2;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|       15 | 开源     |
|       13 | 北京     |
|        3 | 中国     |
|       11 | 技术     |
|        2 | 社区     |
|        1 | 伟大     |
+----------+----------+
6 rows in set (0.01 sec)
  
  
// 如果定义字段名可以使用以下方法
mysql > select * from (values row(15,'开源'),row(13,'北京')) AS t(c1,c2) union table t2;
+------+--------+
| c1   | c2     |
+------+--------+
|   15 | 开源   |
|   13 | 北京   |
|    3 | 中国   |
|   11 | 技术   |
|    2 | 社区   |
|    1 | 伟大   |
+------+--------+
6 rows in set (0.02 sec)

3. Comparing MySQL 8.0 and 5.7's processing of union

In MySQL 8.0, the parser rules for SELECT and UNION were refactored to be more consistent and to reduce duplication.

Compared to MySQL 5.7, some statements may need to be rewritten:

• In contrast to standard SQL, NATURAL JOIN allows an optional INNER keyword (NATURAL INNER JOIN).
• In contrast to standard SQL, a Right-deep JOIN can be used without parentheses (eg, …JOIN…JOIN…ON…ON).
• Similar to other INNER JOINs, STRAIGHT_JOIN now allows a USING clause.
• The parser accepts parentheses around query expressions. For example: (SELECT...UNION SELECT...).
• Previously only left nesting of unions was allowed in subqueries, now it is allowed in top-level statements. Such as: (SELECT 1 UNION SELECT 1) UNION SELECT 1 ;
• SELECT statements containing locking clauses must use parentheses. For example: SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE; change to (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);

4. Reference documents:

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