oracle学习笔记之四 多表查询

 1.从多个表中获取数据
  SELECT  table1.column,table2.column
  FROM    table,table2
  WHERE   table1.column1=table2.column2;
 
  -在WHERE子句中书写连接条件
  -如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀
  -N个表相连时，至少需要N-1个连接条件

2.笛卡尔积
  笛卡尔积在下列情况产生：
  -连接条件被省略
  -连接条件是无效的
  -第一个表中的所有行和第二个表中的所有行都发生连接

3.连接类型
1)等值连接
  -在用到多个表时用表名作前缀来限定列
  -通过使用表前缀可以提高性能
  -通过表名.列名的形似来区分来自不同表但名字相同的列
  -通过使用表的别名来简化查询语句
  例1：
      SELECT emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc
      FROM   emp,dept
      WHERE  emp.deptno=dept.deptno;

  例2：
      SELECT e.empno,e.ename,e.deptno,d.deptno,d.loc
      FROM   emp e,dept d
      WHERE  e.deptno=d.deptno;

2)非等值连接
  例：
     SELECT   e.ename,e.sal,s.grade
     FROM     emp e,salgrade s
     WHERE    e.sal
     BETWEEN  s.losal AND s.hisal;

3)外部连接
  -外部连接条件不能使用IN运算符，也不能使用OR运算符与其他条件相连
  例：
     SELECT   e.ename,d.deptno,d.dname
     FROM     emp e,dept d
     WHERE    e.deptno(+)=d.deptno
     ORDER BY e.deptno;

4)自身连接
  例：
     SELECT   worker.ename||' works for '||manager.ename
     FROM     emp worker,emp manager
     WHERE    worker.mgr=manager.empno;

4.SQL:1999语法的连接
  SELECT table1.column,table2.column
  FROM   table1
  [CROSS JOIN table2]|
  [NATURAL JOIN table2]|
  [JOIN  table2 USING(column_name)]|
  [JOIN  table2
     ON  (table1.column_name=table2.column_name)]|
  [LEFT|RIGHT|FULL OUTER JOIN table2
     ON  (talble1.column_name=table2.column_name)];

1)创建交叉连接
  CROSS JOIN子句会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的
 
  例:
     SELECT   emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc
     FROM     emp
     CROSS    JOIN dept;

2)自然连接
  -自然连接子句是基于两个表存在相同名字的列
  -返回两个表相匹配列中具有相同值的记录
  -如果相同名称的列的数据类型不同，则会产生错误

  例1：
      SELECT   empno,ename,sal,deptno
      FROM     emp
      NATURAL JOIN dept;
  结果：
        EMPNO     ENAME     SAL     DEPTNO    LOC
        7369      SMITH     800       20     DALLAS
        7499      ALLEN     1600      30     CHICAGO 
        7521      WARD      1250      30     CHICAGO 
        7566      JONES     2975      20     DALLAS 
        7654      MARTIN    1250      30     CHICAGO 
        7698      BLAKE     2850      30     CHICAGO 
        7782      CLARK     2450      10     NEW YORK
        ...

  例2：
      SELECT   empno,ename,sal,deptno,loc
      FROM     emp
      NATURAL JOIN dept
      WHERE   deptno IN (10,20);
  结果：
        EMPNO   ENAME    SAL    DEPTNO    LOC 
        7369    SMITH    800      20     DALLAS 
        7566    JONES    2975     20     DALLAS 
        7788    SCOTT    3000     20     DALLAS 
        7876    ADAMS    1100     20     DALLAS 
        7902    FORD     3000     20     DALLAS 
        7782    CLARK    2450     10     NEW YORK 
        7839    KING     5000     10     NEW YORK 
        7934    MILLER   1300     10     NEW YORK 

3)使用USING子句创建连接
  -USING子句可以用于指定产生连接的列
  例:
     SELECT  e.ename,e.sal,deptno,d.loc
     FROM    emp e
     JOIN    dept d USING (deptno)
     WHERE   deptno=20;
  结果：
       ENAME    SAL    DEPTNO    LOC 
       SMITH    800      20      DALLAS 
       JONES    2975     20      DALLAS 
       SCOTT    3000     20      DALLAS 
       ADAMS    1100     20      DALLAS 
       FORD     3000     20      DALLAS
       ...

  注：
  -如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句替换，以指定产生等值连接的列。
  -如果有多个列匹配的情况，使用USING子句只能指定其中的一列
  -USING子句中用到的列不能使用表名和列名作为前缀
  -NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用

