外连接、内连接

SQL> --外连接
SQL> --按部门统计员工人数：部门号 部门名称 人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno=d.deptno
  4  group by d.deptno,d.dname;

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

SQL> select * from dept;

    DEPTNO DNAME          LOC                                                   
---------- -------------- -------------                                         
        10 ACCOUNTING     NEW YORK                                              
        20 RESEARCH       DALLAS                                                
        30 SALES          CHICAGO                                               
        40 OPERATIONS     BOSTON                                                

SQL> select * from emp where deptno=40;

未选定行

SQL> /*
SQL> 希望： 对于某些不成立的记录，任然希望包含在最后的结果中
SQL> 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含
SQL>     写法:where e.deptno=d.deptno(+)
SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含
SQL>     写法: where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno,d.dname;

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

SQL> host cls

SQL> --自连接
SQL> --查询员工信息：员工姓名 老板姓名
SQL> set linesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                              
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                              
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                              
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                              
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                              
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                              
      7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                              
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                              
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7900 JAMES      CLERK           7698 03-12月-81            950                    30                                                                                                              
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                                                              
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                              

已选择 14 行。

SQL> --自连接： 通过表的别名，将同一张表视为多张表
SQL> select e.ename 员工姓名,b.ename 老板姓名
  2  from emp e,emp b
  3  where e.mgr=b.empno;

员工姓名   老板姓名                                                                                                                                                                                     
---------- ----------                                                                                                                                                                                   
FORD       JONES                                                                                                                                                                                        
SCOTT      JONES                                                                                                                                                                                        
JAMES      BLAKE                                                                                                                                                                                        
TURNER     BLAKE                                                                                                                                                                                        
MARTIN     BLAKE                                                                                                                                                                                        
WARD       BLAKE                                                                                                                                                                                        
ALLEN      BLAKE                                                                                                                                                                                        
MILLER     CLARK                                                                                                                                                                                        
ADAMS      SCOTT                                                                                                                                                                                        
CLARK      KING                                                                                                                                                                                         
BLAKE      KING                                                                                                                                                                                         

员工姓名   老板姓名                                                                                                                                                                                     
---------- ----------                                                                                                                                                                                   
JONES      KING                                                                                                                                                                                         
SMITH      FORD                                                                                                                                                                                         

已选择 13 行。

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

  COUNT(*)                                                                                                                                                                                              
----------                                                                                                                                                                                              
       196                                                                                                                                                                                              

SQL> --自连接不适合操作大表
SQL> --层次查询
SQL> select level,empno,ename,mgr
  2  from emp
  3  connect by prior empno=mgr
  4  start with mgr is null
  5  order by 1;

     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                                                                                                                                                             

已选择 14 行。