Oracle外连接，左外连接，右外连接，内连接简析

--内连接即普通等值连接
SELECT e.ENAME, e.JOB, e.SAL, d.DNAME
  FROM EMP e
 INNER JOIN DEPT d
    ON e.DEPTNO = d.DEPTNO
 WHERE e.SAL > 2000;

select e.ename ,e.job, e.sal ,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal > 2000;

--外连接验证之前，先往dept表中插入一些信息
--表操作基于Oracle中scott用户下emp，dept表。由于deptno是emp的外键，
--所有左外连接查询不明显，但外连接、右外连接查询结果明显

insert into dept (DEPTNO, DNAME, LOC)
values (60, '研发部', 'beijing');

insert into dept (DEPTNO, DNAME, LOC)
values (80, '培训部', 'beijing');

insert into dept (DEPTNO, DNAME, LOC)
values (70, '公关部', 'beijing');

commit;

select e.ename, e.job, e.sal, d.dname
  from emp e, dept d
 where e.deptno (+)= d.deptno;
 
--左外连接：left outer join 或 left join
select e.ename,e.job,e.sal,d.dname
  from emp e left outer join dept d on d.deptno = e.deptno ;
  
select e.ename,e.job,e.sal,d.dname
  from emp e left  join dept d on d.deptno = e.deptno ;
----此为Oracle左外连接自带语法
select e.ename,e.job,e.sal,d.dname
  from emp e ,dept d
 where d.deptno (+)= e.deptno ;
 
--右外连接：right outer join 或 right join
select e.ename,e.job,e.sal,d.dname
  from emp e right outer join dept d on d.deptno = e.deptno ;
  
select e.ename,e.job,e.sal,d.dname
  from emp e right  join dept d on d.deptno = e.deptno ;
----此为Oracle右外连接自带语法
select e.ename,e.job,e.sal,d.dname
  from emp e ,dept d
 where d.deptno = e.deptno(+) ;

--外连接：full outer join 或 full join
select e.ename,e.job,e.sal,d.dname
  from emp e full outer  join dept d on d.deptno = e.deptno ;

select e.ename,e.job,e.sal,d.dname
  from emp e full  join dept d on d.deptno = e.deptno ;
  
/*
三个表做左外连接
SELECT *
FROM TESTA
LEFT OUTER JOIN TESTB
ON TESTA.A=TESTB.A
LEFT OUTER JOIN TESTC
ON TESTA.A=TESTC.A
*/

 

转载于:https://www.cnblogs.com/wannianma/archive/2013/04/02/2994621.html