Shulou(Shulou.com)05/31 Report--

Editor to share with you the MySQL query data merge query results of the case, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

.

Using the union keyword, you can give multiple select statements and combine their results into a single result set. When merging, the number of columns and data types corresponding to both tables must be the same. Individual select statements are separated by the union or union all keyword.

Union does not use the keyword all, deletes duplicate records during execution, and all returned rows are unique; the purpose of using the keyword all is not to delete duplicate rows or automatically sort the results.

The basic syntax format is:

Select column,...from table1union [all] select column,... From table2

[example 1] query the information of all fruits whose price is less than 9, query the information of all fruits whose s_id is equal to 101,103, and use union to connect the query results. The SQL statement is as follows:

Mysql > select scuttlehead from fruits-> where f_price union all-> from fruits-> where f_price union all-> select signoridrecorder fancinameMagi fancier price-> from fruits-> where s_id in (101103) +-+ | s_id | f_name | f_price | +-+ | 104 | lemon | 6.40 | 101 | apple | 5.20 | 103 | apricot | 2.20 | 104 | berry | 7.60 | | 107 | | xxxx | 3.60 | melon | 8.20 | cherry | 3.20 | xbabay | 2.60 | grape | 5.30 | xbabay | 3.60 | apple | 5.20 | apricot | 2.20 | 101 | blackberry | 10.20 | cherry | 3.20 | coconut | 9.20 | +-- | -+ 15 rows in set (0.06 sec)

Union combines the results of multiple select statements into a result set. You can view the results of each select statement separately:

Mysql > select where f_price-> from fruits-> where f_price

< 9.0;+------+---------+---------+| s_id | f_name | f_price |+------+---------+---------+| 104 | lemon | 6.40 || 101 | apple | 5.20 || 103 | apricot | 2.20 || 104 | berry | 7.60 || 107 | xxxx | 3.60 || 105 | melon | 8.20 || 101 | cherry | 3.20 || 105 | xbabay | 2.60 || 102 | grape | 5.30 || 107 | xbabay | 3.60 |+------+---------+---------+10 rows in set (0.00 sec)mysql>

Select from fruits-> where s_id in (101103) +-+ | s_id | f_name | f_price | +-+ | 101 | apple | 5.20 | 103 | apricot | 2.20 | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut

As can be seen from the separate query results, the first select statement queries the fruit whose price is less than 9, and the second select statement queries the fruit provided by suppliers 101,103.

Use union to separate the two select statements, combine the output into a single result set after execution, and delete duplicate records.

Use union all to contain duplicate lines. Union automatically removes duplicate rows from the query result set, and you can use union all if you want to return all matching rows without deleting them.

[example 2] query the information of all fruits whose price is less than 9, query the information of all fruits whose s_id is equal to 101,103, and use union all to connect the query results. The SQL statement is as follows:

Mysql > select scuttlehead from fruits-> where f_price union all-> from fruits-> where f_price union all-> select signoridrecorder fancinameMagi fancier price-> from fruits-> where s_id in (101103) +-+ | s_id | f_name | f_price | +-+ | 104 | lemon | 6.40 | 101 | apple | 5.20 | 103 | apricot | 2.20 | 104 | berry | 7.60 | | 107 | | xxxx | 3.60 | melon | 8.20 | cherry | 3.20 | xbabay | 2.60 | grape | 5.30 | xbabay | 3.60 | apple | 5.20 | apricot | 2.20 | 101 | blackberry | 10.20 | cherry | 3.20 | coconut | 9.20 | +-- | -- + 15 rows in set (0.00 sec)

As you can see, the total records here are equal to the sum of the records returned by the two select statements, and the join query results do not remove duplicate rows.

The difference between union and union all:

The function of using union all is not to delete duplicate lines, and all keyword statements require less resources to execute, so use it as much as possible.

When determining that there is no duplicate data in the query results or that there is no need to remove duplicate data, uninon all should be used as much as possible to improve query efficiency.

The above is all the contents of this article "the case of merging query results of MySQL query data". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.