左连接，右连接，+

oracle 的左外连接 右外连接 及其简写(+)

• 左外连接
  select a.*,b.*from tableA a left join tableB b on a.id=b.id    等价于   select a.*,b.* from tableA a,tableB b where a.id=b.id(+)
  这个表示选择所有 tableA 的记录，如果不满足 a.id=b.id 的 tableB 的相关值全部为 null
  如
                    a.id  a.name  b.id  b.school
                        1      abc    1      whut
                        2      cde     null  null

• 右外连接
  select a.*,b.* from tableA a right join tableB b on a.id=b.id
  等价于
  select a.*,b.* from tableA a,tableB b where a.id(+)=b.id
  看完上面的左外连接和右外连接是不是我们就能说“+”在左侧是右外链接，“+”在右侧是左外连接？  这就错了！
  
  

  1. Oracle中发出下列命令：

     SQL> selecte.ename,a.street_address,a.city,a.post_code

        2  from emp e,addr a

        3  where e.empno = a.empno(+)

        4  and a.state = ‘TEXAS’;

     下列哪个选项显示等价ANSI/ISO语句？

     A．select e.ename, a.street_address,a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empnowhere a.state = ‘TEXAS’;

     B．select e.ename, a.street_address,a.city, a.state, a.post_code from emp e left outer join addr a on e.empno =a.empno where a.state = ‘TEXAS’;

     C．select e.ename, a.street_address,a.city, a.state, a.post_code from emp e right outer join addr a on e.empno =a.empno where a.state = ‘TEXAS’;

     D．select e.ename, a.street_address,a.city, a.state, a.post_code from emp e right outer join addr a where e.empno =a.empno(+) and a.state = ‘TEXAS’;
     答案是：C 因为是右外连接。