Shulou(Shulou.com)05/31 Report--

This article shows you how UNION and UNION ALL are used in MySQL. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something through the detailed introduction of this article.

UNION in MySQL

UNION will filter out duplicate records after table linking, so it will sort the resulting result set after table linking, delete duplicate records and return results. In practice, most applications do not produce duplicate records, the most common is the process table and history table UNION. For example:

select * from gc_dfys union select * from ls_jg_dfys

This SQL first fetches the results of two tables at runtime, then sorts them with sort space to remove duplicate records, and finally returns the result set. If the table data volume is large, it may lead to sorting with disk.

UNION ALL in MySQL

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

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the two result sets combined 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, as if you had used DISTINCT for the entire result set, mysql will automatically deduplicate if there is identical data in the multi-table query results.

Using Union all, it will not be repeated and return all rows.

If you want to use ORDER BY or LIMIT clauses to classify or restrict all UNION results, you should enclose parentheses around individual SELECT statements and place 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 just as easy as this:

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

In clause. Order by is only meaningful when used with limit. If it is not used with limit, it will be removed by parser optimization.

If you still want to group by, and there are 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 brackets of union, otherwise an error will be reported.

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

Because pure sql, efficiency will be relatively low, and it will write temporary files, if your disk space is not large enough, there may be errors

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

Examples:

DROP TABLE IF EXISTS `ta`;CREATE TABLE `ta` ( `id` varchar(255) DEFAULT NULL, `num` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ------------------------------ Records of ta-- ----------------------------INSERT INTO `ta` VALUES ('a', '5');INSERT INTO `ta` VALUES ('b', '10');INSERT INTO `ta` VALUES ('c', '15');INSERT INTO `ta` VALUES ('d', '10'); -- ------------------------------ Table structure for `tb`-- ----------------------------DROP TABLE IF EXISTS `tb`;CREATE TABLE `tb` ( `id` varchar(255) DEFAULT NULL, `num` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ------------------------------ Records of tb-- ----------------------------INSERT INTO `tb` VALUES ('b', '5');INSERT INTO `tb` VALUES ('c', '15');INSERT INTO `tb` VALUES ('d', '20');INSERT INTO `tb` VALUES ('e', '99');

At this time, num in the c field corresponding to ta tb is the same

sql：

SELECT id,SUM(num) FROM ( SELECT * FROM ta UNION ALL SELECT * FROM tb) as tmp GROUP BY id

Run Results:

If:

SELECT id,SUM(num) FROM ( SELECT * FROM ta UNION SELECT * FROM tb) as tmp GROUP BY id

Run Results:

With Union, all returned rows are unique, as if you had used DISTINCT for the entire result set, mysql will automatically deduplicate if there is identical data in the multi-table query results.

Using Union all, it will not be repeated and return all rows.

The above is how UNION and UNION ALL are used in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.