Mysql求两个表(查询结果)的差集
两个查询结果的差集,mysql直接提供了关键字not in,而且我们也一般先会想到这个查询方式。但是在数据量多的情况之下,效率会很低,这是必须使用左链接的方式求查询。
场景是这样的小号信息可以在多个分组里面,现在需要查询出小号信息在未分组的信息并且进行分页查询!
CREATE TABLE `tm_friend_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`mobile` varchar(248) DEFAULT '' COMMENT '手机号码',
`nickname` varchar(248) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '昵称',
`state` int(1) NOT NULL DEFAULT '1' COMMENT '状态(1正常/2已删除)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2044 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='小号好友表';
CREATE TABLE `tm_friend_group` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`name` varchar(248) DEFAULT '' COMMENT '名称',
`state` int(1) NOT NULL DEFAULT '1' COMMENT '状态(1正常/2已删除)',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
`sort` int(3) DEFAULT '1' COMMENT '排序',
`create_id` int(11) DEFAULT '1' COMMENT '创建用户ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='小号分组表';
CREATE TABLE `tm_friend_group_relation` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`group_id` int(11) DEFAULT NULL COMMENT '组号ID',
`friend_id` int(11) DEFAULT NULL COMMENT '好友ID',
`state` int(1) NOT NULL DEFAULT '1' COMMENT '状态(1正常/2已删除)',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='小号分组关联表';
采用not in查询
SELECT
info.mobile,
info.nickname,
info.state
FROM
tm_friend_info info
WHERE
info.state = 1
AND info.id NOT IN (
SELECT
friend_id
FROM
tm_friend_group_relation relation,
tm_friend_group tgroup
WHERE
relation.group_id = tgroup.id
AND tgroup.state = 1
AND relation.state = 1
)
采用left join查询
SELECT
info.mobile,
info.nickname,
info.state
FROM
tm_friend_info info
LEFT JOIN (
SELECT
friend_id
FROM
tm_friend_group_relation relation,
tm_friend_group tgroup
WHERE
relation.group_id = tgroup.id
AND relation.state = 1
AND tgroup.state = 1
) AS b ON info.id = b.friend_id
WHERE
info.state = 1
AND b.friend_id IS NULL
结果都是一样的!但是可以看到执行策略!却是有很大的不同,采用not in中select_type中SUBQUERY是子查询非常的消耗性能!
select_type介绍
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。