oracle 多表查询

浏览数：30 / 时间：2015年06月12日

Oracle 连接:

　　Equijoin：等值连接 rowa=rowb

　　Non-equijoin：不等值连接

　　Outer join：外连接分为左链接和右链接，左链接：rowa=rawb(+);右链接rowa（+）=rawb

　　Self join：自连接:利用一个表可以有多个别名，自连接不太适合操作大表

  1 SQL> --等值连接
  2 SQL> select e.empno,e.ename,e.sal,d.dname
  3   2  from emp e,dept d
  4   3  where e.deptno=d.deptno;
  5  
  6 EMPNO ENAME            SAL DNAME
  7 ----- ---------- --------- --------------
  8  7369 SMITH         800.00 RESEARCH
  9  7499 ALLEN        1600.00 SALES
 10  7521 WARD         1250.00 SALES
 11  7566 JONES        2975.00 RESEARCH
 12  7654 MARTIN       1250.00 SALES
 13  7698 BLAKE        2850.00 SALES
 14  7782 CLARK        2450.00 ACCOUNTING
 15  7839 KING         5000.00 ACCOUNTING
 16  7844 TURNER       1500.00 SALES
 17  7900 JAMES         950.00 SALES
 18  7902 FORD         3000.00 RESEARCH
 19  7934 MILLER       1400.00 ACCOUNTING
 20  
 21 12 rows selected
 22 
 23 SQL> --不等值连接
 24 SQL> select e.ename,e.sal,s.grade
 25   2  from emp e,salgrade s
 26   3  where e.sal between s.losal and s.hisal
 27   4  ;
 28  
 29 ENAME            SAL      GRADE
 30 ---------- --------- ----------
 31 SMITH         800.00          1
 32 JAMES         950.00          1
 33 WARD         1250.00          2
 34 MARTIN       1250.00          2
 35 MILLER       1400.00          2
 36 TURNER       1500.00          3
 37 ALLEN        1600.00          3
 38 CLARK        2450.00          4
 39 BLAKE        2850.00          4
 40 JONES        2975.00          4
 41 FORD         3000.00          4
 42 KING         5000.00          5
 43  
 44 12 rows selected
 45 
 46 SQL> --外连接
 47 SQL> select d.deptno,d.dname,count(e.empno)
 48   2  from dept d,emp e
 49   3  where d.deptno=e.deptno
 50   4  group by d.deptno,d.dname
 51   5  ;
 52  
 53 DEPTNO DNAME          COUNT(E.EMPNO)
 54 ------ -------------- --------------
 55     10 ACCOUNTING                  3
 56     20 RESEARCH                    3
 57     30 SALES                       6
 58 
 59 
 60 SQL> --左外连接
 61 SQL> select d.deptno,d.dname,count(e.empno)
 62   2  from dept d,emp e
 63   3  where d.deptno=e.deptno(+)
 64   4  group by d.deptno,d.dname
 65   5  order by deptno
 66   6  ;
 67  
 68 DEPTNO DNAME          COUNT(E.EMPNO)
 69 ------ -------------- --------------
 70     10 ACCOUNTING                  3
 71     20 RESEARCH                    3
 72     30 SALES                       6
 73     40 OPERATIONS                  0
 74 
 75 SQL> --自连接
 76 SQL> select e.ename 员工,b.ename 老板 
 77   2  from emp e,emp b
 78   3  where e.empno=b.mgr;
 79  
 80 员工       老板
 81 ---------- ----------
 82 FORD       SMITH
 83 BLAKE      ALLEN
 84 BLAKE      WARD
 85 KING       JONES
 86 BLAKE      MARTIN
 87 KING       BLAKE
 88 KING       CLARK
 89 BLAKE      TURNER
 90 BLAKE      JAMES
 91 JONES      FORD
 92 CLARK      MILLER
 93  
 94 11 rows selected
 95 
 96 SQL> --层次查询:  对同一张表的前后两次操作，进行连接
 97 SQL> select level,empno,ename,mgr
 98   2  from emp
 99   3  connect by prior empno=mgr
100   4  start with mgr is null
101   5  order by 1
102   6  ;
103  
104      LEVEL EMPNO ENAME        MGR
105 ---------- ----- ---------- -----
106          1  7839 KING       
107          2  7566 JONES       7839
108          2  7698 BLAKE       7839
109          2  7782 CLARK       7839
110          3  7844 TURNER      7698
111          3  7900 JAMES       7698
112          3  7654 MARTIN      7698
113          3  7521 WARD        7698
114          3  7934 MILLER      7782
115          3  7902 FORD        7566
116          3  7499 ALLEN       7698
117          4  7369 SMITH       7902
118  
119 12 rows selected