In mysql, if you want to merge two result sets, you can use UNION and UNION ALL. If you want to remove duplicate records, you can belong to UNION ALL. However, if you want to remove non-duplicate rows, you can use UNION. This article introduces the difference between UNION and UNION ALL in mysql.

1. Different specific functions

UNION statement: used to display query data in the same column in different tables; (Excluding duplicate data)

The UNION ALL statement is used to display the query data in the same column in different tables. (including duplicate data)

2. Different use

Using the UNION keyword merges all the query results together and then removes the same records.

Using the UNION ALL keyword merges ALL the results together.

3. Different efficiency

The UNION sorts the results and repeats the query in 11.84 seconds over 730,000 data tests.

The UNION ALL does not sort and does not repeat the 4.34 second query in the 730,000 data test

4, different platoon weight

Union: Merge two result sets. Multiple table joins will remove duplicate records without duplicate row data.

UNION ALL: Joins two result sets, removes duplicate records after joining multiple tables

Generally speaking, union All is much faster than Union. If there is no need to discharge weight, it is recommended to use Union All directly. If you need to rearrange weights, use union.

case

Get the data of boys and girls in a class, and two boys and three girls in total five data

select id,grade_id,sex from (select id,grade_id,sex from oc_user_students where (birthday > = '2011-06-05' or birthday
< = '2016-06-05' ) and class_id=0 and sex=1 and status=1 order by id limit 0.2) as t1 union select id,grade_id,sex from (select id,grade_id,sex from oc_user_students where (birthday> = '2011-06-05' or birthday < = '2016-06-05' ) and class_id=0 and sex=2 and status=1 order by id limit 0.3) as t2 order by id asc limit 0.5
Copy the code

High performance MySQL (version 3).pdf