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

背景

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

问题重现

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

情况一

：


查询每个部门工资小于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

错误结果：

这是因为右表的过滤条件放在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

正确结果：

情况二

：


查询每个部门工资小于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

错误结果：

这是因为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

正确结果：

总结

：

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