自连接存在的问题

1.不适合操作大表---数据运算太大，产生的笛卡尔积的大小数据的平方

解决办法：层次查询----一张表查询，本质是单表查询

select level,empno,ename,sal,mgr

from emp

connect by prior empno=mgr

start with mgr is null

order by 1;

connect by 上一层的员工号=老板号

start with mgr is null 一定要从顶层开始查

必须在查询语句中把查询的伪列给显示出来level

*******************************************************

SQL> select count(*) from emp e,emp b;

  COUNT(*)

----------

       196

SQL> select level,empno,ename,sal,mgr

  2  from emp

  3  connect by prior empno=mgr

  4  start with mgr is null

  5  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

     LEVEL      EMPNO ENAME             SAL        MGR

---------- ---------- ---------- ---------- ----------

         3       7844 TURNER           1500       7698

         4       7876 ADAMS            1100       7788

         4       7369 SMITH             800       7902

已选择14行。

自连接存在的问题：不适合操作大表，【自连接产生的是笛卡尔集】

解决办法：层次查询【本质上是单表查询】

层次查询的原理

from emp 

connect by 上一层的员工号=老板号   ——》等价于如下所示的语句：

    层次查询必须给出从哪一个节点开始遍历：

select empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno=7566

    若从根节点开始遍历，可以遍历整个树，只有根节点没有老板号，可以这样写查询语句

select empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno is null

    层次查询有伪列level，必须查询该列才能显示出来

select level, empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno is null

order by 1

层次查询：自连接的优化查询。

level:树的深度，层次。类似序列号。

connect by pripro  上一层的员工号等于这层的老板号。

from emp connect by pripro empno=mgr 

start with mgr is null;

自连接存在的问题：
（1）自连接不适合操作大表，也就是记录多的表，原因是自连接至少有两张表参与，并进行笛卡尔全集，连接之后的记录数就是单张表记录数的平方（笛卡尔积行数是两张表行数的乘积），如果有三张表记录数就是原来的三次方，如果原来的表包含的记录数过多，连接之后的结果就会很大，所以自连接不适合操作大表。解决办法：层次查询。注意层次查询是单表查询，不存在第二张表！因为只有在一张表的情况下才不会产生笛卡尔积，才能解决这个问题。
层次查询在某些情况下可以替代自连接，本质上是单表查询。
例：
select empno,ename,sal,mgr
from emp   //层次查询只涉及一张表
connect by prior empno=mgr   //层次查询的条件不用where表示，而用connect by表示，上一层的员工号=当前曾的老板号
start with mgr is null或者start with empno=7839;   //stsrt with表示从何处开始遍历查询，只有从根节点开始时才可以使用 * is null 的格式，其余节点都只能使用第二种格式。
此外层次查询的树的深度用level表示，可以在select语句中加入level就可以得到深度的查询结果，还可以对查询结果进行排序如order by 1;
比较自连接和层次查询的优劣：
自连接得到的查询结果比较直观但是不适合操作大表；
层次查询只涉及一张表，不产生笛卡尔积，但是得到的结果不够直观。
所以不同的查询语句有不同的特点，不是万能的！

testtetetete

这里第5行指的是从根节点开始遍历，第9行表示从具体某个节点7839开始遍历。

level为伪列，树的深度。相关关键字：

connect by prior A=BB

star with A=XXX;

总结：

‌自链接不适合查询大表(因为会产生笛卡尔里)，

可以层次查询(树状结构，可以从任意一个节点向下遍历，缺点是结果不够直观)

                层次查询：

             自连接存在的问题：

            （1）自连接不适合操作大表，也就是记录多的表，原因是自连接至少有两张表参与，并进行笛卡尔全集，连接之后的记录数就是单张表记录数的平方（笛卡尔积行数是两张表行数的乘积），如果有三张表记录数就是原来的三次方，如果原来的表包含的记录数过多，连接之后的结果就会很大，所以自连接不适合操作大表。解决办法：层次查询。注意层次查询是单表查询，不存在第二张表！因为只有在一张表的情况下才不会产生笛卡尔积，才能解决这个问题。

            层次查询在某些情况下可以替代自连接，本质上是单表查询。

            例： select empno,ename,sal,mgr from emp

                                 //层次查询只涉及一张表

                        connect by prior empno=mgr

                                //层次查询的条件不用where表示，而用connect by表示上下层关系，上一层的员工号=当前这层的老板号，关键字prior表示相邻两层中的上一层。

                        start with mgr is null或者start with empno=7839;

                            //stsrt with表示起始条件，从何处开始遍历查询，只有从根节点开始时才可以使用start with..... is null 的格式，其余节点都只能使用第二种格式。

            此外层次查询的树的深度用level表示，可以在select语句中加入level就可以得到深度的查询结果，还可以对查询结果进行排序如order by 1;

            比较自连接和层次查询的优劣： 自连接得到的查询结果比较直观，但是不适合操作大表； 层次查询只涉及一张表单表查询，不会产生笛卡尔积，但是得到的结果不够直观。 所以不同的查询语句有不同的特点，不是万能的！

