Oracle 自连接 / 外连接 / 子查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

--连接查询的三种格式 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);