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 */