Basic usage of UNION and UNION ALL in MySQL

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

When using UNION, all returned rows are unique, just as if you had used DISTINCT on the entire result set. If there is exactly the same data in the multi-table query results, MySQL will automatically remove duplicates.

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;

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

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)

example:

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 him
-- ----------------------------
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, the num of 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

Running results:

like:

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

Running results:

When using UNION, all returned rows are unique, just as if you had used DISTINCT on the entire result set. If there is exactly the same data in the multi-table query results, MySQL will automatically remove duplicates.

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.