MySQL关联left join 条件on与where不同

来源：互联网发布：u盘照片数据恢复编辑：程序博客网时间：2024/05/24 05:51

联表查询的on个where 语句文章来源：http://database.51cto.com/art/201005/200521.htm（尊重别人劳动成果）

http://bbs.csdn.net/topics/390582391(这个链接有图可以更详细的分析)

自己总结：先写个sql对比

a: select p.pname,p.pcode,s.saletime,count(s.aid) as total from products as p
left join sales_detail as s on (s.pcode=p.pcode)
where s.saletime in ('2008-09-23','2008-09-24')
group by p.pcode order by total desc,p.pid asc

b:select p.pname,p.pcode,s.saletime,count(s.aid) as total from products as p
left join sales_detail as s on ((s.pcode=p.pcode)

and s.saletime in ('2008-09-23','2008-09-24'))
group by p.pcode order by total desc,p.pid asc

表结构这里就不写了直接看查询的结果就知道了

pname | pcode | saletime | total | -----第一个语句

| 商品1 | AC90 | 2008-09-23 | 2 |

pname | pcode | saletime | total | -----第二个语句

| 商品1 | AC90 | 2008-09-23 | 2 |

| 商品2 | DE78 | NULL | 0 |

| 商品3 | XXXX | NULL | 0 |

分析下明白： on、and 中的条件关联，一表数据不满足条件时会显示空值。where则输出两表完全满足条件数据。

from 表1 left on 表2 on后面的条件判断全部是针对left join的表 where 针对2张表

0 0