Oracle多表关联查询

在实际的应用系统开发中会涉及多个数据表，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关联，这样当用户查询某一个表的信息时，很可能需要查询关联表的信息，这就是多表关联查询。

1、内连接

内连接是一种常见的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中，INNER关键字可以省略，当只使用JOIN关键字时，语句只表示内连接操作。在使用内连接查询多个表时，必须在FROM子句之后定义一个ON子句，该子句用来指定两个表实现内连接的“连接条件”。需要注意的是，在内连接的检索结果中，所有记录行都是满足连接条件的。

【实例】在SCOTT模式下，通过DEPTNO字段来内连接EMP表和DEPT表，并检索这两个表中相关字段的信息。

--内连接
SELECT E.EMPNO AS 员工编号,E.ENAME AS 员工名称,D.DNAME AS 部门 
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO

 

2、外连接

使用内连接进行多表查询时，返回的查询结果中只包含符合查询条件和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除了返回所有匹配的行外，还会返回一部分或全部不匹配的行，这主要取决于外连接的种类。

左外连接：关键字LEFT OUTER JOIN或LEFT JOIN。

右外连接：关键字RIGHT OUTER JOIN或RIGHT JOIN。

完全外连接：关键字FULL OUTER JOIN或FULL JOIN。

与内连接不同的是，外连接不只列出与连接条件匹配的行，还能够列出左表（左外连接时）、右表（右外连接时）或两个表（完全连接时）中所有符合搜索条件的数据行。在执行完全外连接时，Oracle会执行一个完整的左外连接和右外连接查询，然后将查询结果合并，并消除重复的记录行。

【实例】实现在EMP表和DEPT表之间通过DEPTNO列进行左外连接、右外连接、完全外连接。

--插入一条测试数据
INSERT INTO EMP(EMPNO,ENAME,JOB)VALUES(9527，'EAST','SALESMAN');
COMMIT;

--左外连接
SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

--右外连接
SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

--完全外连接
SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E FULL JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

 

3、自然连接

自然连接是指在检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行行自动连接。在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle系统自动完成，自然连接使用“NATURAL JOIN”关键字。

【实例】在EMP表中检索工作（SAL字段）大于2000的记录，并实现EMP表与DEPT表的自然连接。

--自然连接
SELECT EMPNO,ENAME,JOB,DNAME
FROM EMP NATURAL JOIN DEPT
WHERE SAL > 2000;

由于自然连接强制要求表之间必须具有相同的列名称，这样容易在设计表时出现不可预知的错误，所有在实际应用系统开发中很少用到自然连接。但这毕竟是一种多表关联查询数据的方式，在某些特定情况下还是有一定的使用价值。

 

4、自连接

在应用系统开发中，用户可能会拥有“自引用式”的外键。“自引用式”外键是指表中的一个列可以是该表主键的一个外键。比如，在SCOTT模式下，EMP表中某一行的MGR列值（管理者列）可能是另一行的EMPNO列值（员工列），因为管理者本身也是公司的员工。这样用户就可以通过MGR列与EMPNO列的关系，实现查询某个管理者所管理的下属员工信息。

【实例】在SCOTT模式下，查询所有管理者的下属员工信息。

--自连接
SELECT EM2.ENAME AS 上层管理者,EM1.ENAME AS 下属员工
FROM EMP EM1 LEFT JOIN EMP EM2
ON EM1.MGR = EM2.EMPNO
ORDER BY EM1.MGR;

 

5、交叉连接

交叉连接实际上就是不需要任何连接条件的连接，它使用CROSS JOIN关键字来实现，交叉连接的执行结果是一个笛卡儿积，这种查询结果是非常冗余的，但可以通过WHERE子句来过滤出有用的记录信息。

【实例】在SCOTT模式下，通过交叉连接DEPT表和EMP表，计算出查询结果的行数。

--交叉连接
SELECT COUNT(*) FROM DEPT CROSS JOIN EMP;