Brief analysis of MySQL union and union all

In the database, both UNION and UNION ALL keywords merge two result sets into one, but the two are different in terms of usage and efficiency.

UNION in MySQL

UNION will filter out duplicate records after linking the tables, so after the tables are linked, the resulting result set will be sorted, duplicate records will be deleted, and then the results will be returned. In practice, duplicate records will not be generated in most applications. The most common is the UNION of the process table and the history table. like:

select * from gc_dfys union select * from ls_jg_dfys

When this SQL is run, it first retrieves the results of the two tables, then uses the sorting space to sort and delete duplicate records, and finally returns the result set. If the amount of data in the table is large, it may result in disk sorting.

UNION ALL in MySQL

UNION ALL simply combines the two results and returns them. In this way, if there is duplicate data in the two returned result sets, the returned result set will contain the duplicate data.

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the two merged result sets do not contain duplicate data, then use UNION ALL, as follows:

select * from gc_dfys union all select * from ls_jg_dfys

With UNION, all returned rows are unique, just as if you had used DISTINCT on the entire result set.

If you use Union all, all rows will be returned without removing duplicates.

If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, you should parenthesize the individual SELECT statements and put the ORDER BY or LIMIT after the last one:

(SELECT a FROM tbl_name WHERE a=10 AND B=1) 
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

It's a bit more troublesome to do this:

select userid from (
select userid from testa union all select userid from testb) t 
order by userid limit 0,1;

If you still want to group by, and have conditions, then:

select userid from (select userid from testa union all select userid from testb) t group by userid having count(userid) = 2;

Note: There must be an alias after the union brackets, otherwise an error will be reported

Of course, if the data volume of several union tables is large, it is recommended to export the text first and then execute it with a script.

Because pure SQL is used, the efficiency will be relatively low, and it will write temporary files. If your disk space is not large enough, errors may occur.

Error writing file '/tmp/MYLsivgK' (Errcode: 28)

Summarize

The above is the introduction of MySQL union and union all to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!