mysql left join on 与 where 筛选的差异
准备两张表:
- a表:
- id a
- 1 a1
- 2 a2
- 3 a3
-
- b表:
- id b
- 1 b1
- 2 b2
1.没有条件
SELECT * FROM a
LEFT JOIN b ON a.id = b.id;
------------------------------
id a id1 b
1 a1 1 b1
2 a2 2 b2
3 a3
2.WHERE
SELECT * FROM a
LEFT JOIN b ON a.id = b.id
WHERE b.b = 'b1';
------------------------------
id a id1 b
1 a1 1 b1
3.ON(AND)
SELECT * FROM a
LEFT JOIN b ON a.id = b.id
AND b.b = 'b1';
------------------------------
id a id1 b
1 a1 1 b1
2 a2
3 a3
发现left join 语句 on和 where有差异,
和标准查询关键字执行顺序有关;
left join 是在from范围类,所以先on条件筛选表,然后两表再做left join。
而对于where来说在left join结果再次筛选
where等价于:
1>SELECT * FROM a LEFT JOIN b ON a.id = b.id;
2>在查询结果中将b.b = 'b1'筛选出来
on(and)等价于:
1>select * from b where b.b = 'b1'
2>再将查询结果与A表做left join
*********************************************
* 对于主表的筛选条件应放在where后面
* 对于关联表的筛选条件应放到on后面
- a表:
- id a
- 1 a1
- 2 a2
- 3 a3
- b表:
- id b
- 1 b1
- 2 b2
SELECT * FROM a
LEFT JOIN (SELECT * from b where b.b = 'b1') b ON a.id = b.id
where a.a = 'a1'