Union all is used to replace union in SQL Union query optimization.

Optimizing Union
Union has an interesting optimization that exists except ss a few different databases. it's obvious when you think about how it works. union gives you the rows from two tables that don't exist in the other. so implicitly, you are removing duplicates. to do this
The MySQL database must return distinct rows, and thus must sort the data. sorting, as we know is expensive, especially for large tables.

Union all can very well be a big speedup for you. What if you already know that your data does not contain duplicates in either row, or what if you don't care about duplicates? In either case, Union all is for you. Further, there may be other ways you can avoid
The duplicates in your rows using some application logic, so you know that Union all will provide the results you want, without the heavy overhead of sorting the data.

The difference between Union and Union all is that Union performs a distanct action on the data, and the speed of the distanct action depends on the number of existing data. The larger the number, the slower the time. For several datasets, it is basically the complexity of O (n) algorithms to ensure that the data between datasets are not duplicated.

With the theoretical basis, you can change the SQL structure. After ensuring that there are no duplicates in the Data logic, you can change the two Union statements to Union all, the response speed of a query changes from 1.7 seconds to 300 milliseconds, which takes only 17% of the previous time.

Union is also useful. In the query of massive data, if we use select * From c_cons where cons_id in ('123456', '123456', '123456 '); such a query statement may cause full table scanning and can be replaced by Union all, for example:

Select * From c_cons where cons_id = '201312'
Union all
Select * From c_cons where cons_id = '201312'
Union all
Select * From c_cons where cons_id = '201312'
In this way, queries are much faster than in queries, and it does not perform full table scans.

Example 2:

Or statement (Part excerpt)

Select * From tablename where (CDP = 300 and inline = 301) or (CDP = 301 and inline = 301) or (CDP = 302 and inline = 301) or (CDP = 303 and inline = 301) or (CDP = 304 and inline = 301) or (CDP = 305 and inline = 301) or (CDP = 306 and inline = 301) or (CDP = 307 and inline = 301)

Union all statement (Part excerpt)

Select * From tablename where (Inline = 300 and CDP = 300) Union all select * From tablename where (Inline = 301 and CDP = 300) union all select * From tablename where (Inline = 302 and CDP = 300) Union all select * From tablename where (Inline = 303 and CDP = 300)

Returns an irregular 900 data records. The former takes more than 60 seconds, and the latter takes about 8 seconds.

------------------------------
Test with DB2 and find that the efficiency of using in is higher than that of Union all