Shulou(Shulou.com)06/02 Report--

This article introduces what is the use of union and union all in sql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

If we need to display the results of the two select statements as a whole, we need to use the union or union all keyword. The function of union (or federation) is to display multiple results together.

The difference between union and union all is that union automatically compresses duplicate results in multiple result sets, while union all displays all results, whether duplicated or not.

Union usage

Objective: to query the members of group_id = 1 and group_id as null in the members table:

Select m.member_name from members m where m.group_id = 1unionselect m.member_name from members m where m.group_id is null

Results:

Analysis: combine the results of the above query with the results of the following query. You will find a problem. The data of "Xiao Heng" is only shown once, but we have two Xiao Heng. So union will reprocess the results found.

Union all usage

Objective: to solve the problems encountered by union and query all the results found:

Select m.member_name from members m where m.group_id = 1union allselect m.member_name from members m where m.group_id is null

Results:

Analysis: you can see that the data of "Xiao Heng" are shown twice, that is, union all will show all the results directly.

Summarize the perceptual knowledge of 1:join/union & difference

The two tables joined by join are the result sets obtained by matching the same values of the same fields in the two tables. It can be thought that the columns of the result set have been broadened. Join makes the content of the query more detailed. Union takes the union of two result sets, but the two result sets have the same number of columns, the same order of columns, and the same or similar field types (what DBMS thinks are similar) before they can be union and displayed together. You can think of it as too many rows in the result set. Union is to make the query more results.

Summarize the perceptual knowledge of 2:union/union all & difference

Union: merge two result sets, excluding duplicate rows, and sort the default rules at the same time; union will filter out duplicate records after table linking, so the resulting result sets will be sorted after table linking, and duplicate records will be deleted and returned. Most practical applications will not produce duplicate records, the most common is the process table and history table union. Union all: merge two result sets, including duplicate rows, without sorting; if there is duplicate data in the two returned result sets, the returned result set will contain duplicate data.

So much for sharing about the usage of union and union all in sql. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.