多表查询

笛卡尔全集

两张表进行连接时可以产生笛卡尔全集, 列数为两表各自列相加, 行数为两表相乘

过滤得到笛卡尔集

过滤或者不过滤后, 可以进行分组, 分组后可以用having在过滤一次

等值连接过滤

通过1对一的判定对笛卡尔全集内数据进行挑选

  1  select e.ename, e.sal, d.dname
  2  from emp e, dept d
  3  where e.deptno=d.deptno
  4* order by d.deptno
SQL> /

ENAME             SAL DNAME
---------- ---------- --------------
CLARK            2450 ACCOUNTING
KING             5000 ACCOUNTING
MILLER           1300 ACCOUNTING
JONES            2975 RESEARCH
FORD             3000 RESEARCH
ADAMS            1100 RESEARCH
SMITH             800 RESEARCH
SCOTT            3000 RESEARCH
WARD             1250 SALES
TURNER           1500 SALES

不等值连接过滤

可以使用 大于 小于 between 判定符过滤, 同样使用两表中的列或者其他常亮表达式

  1  select e.ename, e.sal, s.grade
  2  from emp e, salgrade s
  3* where e.sal between s.losal and s.hisal
SQL> /

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4

外连接

上面两种过滤中, 当某一边不存在 与例一边对应时, 仍然想要主要那一边全行存在;

保证某一边全行存在就是目的

SQL> select * from emp where deptno=40;

未选定行

外连接的(+)放在谁旁边, 就代表谁是被连接进来的次要表;

为保证主判定列得到全行, 外连列会用一个 null列补充一行;

左右连接的叫法刚好和 (+) 在的方向相反

  1  select d.deptno, d.dname, e.ename
  2  from dept d, emp e
  3* where d.deptno=e.deptno(+)
SQL> /

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          WARD
        20 RESEARCH       JONES
        30 SALES          MARTIN
        30 SALES          BLAKE
        10 ACCOUNTING     CLARK
        20 RESEARCH       SCOTT
        10 ACCOUNTING     KING
        30 SALES          TURNER
        20 RESEARCH       ADAMS

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        30 SALES          JAMES
        20 RESEARCH       FORD
        10 ACCOUNTING     MILLER
        40 OPERATIONS

其中 40 号部门没有任何员工, 但却仍然生成了 一列 null 右列来充数, 这样的话, 对笛卡尔集分组时, 分组函数根据 null特性自行使用

自连接

把表和表本身连接成笛卡尔全集;

语法上用 表别名就可以

同样可以继续使用 左右过滤

  1  select e.ename||' boss is '||b.ename
  2  from emp e, emp b
  3* where e.mgr=b.empno(+)
SQL> /

E.ENAME||'BOSSIS'||B.ENAME
-----------------------------
FORD boss is JONES
SCOTT boss is JONES
JAMES boss is BLAKE
TURNER boss is BLAKE
MARTIN boss is BLAKE
WARD boss is BLAKE
ALLEN boss is BLAKE
MILLER boss is CLARK
ADAMS boss is SCOTT
CLARK boss is KING
BLAKE boss is KING

E.ENAME||'BOSSIS'||B.ENAME
-----------------------------
JONES boss is KING
SMITH boss is FORD
KING boss is

层次查询

层次查询可以避免 先决笛卡尔全集过大的问题

查询得到结果集是一棵树

首先用 connect by prior empno=mgr 确定上下级关系

start with mgr is null 找出根

层次查询中会自动生成一列 level 表示树的深度

可以生成 多颗独立的树, 当 出现多个根的时候

相比联表查询那样粗暴的生成笛卡尔全集的 N^2,  层次查询生成的全集比较少

  1  select level, empno, ename, mgr
  2  from emp
  3  connect by prior empno=mgr
  4  start with mgr is null
  5* order by 1
SQL> /

     LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
         1       7839 KING
         2       7566 JONES            7839
         2       7698 BLAKE            7839
         2       7782 CLARK            7839
         3       7902 FORD             7566
         3       7521 WARD             7698
         3       7900 JAMES            7698
         3       7934 MILLER           7782
         3       7499 ALLEN            7698
         3       7788 SCOTT            7566
         3       7654 MARTIN           7698

     LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
         3       7844 TURNER           7698
         4       7876 ADAMS            7788
         4       7369 SMITH            7902