对比Oracle MySQL的内连接，外连接，左关联，右关联

假设有A,B 表， A为必须存在的主表，B为翻译字典表

Oracle
select ........... ,nvl(b.value,' ') from a,b where a.key = b.key(+);

MySQL:

select ......., ifnull(b.value,'') as xxx from a left join b on a.key = b.key; //注意：where 变成了on

补充：可以连续多个关联

from a left join b on a.key = b.key left join c on a.key2 = c.key2

理解为left join 左边的是主表

A left join b on xxx 表示A不可缺失任何记录

right join 反之

LEFT JOIN SQL语句中where结合的用法

- 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

心得：on中的条件关联，一表数据不满足条件时会显示空值。where则输出两表完全满足条件数据。

也就是说，On后面只带2个表的关联条件，而其他的筛选条件，要写到where后面

推荐阅读

热点文章