oracle 多表连接

笛卡尔集：表的列数相加，行数相乘，为了避免笛卡尔集，可以在WHERE中加入有效的连接条件（例如EMP.DEPTNO=DEPT.DEPTNO)，连接条数至少有N-1个，N代表表的个数。在实际运行环境下，应避免使用笛卡尔全集,笛卡尔全集中，有些记录是错误的。

等值连接

查询员工信息，要求显示：员工号，姓名，月薪，部门名称

SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            2450 ACCOUNTING
      7839 KING             5000 ACCOUNTING
      7934 MILLER           1300 ACCOUNTING
      7566 JONES            2975 RESEARCH
      7902 FORD             3000 RESEARCH
      7876 ADAMS            1100 RESEARCH
      7369 SMITH             800 RESEARCH
      7788 SCOTT            3000 RESEARCH
      7521 WARD             1250 SALES
      7844 TURNER           1500 SALES
      7499 ALLEN            1600 SALES
      7900 JAMES             950 SALES
      7698 BLAKE            2850 SALES
      7654 MARTIN           1250 SALES

不等值连接

查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别

SELECT E.EMPNO,E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;--BETWEEN AND 小值在前，大值在后。

     EMPNO ENAME             SAL      GRADE
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7900 JAMES             950          1
      7876 ADAMS            1100          1
      7521 WARD             1250          2
      7654 MARTIN           1250          2
      7934 MILLER           1300          2
      7844 TURNER           1500          3
      7499 ALLEN            1600          3
      7782 CLARK            2450          4
      7698 BLAKE            2850          4
      7566 JONES            2975          4
      7788 SCOTT            3000          4
      7902 FORD             3000          4
      7839 KING             5000          5

外连接

通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中。左外连接：当连接条件不成立时，等号左边的表仍被包含；右外连接：当连接条件不成立时，等号右边的表仍被包含。

按部门统计员工人数，要求显示：部门号，部门名称，人数

SELECT D.DEPTNO 部门号,D.DNAME 部门名称,COUNT(E.EMPNO)人数 FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME;--右外连接+号写在等号左边，左外连接右边。

    部门号 部门名称             人数
---------- -------------- ----------
        10 ACCOUNTING              3
        40 OPERATIONS              0
        20 RESEARCH                5
        30 SALES                   6
SELECT D.DEPTNO 部门号,D.DNAME 部门名称,COUNT(E.EMPNO)人数 FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME;--等值连接查询出来的结果

    部门号 部门名称             人数
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                5
        30 SALES                   6

自连接

查询员工姓名和员工的老板的姓名

SELECT E.ENAME 员工姓名,B.ENAME 老板姓名 FROM EMP E,EMP B WHERE E.MGR=B.EMPNO;
--通过别名，将同一张表视为多张表。
员工姓名   老板姓名
---------- ----------
FORD       JONES
SCOTT      JONES
TURNER     BLAKE
ALLEN      BLAKE
WARD       BLAKE
JAMES      BLAKE
MARTIN     BLAKE
MILLER     CLARK
ADAMS      SCOTT
BLAKE      KING
JONES      KING
CLARK      KING
SMITH      FORD

自连接存在的问题和解决方法

自连接不适合大表，其产生的笛卡尔集和原来的表至少是平方的关系。解决方法：层次查询，层次查询在某些情况下可以取代自连接，本质上是单表查询。

SELECT LEVEL,EMPNO,ENAME,SAL,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH MGR IS NULL ORDER BY 1;

     LEVEL      EMPNO ENAME             SAL        MGR
---------- ---------- ---------- ---------- ----------
         1       7839 KING             5000
         2       7566 JONES            2975       7839
         2       7698 BLAKE            2850       7839
         2       7782 CLARK            2450       7839
         3       7902 FORD             3000       7566
         3       7521 WARD             1250       7698
         3       7900 JAMES             950       7698
         3       7934 MILLER           1300       7782
         3       7499 ALLEN            1600       7698
         3       7788 SCOTT            3000       7566
         3       7654 MARTIN           1250       7698
         3       7844 TURNER           1500       7698
         4       7876 ADAMS            1100       7788
         4       7369 SMITH             800       7902

 SELECT LEVEL,EMPNO,ENAME,SAL,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH EMPNO=7839 ORDER BY 1;
--层次查询没有自连接查询的结果直观
     LEVEL      EMPNO ENAME             SAL        MGR
---------- ---------- ---------- ---------- ----------
         1       7839 KING             5000
         2       7566 JONES            2975       7839
         2       7698 BLAKE            2850       7839
         2       7782 CLARK            2450       7839
         3       7902 FORD             3000       7566
         3       7521 WARD             1250       7698
         3       7900 JAMES             950       7698
         3       7934 MILLER           1300       7782
         3       7499 ALLEN            1600       7698
         3       7788 SCOTT            3000       7566
         3       7654 MARTIN           1250       7698
         3       7844 TURNER           1500       7698
         4       7876 ADAMS            1100       7788
         4       7369 SMITH             800       7902