【mysql】左外连接查询中 on 和 where 的区别

论点

在使用 mysql 的左外连接（left join）查询时，驱动表（left join 左边的表），亦即数据需要全部被查出表的查询条件建议用 where 设定，被驱动表（left join 右边的表）的查询条件建议写到 on 中，两者的关联条件必须写在 on 中。这也是左外连接查询的语义所在。当然，也要结合业务场景、表索引等因素作灵活调整。另外，左外连接查询的驱动表并不一定就是 left join 左边的表。本文为验证此结论，做如下探究。

mysql 版本为 5.7.22

论证

假设有一个答题游戏，参赛选手分属不同的队伍。系统需要记录选手每次（可多次答题）的答题得分。现需要统计每个队伍中得分超过 5 分的参赛选手数量，我们可以使用左外连接查询来获取这些统计数据。其中，分数大于 5 的逻辑就是上文结论中被驱动表的查询条件。

创建两张数据表并添加测试数据：

drop table if exists team;
-- 创建团队表
create table team (
	team_id int unsigned not null auto_increment,
	name varchar(10) not null comment '名称',
	primary key (team_id)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci comment '团队表';

-- 添加团队数据
insert into team
values
	(10, '产品部'),
	(20, '架构组'),
	(30, '测试部');

drop table if exists play;
-- 创建答题记录表
create table play (
	id int unsigned not null auto_increment,
	team_id int unsigned not null comment '所属团队id',
	user_id int unsigned not null comment '用户id',
	score tinyint unsigned not null comment '得分',
	primary key (id),
	key (team_id)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci comment '答题记录表';

-- 添加答题数据
insert into play
values
	(null, 10, 10, 1),
	(null, 10, 10, 2),
	(null, 10, 20, 3),
	(null, 10, 30, 4),
	(null, 10, 30, 5),
	(null, 10, 30, 6),
	(null, 30, 50, 7),
	(null, 30, 50, 8),
	(null, 30, 60, 9);

此次活动，产品部有 3 名朋友踊跃地参赛，测试部派出 2 名高智商同事乐在其中。遗憾的是，架构组的同事因为工作繁忙没能参加此次活动。所以 架构组的参赛人员统计数应该为 0。

接下来让我们看一下不同的查询位置会得到哪些数据。

①. 大于 5 分的条件放在 on 中

select
	t.team_id,
	t.name,
	count(distinct(p.user_id)) as person_total
from
	team t
left join
	play p
on t.team_id = p.team_id and p.score >= 5
group by
	p.team_id
order by
	person_total desc;

查询结果为：

可以看到架构组孤零零的存在。

②. 大于 5 分的条件放在 where 中

select
	t.team_id,
	t.name,
	count(distinct(p.user_id)) as person_total
from
	team t
left join
	play p
on t.team_id = p.team_id
where
	p.score >= 5
group by
	p.team_id
order by
	person_total desc;

查询结果为：

不符合左外连接查询初衷，团队表的数据没有全部展示。

分析

对上述两个查询 sql 做如下解释：

-- 大于 5 分的条件放在 on 中
explain select
	t.team_id,
	t.name,
	count(distinct(p.user_id)) as person_total
from
	team t
left join
	play p
on t.team_id = p.team_id and p.score >= 5
group by
	p.team_id
order by
	person_total desc;

show warnings;

-- 大于 5 分的条件放在 where 中
explain select
	t.team_id,
	t.name,
	count(distinct(p.user_id)) as person_total
from
	team t
left join
	play p
on t.team_id = p.team_id
where
	p.score >= 5
group by
	p.team_id
order by
	person_total desc;

show warnings;

结果如下图：

在本例（结合上图）中可以看出，当 left join 右边表的查询限定条件放在 where 中时，该表作为驱动表进行实际查询。