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=mgr100   4  start with mgr is null101   5  order by 1102   6  ;103  104      LEVEL EMPNO ENAME        MGR105 ---------- ----- ---------- -----106          1  7839 KING       107          2  7566 JONES       7839108          2  7698 BLAKE       7839109          2  7782 CLARK       7839110          3  7844 TURNER      7698111          3  7900 JAMES       7698112          3  7654 MARTIN      7698113          3  7521 WARD        7698114          3  7934 MILLER      7782115          3  7902 FORD        7566116          3  7499 ALLEN       7698117          4  7369 SMITH       7902118  119 12 rows selected