Oracle的各种表连接

以emp,dept为例

SQL> select * from emp; 

  查询结果  
 EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10
      1234 dfff       aaa	      7369 06-JUN-18	   1600        100	   10

15 rows selected.

SQL> select * from dept;

 查询结果：
DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

1.内连接 inner join/join

与select 多表查询的结果一样

例：

列出所有“CLERK”的员工姓名及部门名称

SQL> select e.ename,d.dname from dept d,emp e where d.deptno=e.deptno and e.job='CLERK';  #多表查询

SQL> select e.ename,d.dname from emp e join dept d on (e.deptno=d.deptno) where e.job='CLERK'; #内连接方式，on后面的（）可以省略

查询结果：
  ENAME	   DNAME
---------- --------------
MILLER	   ACCOUNTING
SMITH	   RESEARCH
ADAMS	   RESEARCH
JAMES	   SALES

2.左外连接 left join/left outer join

显示左表的全部数据和左表右表匹配后的数据

select * from t_A a left join t_B b on a.id=b.id;

t_A 看做左表
t_B 看做右表即匹配表
结果集是t_A的所有数据，t_A与t_B匹配后的数据，t_B记录不足的地方为null

或者
select * from t_A a t-B b where a.id=b.id(+);   #"+" 表示补充，哪个表有“+” 哪个表就是匹配表

举一个栗子：

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

SQL> select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno

或者

SQL> select d.dname,e.* from dept d,emp e where d.deptno=e.deptno(+);

查询结果：
 DNAME            EMPNO ENAME      JOB          MGR HIREDATE       SAL         COMM     DEPTNO
-------------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
ACCOUNTING         7782 CLARK      MANAGER         7839 09-JUN-81      2450              10
ACCOUNTING         1234 dfff         aaa         7369 06-JUN-18      1600          100      10
ACCOUNTING         7839 KING         PRESIDENT          17-NOV-81      5000              10
ACCOUNTING         7934 MILLER     CLERK         7782 23-JAN-82      1300              10
RESEARCH         7566 JONES      MANAGER         7839 02-APR-81      2975              20
RESEARCH         7369 SMITH      CLERK         7902 17-DEC-80       800              20
RESEARCH         7788 SCOTT      ANALYST         7566 19-APR-87      3000              20
RESEARCH         7902 FORD         ANALYST         7566 03-DEC-81      3000              20
RESEARCH         7876 ADAMS      CLERK         7788 23-MAY-87      1100              20
SALES             7844 TURNER     SALESMAN         7698 08-SEP-81      1500        0      30
SALES             7499 ALLEN      SALESMAN         7698 20-FEB-81      1600          300      30
SALES             7900 JAMES      CLERK         7698 03-DEC-81       950              30
SALES             7521 WARD         SALESMAN         7698 22-FEB-81      1250          500      30
SALES             7654 MARTIN     SALESMAN         7698 28-SEP-81      1250         1400      30
SALES             7698 BLAKE      MANAGER         7839 01-MAY-81      2850              30
OPERATIONS

3.右外连接 right join/rignt outer join

以右表为基础，显示右表的所有记录，以及左表与右表的匹配记录

支持“+”

4.全外连接 full join/full outer join

左右表不作限制，所有的记录都显示，不支持“+”

5.自连接 self join

例：查看所有员工的姓名及其直接上级的姓名

SQL> select a.ename as leader,b.ename from emp a,emp b where a.empno=b.mar;

查询结果：
LEADER	   ENAME
---------- ----------
SMITH	   dfff
JONES	   FORD
JONES	   SCOTT
BLAKE	   ALLEN
BLAKE	   WARD
BLAKE	   JAMES