层次查询语句SQL

树查询-层次查询（解决自连接数据庞大问题）

select level,empno,ename,sal,mgr --level 伪列 层级

from emp

connect by prior empno=mgr --prior 上层

start with empno=001//start with mgr is null

order by level/order by 1;

1. 自连接缺陷：多表查询》》笛卡尔集》》》会带来至少平方级别的数据累计（不适合大表）

2. 引出层次查询（实际是是单表查询  没有笛卡尔集）

3. 需要遍历树结构 

4. 节点连接条件（上一节点的员工号是下一节点的老板）connect by prior empno=mgr

5. 需要设置开始节点 start with

6. 各有优缺点：自连接直观（但不适合大表） 层次查询性能更好（但是不直观）

自连接存在的问题

select e.ename 员工姓名,b.ename 老板姓名

from emp e,emp b

where e.mgr=b.empno;

获得笛卡尔全集

select count(*) from emp e,emp b;

不适合操作大表

层次查询（单表查询）

自连接存在的问题

1. 不适合操作大表。

2. 解决办法：层次查询

3. 层次查询在某些情况下，可以替代自连接，本质上是一个单表查询。

4. 
   

层次查询关键字：connect by prior

/** 等值连接 **/

SELECT e.empno, e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;

/** 不等值连接 **/

SELECT e.empno, e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal and hisal;

/**右外连接**/

SELECT d.deptno "部门号", d.dname "部门名称", "COUNT"(e.empno) "人数" FROM emp e, dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno, d.dname ORDER BY d.deptno;

/**左外连接**/

SELECT d.deptno "部门号", d.dname "部门名称", "COUNT"(e.empno) "人数" FROM emp e, dept d WHERE d.deptno=e.deptno(+) GROUP BY d.deptno, d.dname ORDER BY d.deptno;

/** 自连接：通过对同一张表起不同的别名变成多张表 **/

SELECT e.ename "员工姓名", b.ename "老板姓名" FROM emp e, emp b WHERE e.mgr=b.empno;

/** 层次查询：取代自连接，是一个单表查询 **/

SELECT empno, ename, sal, mgr, LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr is null ORDER BY 5;

层次查询

某些情况下，可以替代自连接

本质上，是一个单表查询

自连接：

select level【伪列，表示树的深度】, empno, ename, sal, mgr

from emp

connect by prior empno=mgr【前者的员工号等于后者的老板号，在树中即上一层的员工号等于当前层的老板号】

start with mgr is null;【从没有老板的那个员工即树的根节点开始遍历整棵树】

//相当于 start with empno=某个员工号【遍历以该员工为根节点的树】;

order by 1;

自连接：当显示结果需要通过自连接实现，则需要给表一个别名，虚拟两张表进行连接查询。

自连接优点：显示结果清晰明了     缺点：多表连接查询会产生笛卡尔积

层次查询可以解决自连接查询产生的问题

层次查询本质是一个单表查询

相邻两层的上一层的员工号，用关键字prior实现

connect by（connect by表示连接） 上一层的员工号=老板号（条件）

start with empno=7839; ---从哪个节点开始找，此处可以从任意一个节点开始

level为层次查询的伪列，要显示层次查询中的数据，必须显示加上level关键字

总语句：

select level,empno,ename,sal,mgr from emp

connect by prior empno=mgr start with empno=7839;

层次查询level是深度

分层查询使用connect by prior empno=mgr start with mgr is null/start with empno=7658;分层查询有一个伪劣是level，代表着分层查询时的层次，从第一层开始算。

select level,empno,ename ,sal,mgr from emp

connect by prior empno=mgr

start with mgr is null

orader by level;

start with empno =7839 表示从这一列开始

 自连接不适合大表

层次查询：

层次查询：“connect by prior...”语句

select level, empno,ENAME,sal,MGR

from emp

connect by prior empno=mgr

start with mgr is null

order by 1;

1. 自连接缺陷：多表查询》》笛卡尔集》》》会带来至少平方级别的数据累计（不适合大表）

2. 引出层次查询（实际是是单表查询  没有笛卡尔集）

3. 需要遍历树结构 

4. 节点连接条件（上一节点的员工号是下一节点的老板）connect by prior empno=mgr

5. 需要设置开始节点 start with

6. 各有优缺点：自连接直观（但不适合大表） 层次查询性能更好（但是不直观）

利用树的深度原理去实现层次查询。

select empno,bname//查询员工号和老板号 from emp 

connect by prior empno=mgr//员工号的上一层（老板号）=老板号 

 start with empno=2431; 

根节点还可以 start with mgh is null;（根节点的老板号为空）

层次查询：在某些情况下可以替代自连接，避免笛卡尔全集，本质上是一个单表查询。

添加level，start with  ,order by 1。