Simply understand the difference between MySQL union all and union
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.
