Oracle自连接，外连接举例

一、多表连接

--查询每个员工的部门信息
select ename,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno;
--使用别名where连接
select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
--使用inner join on连接
select e.empno,e.ename,d.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno;
--使用inner join using连接,不能显式指明d.deptno,否则会报错
select e.empno,e.ename,deptno,d.dname from emp e inner join dept d using(deptno);

二、自连接

--查询每个员工的直接领导者姓名
select a.ename as 员工,b.ename as 上级 from emp a,emp b where a.mgr=b.empno;
--使用左外连接用(+)在右边，由于king没有直接领导者，所以他的上级为空
select a.ename as 员工,b.ename as 上级 from emp a,emp b where a.mgr=b.empno(+);
select a.ename as 员工,b.ename as 上级 from emp a inner join emp b on a.mgr=b.empno;

三、外连接

--左外连接(查询出部门的员工的情况---显示所有部门)
--使用left outer join on
select e.ename,d.dname from emp e left outer join dept d on e.deptno=d.deptno(+);
--使用left outer join using
select e.ename,d.dname from emp e left outer join dept d using(deptno);
--左外连接用(+)
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);
--右外连接(查询出所有的员工的部门情况---显示所有员工)
--使用right outer join on
select e.ename,d.dname from emp e right outer join dept d on e.deptnp(+)=d.deptno;
--使用right outer join using
select e.ename,d.dname from emp e right outer join dept d using(deptno);
--右外连接(+)
select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;
--全外连接
--full outer join on
select d.deptno,dname,ename from emp e full outer join dept d on e.deptno = d.deptno;
--full outer join using
select d.deptno,dname,ename from emp e full outer join dept d using(deptno);

四、练习

--显示每个部门的名称和这个部门员工平均工资
select dname,avg_sal from dept d,(select deptno,avg(nvl(sal,0)) avg_sal from emp group by deptno)t where d.deptno=t.deptno;
select e.deptno,dname,avg(nvl(sal,0)) from emp e,dept d where e.deptno=d.deptno group by e.deptno,dname;
--显示每个员工姓名,工资和直接领导者的姓名,工资
select a.ename 员工,a.sal,b.ename 领导,b.sal from emp a,emp b where a.mgr=b.empno;
--显示部门员工工资平均值高于所有员工工资水平的部门名称和平均工资
--先查出各个部门的平均工资
--再查出三个部门的平均工资
select d.dname,t.avg_sal_dept from dept d,
(select deptno,avg(nvl(sal,0)) avg_sal_dept  from emp group by deptno having 
avg(nvl(sal,0)) > (select avg(nvl(sal,0)) sal_avg_total from emp) )t where t.deptno=d.deptno;