Mysql求两个表（查询结果）的差集

                            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) 中没有匹配的行。