前言
最近,有一个业务需求,给我一份数据 A ,把它在数据库 B 中存在,而又比 A 多出的部分算出来。由于数据比较杂乱,我这里简化模型。
然后就会发现,我去,这不就是 not in ,not exists 嘛。
那么问题来了,in, not in , exists , not exists 它们有什么区别,效率如何?
曾经从网上听说,in 和 exists 不会走索引,那么事实真的是这样吗?
带着疑问,我们研究下去。
注意:在说这个问题时,不说明 MySQL 版本的都是耍流氓,我这里用的是 5.7.18 。
用法讲解
为了方便,我们创建两张表 t1 和 t2 。并分别加入一些数据。(id为主键,name为普通索引)
-- t1DROP TABLE IF EXISTS `t1`;CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_t1_name` (`name`(191)) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1009 DEFAULT CHARSET=utf8mb4;INSERT INTO `t1` VALUES ('1001', '张三', '北京'), ('1002', '李四', '天津'), ('1003', '王五', '北京'), ('1004', '赵六', '河北'), ('1005', '杰克', '河南'), ('1006', '汤姆', '河南'), ('1007', '贝尔', '上海'), ('1008', '孙琪', '北京');-- t2DROP TABLE IF EXISTS `t2`;CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_t2_name`(`name`(191)) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1014 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;INSERT INTO `t2` VALUES (1001, '张三', '北京');INSERT INTO `t2` VALUES (1004, '赵六', '河北');INSERT INTO `t2` VALUES (1005, '杰克', '河南');INSERT INTO `t2` VALUES (1007, '贝尔', '上海');INSERT INTO `t2` VALUES (1008, '孙琪', '北京');INSERT INTO `t2` VALUES (1009, '曹操', '魏国');INSERT INTO `t2` VALUES (1010, '刘备', '蜀国');INSERT INTO `t2` VALUES (1011, '孙权', '吴国');INSERT INTO `t2` VALUES (1012, '诸葛亮', '蜀国');INSERT INTO `t2` VALUES (1013, '典韦', '魏国');
那么,对于当前的问题,就很简单了,用 not in 或者 not exists 都可以把 t1 表中比 t2 表多出的那部分数据给挑出来。(当然,t2 比 t1 多出来的那部分不算)
这里假设用 name 来匹配数据。
select * from t1 where name not in (select name from t2);或者用select * from t1 where not exists (select name from t2 where t1.name=t2.name);
得到的结果都是一样的。
但是,需要注意的是,not in 和 not exists 还是有不同点的。
在使用 not in 的时候,需要保证子查询的匹配字段是非空的。如,此表 t2 中的 name 需要有非空限制。如若不然,就会导致 not in 返回的整个结果集为空。
例如,我在 t2 表中加入一条 name 为