mysql的leftjoin踩坑

mysql的leftjoin踩坑

使用MySQL的left join时，遇到的坑, 总的来说就是关于条件放在on里还是放在where里的问题.

举例

有一个公司表和雇员表

id	name
1	A公司
2	B公司

id	name	sex	company
1	张三	1	1
2	李四	1	2
3	小红	2	2

统计所有公司的名称,以及公司下男员工数量

//第一印象写下来的sql
select a.name, count(b.id) as num from company a LEFT JOIN employee b on a.id = b.company where b.sex = 1 GROUP BY company;

就上面的这个数据统计统计出来看,好像是没有问题.

但是我们新加一个皮包公司,没有员工的这种情况呢.

id	name
1	A公司
2	B公司
3	皮包公司

你会发现上面sql统计出来的数据 压根就没有皮包公司.这不是我们想要的结果,我们想统计出来皮包公司,只是男性员工数量为0

问题思考

仔细查询了资料以及尝试过后,我们先确定一些东西.

首先,确定MySQL的select关键字执行顺序

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

• 如果from阶段存在多张表联查,先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表1,
• 通过ON进行筛选，在虚拟表1的基础上进行筛选，得到虚拟表2
• 添加外部行

所以,我们之前写的sql我们可以确定,首先执行的是from,然后是left join…on,根据on后面的条件生成一个虚拟中间表,

可以简单的用下面的sql查看下leftjoin生成的中间表情况.

select * from company_test a LEFT JOIN employee b on a.id = b.company ;

因为sql是left join,所以是以a表为基础,如果a表存在,b表不存在的,b表的的字段会以null补全, 并且,B公司有李四和小红两个人,所以生成的中间表中,有两条记录分别对应到了employee表中的小红和李四.

生成中间表后,我们会执行where条件对这个中间表数据进行筛选,然后执行group by, 最后执行select.

问题解决

所以,我们上面的sql问题出在哪了呢?

我们刚提到了,执行where条件筛选的是中间表数据,所以where后面执行的 b.sex = 1 会排除掉皮包公司和b公司的小红,皮包公司是因为sex字段是null,所以导致了最后结果没有皮包公司记录

那我们的条件 b.sex = 1 的目的是什么呢? 是想先筛选出符合条件的中间表数据,然后通过groupby统计每个公司的男性员工数量. 从上面的一个执行顺序,我们可以知道,如果是想将sex = 1 的条件用来生成中间表, 那这个条件需要放在on后面,作为生成中间表的筛选条件.

可以通过下面的sql来查看加上sex = 1 的情况下生成的中间表.

select * from company_test a LEFT JOIN employee b on a.id = b.company and b.sex = 1;

可以看到a,b公司和皮包公司都有了,只是皮包公司的记录中,employee表的字段值是null.感觉没问题 ,我们进行最后的补全.

sql如下:

select a.name, count(b.id) as num from company_test a LEFT JOIN employee b on a.id = b.company and b.sex = 1 GROUP BY a.id;

得到了我们想要的结果.

反思

在做联表查询的时候,我们首先要确定用哪个联表关键字,是左连接,右连接,还是内连接;需要思考我们的条件是用来生成中间表的条件,还是用来从中间表中筛选出最后结果的条件,最后写出sql后,及时验证.