Author: Zhang Wei
Member of DBA team of aikesheng Beijing Branch, responsible for MySQL daily problem handling and DMP product maintenance. Love technology and open source database, sports, reading, movies, flowers and trees.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
1, Function of UNION:
UNION can combine the results of multiple SELECT query statements into one result set. Some new functions have been added in MySQL 8.0. Let's take a look.
2, Pattern display of UNION operator:
1. Wen Gu
- Union must consist of two or more SELECT statements to use 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.
- The column data types must be compatible, that is, implicit type conversion can be performed, but SQL performance problems may occur. It is recommended that the data types be the same.
Examples are as follows:
create table t1 (id int, name varchar(20)); insert into t1 values(1,"Love can live"),(2,"Open Source"),(3,"community"),(5,"MySQL"),(4,"Zhang"); create table t2 (id int, name varchar(20)); insert into t2 values(3,"China"),(11,"technology"),(15,"Open Source"),(2,"community"),(1,"great"); 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: deletes duplicate data in the result set. 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) // By default, the UNION operator has deleted duplicate data, so the DISTINCT modifier has no effect on the result. 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: returns ALL result sets including duplicate data. 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 using union combined query, only one order by clause can be used to sort the result set, and it must appear after the last SELECT statement. Because Union does not allow sorting of some result sets, it can only sort the final retrieved result set.
Note: the table fields may not be the same when querying multiple tables. Therefore, the retrieved common fields need to be used when sorting the result set. The retrieved field id must exist in the result set.
Examples are as follows:
2. Zhixin
- Starting from MySQL version 8.0.19, UNION can be used not only in SELECT statements, but also in TABLE or VALUES statements, as long as equivalent SELECT statements can be used.
- ORDER BY and LIMIT can be used in TABLE, but the WHERE clause is not supported.
- The. By table name cannot contain. Order A reference to a column name. Instead, provide the column alias in the first SELECT statement and reference the alias 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.
Examples are as follows:
// The use of the new table statement is inconvenient for the de duplication of a single field because it takes the whole table mysql> table t1 union select * from t2; +------+-----------+ | id | name | +------+-----------+ | 1 | Love can live | | 2 | Open Source | | 3 | community | | 5 | MySQL | | 4 | Zhang | | 3 | China | | 11 | technology | | 15 | Open Source | | 2 | community | | 1 | great | +------+-----------+ 10 rows in set (0.01 sec) // Add the use of values statement, but it will invalidate the field name mysql> values row(15,'Open Source') union select * from t2; +----------+----------+ | column_0 | column_1 | +----------+----------+ | 15 | Open Source | | 3 | China | | 11 | technology | | 2 | community | | 1 | great | +----------+----------+ 5 rows in set (0.01 sec) // Use the combination of table statement and values statement mysql> values row(15,'Open Source'),row(13,'Beijing') union table t2; +----------+----------+ | column_0 | column_1 | +----------+----------+ | 15 | Open Source | | 13 | Beijing | | 3 | China | | 11 | technology | | 2 | community | | 1 | great | +----------+----------+ 6 rows in set (0.01 sec) // If you define a field name, you can use the following methods mysql > select * from (values row(15,'Open Source'),row(13,'Beijing')) AS t(c1,c2) union table t2; +------+--------+ | c1 | c2 | +------+--------+ | 15 | Open Source | | 13 | Beijing | | 3 | China | | 11 | technology | | 2 | community | | 1 | great | +------+--------+ 6 rows in set (0.02 sec)
3, Compare the processing of union in MySQL 8.0 and 5.7
In MySQL 8.0, the parser rules for SELECT and UNION are reconstructed to become more consistent and reduce duplication.
Compared with MySQL 5.7, some statements may need to be rewritten:
- Compared with standard SQL, NATURAL JOIN allows an optional INNER keyword (NATURAL INNER JOIN).
- Compared with standard SQL, you can use right deep JOIN without brackets (for example,... JOIN... JOIN... ON... ON).
- Similar to other inner joins, strong_ Join now allows the USING clause.
- The parser accepts parentheses around the query expression. For example: (select... Union select...).
- Previously, only the left nesting of union was allowed in subqueries, but now it is allowed in top-level statements. For example: (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);