oracle里自连接查询,Oracle 自连接 / 外连接 / 子查询

--连接查询的三种格式

select ename, deptno,dname from emp natural join dept;

select ename, deptno,dname from emp join dept using(deptno);

select emp.ename, emp.deptno,dept.dname from emp join dept on emp.deptno!=dept.deptno;

--自连接，将一张表当成两张表来看待

select e.ename,m.ename from emp e join emp m on e.mgr= m.empno

--外连接right outer join/Left outer join/full outer join

select ename,d.deptno,d.dname

from emp right outer join dept d on emp.deptno= d.deptno

--连接更多表

select ename,dname,grade

from emp join dept on emp.deptno=dept.deptno

join salgrade on sal BETWEEN losal and hisal;

---------------------------------------------------------------------子查询

--单行子查询：子查询返回一行

select * from emp where sal>(select avg(sal)from emp);

--多行子查询：子查询返回多行 ANY(任意) ALL(所有) IN(在里面)

select * from emp where sal>all(select avg(sal)from emp group by deptno);

--[not] exists 判断是否行存在

select ename,mgr

from emp e where EXISTS(select 1 from emp where e.empno=mgr);

原文：http://www.cnblogs.com/Evan-Pei/p/4673734.html