left join副表查询条件影响了主表查询结果

• 问题描述：left join副表查询条件影响了主表查询结果

sql代码如下：

SELECT count(r.id) as weight ,r.*,m.*
FROM  room r left join meeting m on m.room_id =r.id  
where r.tenant_id =1 and r.is_del = 0 and (m.organizer_id = 7 OR m.organizer_id IS NULL ) GROUP BY r.id,r.name

如上，meeting表是left join进来的表，room表是主表，按理说meeting表的判断条件不会影响主表，但是实际测试发现：

---

sql语句1：

SELECT count(r.id) as weight ,r.*,m.*
FROM  room r left join meeting m on m.room_id =r.id  
where r.tenant_id =1 and r.is_del = 0 GROUP BY r.id,r.name

查出来的数据有8条；

---

sql语句2：

SELECT count(r.id) as weight ,r.*,m.*
FROM  room r left join meeting m on m.room_id =r.id  
where r.tenant_id =1 and r.is_del = 0 and (m.organizer_id = 7 OR m.organizer_id IS NULL ) GROUP BY r.id,r.name

查出来的数据有7条；

---

即：

m.organizer_id = 7 OR m.organizer_id IS NULL

这个查询条件过滤掉了一条room表的数据。

这里看到这个查询条件加在where后面，现在把他提前，放到on后where前，即：

SELECT count(r.id) as weight ,r.*,m.*
FROM  room r left join meeting m on m.room_id =r.id  and (m.organizer_id = 7 OR m.organizer_id IS NULL )
where r.tenant_id =1 and r.is_del = 0  GROUP BY r.id,r.name

查出来的就是8条了

• 结论：left join联表查询，放在on后的查询条件才不会影响主表的查询结果