多表查询

来源：互联网发布：遇到网络诈骗怎么半编辑：程序博客网时间：2024/05/16 01:24

笛卡尔全集

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

过滤得到笛卡尔集

过滤或者不过滤后, 可以进行分组, 分组后可以用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.deptnoSQL> /ENAME             SAL DNAME---------- ---------- --------------CLARK            2450 ACCOUNTINGKING             5000 ACCOUNTINGMILLER           1300 ACCOUNTINGJONES            2975 RESEARCHFORD             3000 RESEARCHADAMS            1100 RESEARCHSMITH             800 RESEARCHSCOTT            3000 RESEARCHWARD             1250 SALESTURNER           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.hisalSQL> /ENAME             SAL      GRADE---------- ---------- ----------SMITH             800          1JAMES             950          1ADAMS            1100          1WARD             1250          2MARTIN           1250          2MILLER           1300          2TURNER           1500          3ALLEN            1600          3CLARK            2450          4BLAKE            2850          4JONES            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 JONESSCOTT boss is JONESJAMES boss is BLAKETURNER boss is BLAKEMARTIN boss is BLAKEWARD boss is BLAKEALLEN boss is BLAKEMILLER boss is CLARKADAMS boss is SCOTTCLARK boss is KINGBLAKE boss is KINGE.ENAME||'BOSSIS'||B.ENAME-----------------------------JONES boss is KINGSMITH boss is FORDKING 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 1SQL> /     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

0 0