假设有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后面