The method of merging the query results of multiple select statements in MySQL

The following brings you about the method of merging the query results of multiple select statements in MySQL. I believe you must have seen similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after reading the method of merging the query results of multiple select statements in MySQL.

1. Background

* the full merge query result is to merge the query results of multiple select statements together.

* the result set that participates in the merge requires field unification.

The * field can be replaced with an empty string''.

two。 Practice of merging query results [users1 and users2]

* View users1 table and users2 table structure

Mysql > desc users1 +-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | bigint (20) | NO | PRI | NULL | auto_increment | | name | varchar (64) | NO | | NULL | | sex | enum ('M') 'F') | NO | | NULL | age | int (11) | NO | | NULL | | +-+-+ 4 rows in set (0.00 sec) mysql > desc users2 +-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | bigint (20) | NO | PRI | NULL | auto_increment | | name | varchar (64) | NO | | NULL | | sex | enum ('M') 'F') | NO | | NULL | age | int (11) | NO | | NULL | | +-+-+ 4 rows in set (0.01 sec)

* View users1 and users2 data

Users1 and users2 tables have the same field tom

Mysql > select * from users1;+----+ | id | name | sex | age | +-- + | 1 | tom | M | 25 | 2 | jak | F | 42 | +-+ 2 rows in set (0.00 sec) mysql > select * from users2 +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | | 2 | lisea | M | 42 | +-+ 2 rows in set (0.00 sec)

* union merge and de-duplicate

Mysql > (select * from users1) union (select * from users2) +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 42 | 2 | lisea | M | 42 | +-+ 3 rows in set (0.00 sec)

* union all is only not heavy.

Mysql > (select * from users1) union all (select * from users2) +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 42 | 1 | tom | M | 25 | 2 | lisea | M | 42 | +-+ 4 rows in set (0.01sec)

* View union performance analysis

[temporary tables are used]

Mysql > explain (select * from users1) union (select * from users2) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | users1 | NULL | ALL | NULL | 2 | 100.00 | NULL | | 2 | UNION | users2 | NULL | ALL | NULL | | NULL | 2 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+ 3 rows in set 1 warning (0.01 sec)

* View union all performance analysis

[temporary table not used]

Mysql > explain (select * from users1) union all (select * from users2) +-+ | id | select_type | | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | PRIMARY | users1 | NULL | ALL | NULL | 2 | 100.00 | NULL | 2 | UNION | users2 | NULL | ALL | NULL | 2 | 100.00 | NULL | +-+-- -+ 2 rows in set 1 warning (0.01 sec)

3. Summary of union and union all

* union has one more step to remove repetition than union all, which creates temporary tables and degrades performance.

* when the data of both result sets are relatively unique, it is recommended to use union all.

4. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

Do you think you want the above method of merging the query results of multiple select statements in MySQL? If you want to know more about it, you can continue to follow our industry information section.