A brief understanding of the difference between MySQL union all and union

Union is a union operation on the data, excluding duplicate rows and performing default sorting. Union all is a union operation on the data, including duplicate rows and not sorting. For example:

Create the database tables:

CREATE TABLE `t_demo` (
 `id` int(32) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `age` int(2) DEFAULT NULL,
 `num` int(3) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy this table:

CREATE TABLE `t_demo_copy` (
 `id` int(32) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `age` int(2) DEFAULT NULL,
 `num` int(3) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add data:

INSERT INTO `t_demo` VALUES ('1', '张三', '21', '69');
INSERT INTO `t_demo` VALUES ('2', 'Li Si', '22', '98');
INSERT INTO `t_demo` VALUES ('3', '王五', '20', '54');
INSERT INTO `t_demo` VALUES ('4', '赵甜', '22', '80');
INSERT INTO `t_demo_copy` VALUES ('1', '张三', '21', '69');
INSERT INTO `t_demo_copy` VALUES ('2', 'Zhu Bajie', '22', '98');
INSERT INTO `t_demo_copy` VALUES ('3', '王五', '20', '54');
INSERT INTO `t_demo_copy` VALUES ('4', '赵甜', '22', '80');
INSERT INTO `t_demo_copy` VALUES ('5', '孙武空', '22', '100');
INSERT INTO `t_demo_copy` VALUES ('6', 'Li Si', '24', '99');

UNION in MySQL

SELECT * FROM t_demo
UNION
SELECT * FROM t_demo_copy

Query results:

​

From the above query data we can find that:

After UNION links the tables, it will filter out duplicate records, sort the generated result set, delete duplicate records, and then return the results.

UNION ALL in MySQL

SELECT * FROM t_demo
UNION ALL
SELECT * FROM t_demo_copy

Query results:

​

From the above data we can see:

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

efficiency:

In terms of efficiency, UNION ALL is much faster than UNION. Therefore, if you can confirm that the two merged result sets do not contain duplicate data and do not need to be sorted, then use UNION ALL.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.