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
却有区别