select sal,job from emp where deptno=7839
union
select sal,job from emp_jobhistory where empno=7839
select sal,job from emp where deptno=7839
union all
select sal,job from emp_jobhistory where empno=7839
层次查询
- 从员工7876出发向上查看
select empno, ename, job, mgr
from emp
start with empno = 7876
connect by prior mgr = empno
prior相当于 empno = 7876
- 从员工KING出发向下查看
select level ename ||'work for' ||
prior ename
from emp
start with ename='KING'
connect by prior empno = mgr
order by level
-
where ename<>'JONES' 不显示JONES
-
connect by中加入ename<>'JONES'意思是不显示JONES之后的所有人
