Oracle多表查询

一. 相等连接查询

  1.  select empno,ename,sal,emp.deptno,loc from emp,dept

        where emp.deptno=dept.deptno

        order by loc;

 

  2.   select empno,ename,sal,emp.deptno,loc from emp,dept

          where emp.deptno=dept.deptno

          and sal>=1500;

 

  3   连接中表别名的使用

        select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e,deptno d

                  where e.deptno=d.deptno

                  and e.sal>=1500

                  order by d.loc

 

  4. 笛卡尔积（乘积连接）

        select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e,dept d

                   order by d.loc

   5.自连接

        select w.empno,w.ename,w.job,w.mgr,m.ename "M_Name",m.job "M_Job" from emp w,emp m

                  where w.mgr=m.empno

                  and w.job like 'ANA%'