4)使用ON子句创建连接
  -自然连接条件基本上是具有相同列名的表之间的等值连接，要指定任意连接条件，或指定要连接的列，可以使用ON子句
  -用ON将连接条件和其他检索条件分隔开
  -ON子句可以提高代码的可读性
  例1：
     SELECT  e.empno,e.ename,e.deptno,d.deptno,d.loc
     FROM    emp e
     JOIN    dept d
     ON      (e.deptno=d.deptno);
  结果：
      EMPNO    ENAME    DEPTNO    DEPTNO    LOC 
      7369     SMITH      20       20       DALLAS 
      7499     ALLEN      30       30       CHICAGO 
      7521     WARD       30       30       CHICAGO 
      7566     JONES      20       20       DALLAS 
      ...

  例2：
      SELECT  e.empno,e.ename,d.loc,m.ename
      FROM    emp e
      JOIN    dept d
      ON      e.deptno=d.deptno
      JOIN    emp m
      ON      e.mgr=m.empno;
  结果：
       EMPNO   ENAME    LOC        ENAME 
       7369    SMITH    DALLAS     FORD 
       7499    ALLEN    CHICAGO    BLAKE 
       7521    WARD     CHICAGO    BLAKE 
       7566    JONES    DALLAS     KING 
       7654    MARTIN   CHICAGO    BLAKE 
       7698    BLAKE    CHICAGO    KING 
       7782    CLARK    NEW YORK   KING 
       7788    SCOTT    DALLAS     JONES 
       7844    TURNER   CHICAGO    BLAKE 
       7876    ADAMS    DALLAS     SCOTT 
       7900    JAMES    CHICAGO    BLAKE 
       7902    FORD     DALLAS     JONES 
       7934    MILLER   NEW YORK   CLARK 

5)左外连接
  例:
     SELECT  e.ename,e.deptno,d.loc
     FROM    emp e
     LEFT    OUTER JOIN dept d
     ON      (e.deptno=d.deptno);
  结果：
       ENAME      DEPTNO      LOC 
       MILLER      10        NEW YORK 
       KING        10        NEW YORK 
       CLARK       10        NEW YORK 
       FORD        20        DALLAS 
       ...
       WARD  

6)右外连接
  例：
     SELECT  e.ename,e.deptno,d.loc
     FROM    emp e
     RIGHT   OUTER JOIN dept d
     ON      (e.deptno=d.deptno);
   结果：
        ENAME     DEPTNO     LOC 
        SMITH       20       DALLAS 
        ALLEN       30       CHICAGO 
        WARD        30       CHICAGO 
        JONES       20       DALLAS 
        ...
                             BOSTON

7)全外连接
  例：
     SELECT  e.ename,e.deptno,d.loc
     FROM    emp e
     FULL    OUTER JOIN dept d
     ON      (e.deptno=d.deptno);
  结果：
       ENAME      DEPTNO      LOC 
       MILLER      10        NEW YORK 
       KING        10        NEW YORK 
       CLARK       10        NEW YORK 
       FORD        20        DALLAS 
       ...
       WARD   
                             BOSTON
      

练习
1.查询emp和dept表，显示所有符合ename列的第一个字母为"S"的empno、ename、deptno、dname列的信息

   SELECT empno,ename,d.deptno,dname
   FROM   emp e,dept d
   WHERE  e.deptno=d.deptno
   AND    ename LIKE 'S%';

   或： 
   SELECT emp.empno,emp.ename,emp.deptno,dept.dname
   FROM   emp,dept
   WHERE  emp.deptno=dept.deptno
   AND    SUBSTR(emp.ename,1,1)='S';

2.查询emp、salgrade和dept表，显示雇员名、部门名、薪水以及薪水的等级
  
   SELECT e.ename,d.deptno,e.sal,s.grade
   FROM   emp e,dept d,salgrade s
   WHERE  e.deptno=d.deptno
   AND    (e.sal BETWEEN s.losal AND s.hisal);

3.显示工作在NEW YORK的所有雇员信息

   SELECT *
   FROM   emp e,dept d
   WHERE  e.deptno=d.deptno
   AND    d.loc='NEW YORK';

4.查询emp表，显示在雇员'SCOTT'之前来公司工作的雇员名和雇佣的时间信息
  
   SELECT a.ename,a.hiredate,b.ename,b.hiredate
   FROM   emp a,emp b
   WHERE  a.hiredate<b.hiredate and b.ename='SCOTT';

5.显示所有部门的雇员信息，如果部门没有雇员，也显示部门信息

   SELECT *
   FROM   emp e,dept d
   WHERE  e.deptno(+)=d.deptno;