Shulou(Shulou.com)06/01 Report--

Union performs data union operation, excluding duplicate rows, while default sorting Union all performs data union operation, including duplicate rows, without sorting for example:

Create a database table:

CREATE TABLE `tdemo` (`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 `troomdemotion _ 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 `tdemo` VALUES ('1Qing,' Zhang San', '21th,' 69'); INSERT INTO `tdemo` VALUES ('2Qing,' Li Si', '22Qing,' 98'); INSERT INTO `tdemo` VALUES ('3Qing,' Wang Wu', '20cm,' 54'); INSERT INTO `tdemo` VALUES ('4Qing,' Zhao Tian', '22Qing,' 80') INSERT INTO `tresume demotion _ copy`VALUES ('1Qing,' Zhang San', '21stories,' 69'); INSERT INTO `tresume demotion _ copy` VALUES ('2Qin,' Zhu Bajie', '22stories,' 98'); INSERT INTO `tresume demotion _ copy` VALUES ('3hammer,' Wang Wu', '20cycles,' 54'); INSERT INTO `troomdemotion _ copy`VALUES ('4Qin,' Zhao Tian', '22Qing,' 80') INSERT INTO `tresume demotion _ copy`VALUES ('54th,' Sun WuKong', '22nd,' 100'); INSERT INTO `tresume demotion _ copy` VALUES ('6pm,' Li Si', '24th,' 99')

UNION in MySQL

SELECT * FROM t_demoUNIONSELECT * FROM t_demo_copy

Query results:

​

From the query data above, we can find that:

After table linking, UNION will filter out the duplicate records, sort the resulting result set, delete the duplicate records and return the results.

UNION ALL in MySQL

SELECT * FROM t_demoUNION ALLSELECT * FROM t_demo_copy

Query results:

​

From the above data, we can see:

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

Efficiency:

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.