Oracle多表连接与子查询

转自：http://www.cnblogs.com/puresoul/archive/2010/07/08/1773871.html

 

一、连接：

1、等值连接
     迪卡尔集连接
              select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门
              from emp a, dept b
     等值连接
              select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门
              from emp a, dept b
              where a.deptno = b.deptno;
2、非等值连接，在emp表和salgrade表中查找员工的姓名，工资，等级，工资上线，工资下线
              select ename as 姓名, sal as 工资, grade as 工资等级,losal as 工资上线，hisal as 工资下线
              from emp, salgrade
              where sal between losal and hisal;
3、外连

　　外连接则是内连接的扩展，它不仅会满足连接条件的记录，而且还会返回不满足连接条件的记录，语法如下：
       右外连
               select e.ename, d.dname, e.deptno
               from emp e,dept d
               where e.deptno = d.deptno(+);
        左外连与右外连相反

     　　以hr登陆查询表employees 和departments 表
               select  first_name as 姓名, department_name as 部门名称,  d.department_id as 部门编号 
               from employees e, departments d
               where e.department_id= d.department_id(+) ;
4、自连：在同一个表中查询每个员工及上司的工号和姓名
               select a.empno  as 员工编号, a.ename as 员工姓名, a.mgr as 上司的员工编号, b.ename as 上司姓名
               from emp a, emp b
               where a.mgr = b.empno;
5、sql99
         交叉连接 cross join------相当于迪卡尔集
               select e.ename,d.dname
               from emp e cross join dept d;

         自然连接 natural join------相当等值连接
               select e.ename, d.dname
               from emp e natural join dept d;

         using子句-----使用同名列查询
              select e.ename, d.dname
              from emp e join dept d
              using (deptno);

         on子句 ------当列名不同时用on子句
              用on查询两张表
                   select e.ename, d.dname
                    from emp e join dept d
                   on e.deptno = d.deptno;

     　 用on查询多张表
               select e.ename, d.dname
               from emp e join dept d
               on e.deptno = d.deptno
               join 第三个表
               on 列1 = 列2;

     　内连接（Inner join）-------内连接只返回满足连接条件的数据
              select employee_id, last_name, salary, department_id, department_name
              from employees inner join departments using(department_id);

     　左外连
              select employee_id, last_name, salary, department_id, department_name
              from employees left join departments using(department_id); 
     　右外连
              select employee_id, last_name, salary, department_id, department_name
              from employees right join departments using(department_id);

     　满外连
              select employee_id, last_name, salary, department_id, department_name
              from employees full outer join departments using(department_id);   

 

二、子查询：

　　1、子查询语法：
　　　　SELECT select_list
　　　　FROM table
　　　　WHERE expr operator
    　　　　(select select_list
     　　　　　　FROM table);

　　2、子查询分为：
　　　　『标准子查询』：子查询只执行一次
　　　　『关联子查询』：主查询执行一次，子查询就执行一次，子查询依赖于主查询的参数。
　　　　　select * from jobs a
　　　　　where   job_id>1   and   Exists(select * from jobs where job_id=a.job_id-1)

　　3、使用子查询的方针：
　　　　(1)子查询要用括弧“()”括起来；
　　　　(2)子查询要放在比较运算符的右边。
　　　　(3)Order by子句在子查询中是没有必要的，除非需要Top-N的分析。
　　　　(4)单行子查询配单个值的操作符，多行子查询配多个值的操作符。

　　      (5)查询是基于未知的值时应考虑使用子查询。

　　注:

  　　如果子查询没有返回结果，主查询也不会返回任何结果

　　如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

　　如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符，只能用IN，ANY，ALL

 

　　多行子查询的比较运算符：
　　　　　　IN，ANY ，ALL

　　　　其中ANY和ALL可以用“对聚合函数(Max,Min)统计结果的比较”来代替。

　　　　　　select stor_id,min(qty) from sales
　　　　　　group by stor_id
　　　　　　having min(qty) in (select min(qty) from sales group by stor_id);
　　　　这是一个画蛇添足的查询示例。 

　　4、子查询示例：

          查找出工资比scott高的人
              select ename, sal from emp
              where sal>
              (select sal from emp where ename='SCOTT'); 
     　  查找那些人和scott相同职位的人
              select ename, job from emp
              where job=
               (select job from emp where ename='SCOTT')  
               and ename <> 'SCOTT';

     　  any的用法  < any意味着小于最大、> any大于最小
              select empno, ename, sal, job
              from emp 
              where sal<any(select sal from emp where job='SALESMAN');

     　  all的用法 < all：小于所有，即小于最小、> all：大于所有，即大于最大
              select empno, ename, sal, job
              from emp
              where sal<all(select sal from emp where job='SALESMAN');

三、Oracle TopN查询：
 　　  在ORACLE中通常采子查询的方式来实现TopN查询
　　　　语法格式：
　　　　　　SELECT 字段列表
　　　　　　FROM  (SELECT 字段列表 FROMtable ORDER BY 排序字段）
　　　　　　WHERE rownum <= n