Oracle 自连接 / 外连接 / 子查询
2015-07-24 15:53 Evan.Pei 阅读(526) 评论(0) 编辑 收藏 举报--连接查询的三种格式 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);