【mysql】left join on and 和 where的区别
left join on and
SELECT a.order_id ,b.id FROM way_order a LEFT JOIN way_order_product b ON a.order_id=b.order_id AND b.order_id >70
返回a表中所有数据和符合and条件的b数据
179 (NULL)
180 (NULL)
183 (NULL)
220 8
224 9
SELECT a.order_id ,b.id FROM way_order a inner JOIN way_order_product b ON a.order_id=b.order_id AND b.order_id >70
SELECT a.order_id ,b.id FROM way_order a LEFT JOIN way_order_product b ON a.order_id=b.order_id where b.order_id >70
220 8
224 9
and后条件在 inner join起作用相当于 把条件放在where后
SELECT a.* ,b.id,b.order_id FROM way_order a INNER JOIN way_order_team b ON a.order_id=b.order_id WHERE b.order_id >70
SELECT a.* ,b.id,b.order_id FROM way_order a INNER JOIN way_order_team b ON a.order_id=b.order_id and b.order_id >70
上面两个sql基本等价
EXPLAIN SELECT a.* ,b.id,b.order_id FROM way_order a left JOIN way_order_team b ON a.order_id=b.order_id WHERE b.order_id >70
EXPLAIN SELECT a.* ,b.id,b.order_id FROM way_order a left JOIN way_order_team b ON a.order_id=b.order_id and a.order_id >70
EXPLAIN SELECT a.* ,b.id,b.order_id FROM way_order a left JOIN way_order_team b ON a.order_id=b.order_id where a.order_id >70
EXPLAIN SELECT a.* ,b.id,b.order_id FROM way_order a left JOIN way_order_team b ON a.order_id=b.order_id and b.order_id >70
却有区别
posted on 2013-06-24 18:35 天地一连线_孤鸿挂飘渺 阅读(740) 评论(0) 编辑 收藏 举报
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步