Oracle的多表查询----------内连接

/*

   语法1：

      select *

      from tab1,tab2

      where 条件;

     

    语法2：

      select *

      from tab1 inner join tab2

      on 条件; 

     

   内连接查询效果：  只有满足条件的数据才会显示。

*/

-- 笛卡尔积 = 14*4= 56

-- 需求：查询员工和部门数据

select *

from emp,dept;

 

-- 需求：查询员工和部门数据

-- 语法1

selecte.empno,e.ename,d.deptno,d.dname

from emp e,dept d

where e.deptno = d.deptno;

 

-- 语法2

selecte.empno,e.ename,d.deptno,d.dname

from emp e innerjoin dept d

on e.deptno = d.deptno;

 

-- 案例

-- 需求：查询员工及其上司信息

select e1.empno,e1.ename,e2.empno,e2.ename

from emp e1,emp e2

where e1.mgr = e2.empno;

 

-- 需求：在上面基础上，查询员工的部门信息

selecte1.empno,e1.ename,d1.deptno,d1.dname,e2.empno,e2.ename

from emp e1,emp e2,dept d1

where e1.mgr = e2.empno

and e1.deptno = d1.deptno;

 

-- 需求：在上面基础上，查询上司的部门信息

selecte1.empno,e1.ename,d1.deptno,d1.dname,e2.empno,e2.ename,d2.deptno,d2.dname

from emp e1,emp e2,dept d1,dept d2

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno  = d2.deptno;

 

 

-- 需求：在上面基础上，查询员工的工资级别

selecte1.empno,e1.ename,d1.deptno,d1.dname,e1.sal,s1.grade,e2.empno,e2.ename,d2.deptno,d2.dname

from emp e1,emp e2,dept d1,deptd2,salgrade s1

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno  = d2.deptno

and e1.sal between s1.losal and s1.hisal;

 

-- 需求：在上面基础上，查询上司的工资级别

select e1.empno,e1.ename,d1.deptno,d1.dname,e1.sal,s1.grade,e2.empno,e2.ename,d2.deptno,d2.dname,e2.sal,s2.grade

from emp e1,emp e2,dept d1,deptd2,salgrade s1,salgrade s2

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno  = d2.deptno

and e1.sal between s1.losal and s1.hisal

and e2.sal between s2.losal and s2.hisal;

 

-- 需求：在上面基础上，把员工的工资级别显示为"一级，二级，三级...."

selecte1.empno,e1.ename,d1.deptno,d1.dname,e1.sal,

decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级') as员工工资级别

,e2.empno,e2.ename,d2.deptno,d2.dname,e2.sal,s2.grade

from emp e1,emp e2,dept d1,deptd2,salgrade s1,salgrade s2

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno  = d2.deptno

and e1.sal between s1.losal and s1.hisal

and e2.sal between s2.losal and s2.hisal;

 

-- 需求：在上面基础上，把上司的工资级别显示为"一级，二级，三级...."

selecte1.empno,e1.ename,d1.deptno,d1.dname,e1.sal,

decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级') as员工工资级别

,e2.empno,e2.ename,d2.deptno,d2.dname,e2.sal,

decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') as上司工资级别

from emp e1,emp e2,dept d1,deptd2,salgrade s1,salgrade s2

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno  = d2.deptno

and e1.sal between s1.losal and s1.hisal

and e2.sal between s2.losal and s2.hisal;

 

 

 -------------------------------------------------------------------------------

select * from emp;

select * from dept;

select * from salgrade;