The union all usage in MySQL

the union in MySQL

The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned.

To illustrate:

SELECT * FROM table1 Union SELECT * FROM Table2

This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large.

the union all in MySQL UNION all simply merges two results and returns. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data.

To illustrate:

SELECT * FROM table1 UNION ALL SELECT * FROM table2
Note: When using UNION, the number of the previous Select column is equal to the number of the next select column, such as: table1: (Id,createdate,lastupdatedate,desc,num,hashcode), Table2: (ID,CREATEDATE,LASTUPDATEDATE,DESC)

If used now: SELECT * FROM table1 UNION all SELECT * from Table2 will not succeed, the database is reported: Error The used SELECT statements has a differ ENT Number of columns

This is a hint query of the two tables of the field is not uniform, if Table1 than table2 field content, you can use an empty string instead

Select Id,createdate,lastupdatedate,desc,num,hashcode from table1 UNION all select Id,createdate,lastupdatedate,desc, "," from table2

If there is not want, you must remember the previous query content and the following query content to the same number of fields, before you query 4, followed by the corresponding put 4, so that will not prompt the number of different parameters of the error.

Actually, just a little bit of a change, you can choose 4 field for ' Select Id,createdate,desc,hascode from table1 '

In terms of efficiency, union All is much faster than union, so if you can confirm that the combined two result sets do not contain duplicate data, then use UNION ALL.

You can use the union ALL function if you encounter two different table data to set a query.

Select COUNT (C.A) from ((select UID a,id,serial,parkname,cardno,scorerealpay,payflag,paytype,createtime From Cp_consumption_record WHERE uid=761 and Payflag=1 ORDER by Createtime DESC) UNION All (SELECT UID a,cpresid,cpparkid , parkserial,carcode,price,businesstype,cardno,createdate from Cp_reservation WHERE uid=761 and BusinessType in (All-in-a-.) ORDER by CreateDate DESC)) C

This is the total number of data in the query result set,

If there are query conditions, you can add them directly after C, for example, query by time

Select C.uid,c.scorerealpay,c.payflag,c.paytype from ((select UID as uid,id as id,serial as SERIAL, parkname as Parkname,cardno cardno,scorerealpay scorerealpay,payflag payflag,paytype paytype,createtime Createtime from Cp_ Consumption_record WHERE uid=761 and Payflag=1 ORDER by Createtime DESC) UNION All (SELECT UID A,cpresid,cpparkid,parkser Ial,carcode,price,businesstype,cardno,createdate from Cp_reservation WHERE uid=761 and Businesstype in (All-in-a-.) ORDER by CreateDate DESC)) c ORDER by Createtime Desc/asc

Here to emphasize, you want to follow the conditions of the query, you want to add the conditions in the select subquery, and finally in accordance with the unified time reverse or the positive sequence