关联查询实践指南：on和where条件在Mysql中的应用场景

Mysql多表关联查询时查询条件放在on之后和where之后的区别

背景

今天在开发中遇到一个问题，在两个表关联查询时，最终得到的结果和预想结果不一致，经过排查后，我发现自己犯了一个很低级的错误，这样会导致查询出来的结果莫名其妙的变多或者变少。

问题重现

准备两张表dept,emp，一张员工表，一张部门表，内容如下；

id	deptName
1	交付一部
2	交付二部
3	交付三部

id	empName	deptId	salary
1	张三	1	2000
2	李四	1	2500
3	王五	2	3400
4	赵六	3	3500

情况一：

查询每个部门工资小于3500的员工人数

问题sql：

SELECT
	t.deptName 部门名称,
	count( p.empName ) 部门人数 
FROM
	dept t
	LEFT JOIN emp p ON t.id = p.deptId 
WHERE
	p.salary < 3500 
GROUP BY
	t.deptName

错误结果：

部门名称	部门人数
交付一部	2
交付二部	1

这是因为右表的过滤条件放在where中会被过滤掉，而不会显示0条

正确写法：

SELECT
	t.deptName 部门名称,
	count( p.empName ) 部门人数 
FROM
	dept t
	LEFT JOIN emp p ON t.id = p.deptId and p.salary < 3500
GROUP BY
	t.deptName

正确结果：

部门名称	部门人数
交付一部	2
交付二部	1
交付三部	0

情况二：

查询每个部门工资小于3500的员工人数(除了交付一部外)

问题sql：

SELECT
	t.deptName 部门名称,
	count( p.empName ) 部门人数 
FROM
	dept t
	LEFT JOIN emp p ON t.id = p.deptId and p.salary < 3500 and t.deptName != '交付一部'
GROUP BY
	t.deptName

错误结果：

部门名称	部门人数
交付一部	0
交付二部	1
交付三部	0

这是因为left join 做连接时，左表会显示全部数据，哪怕匹配不上为null的数据，左表过滤应当放入where条件中

正确写法：

SELECT
	t.deptName 部门名称,
	count( p.empName ) 部门人数 
FROM
	dept t
	LEFT JOIN emp p ON t.id = p.deptId and p.salary < 3500 
where t.deptName != '交付一部'
GROUP BY
	t.deptName

正确结果：

部门名称	部门人数
交付二部	1
交付三部	0

总结：

在left join 中，左表过滤必须放在where中，右表过滤必须放在on后面，这样结果才能跟我们预想的结果一样,如果是inner join的话，放在on后面和where后面是一样的效果。