oracle基础-多表关联查询

一、概述

        在实际应用系统开发中会设计多个数据表，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关系，当用户查询某一个表的信息时，很可能需要查询关联数据表的信息，这就是多表关联查询。SELECT语句自身是支持多表关联查询的，多表关联查询要比单表查询复杂的多。在进行多表关联查询时，可能会涉及表别名、内连接、外连接、自然连接和交叉连接等概念，下面将对这些内容进行讲解。

二、表的别名

        在多表关联查询时，如果多个表之间存在同名的列，则必须使用表名来限定列的引用。例如，在SCOTT模式中，DEPT表和EMP表都有DEPTNO列，当用户使用该列关联查询两个表时，就需要通过指定表名来区分这两个列的归属。但是，随着查询变得越来越复杂，语句就会因为每次限定列必须输入表名而变得冗长。对于这种情况，SQL提供了设定表别名的机制，使用简短的表别名可以代替原有较长的表名称，这样就大大缩减语句的长度。

        【例2.1】在SCOTT模式下，通过DEPTNO（部门号）列来关联EMP表和DEPT表，并检索这两个表中相关字段的信息，代码及运行结果如下：

select e.empno 员工编号,e.ename 员工姓名,d.dname 部门 from dept d,emp e where d.deptno = e.deptno and e.job = 'MANAGER';

        在上面的SELECT语句中，FROM子句最先执行，然后才是WHERE子句和SELECT子句，这样在FROM子句中指定表的别名后，当需要限定引用列时，其他所有子句都可以使用表的别名。

        另外，还需要注意一点，一旦在FROM子句中为表指定了别名，则必须在剩余的子句中都是用表的别名，而不允许再使用原来的表名称，否则，将出现【例2.2】错误提示。

【例2.2】 

select e.empno 员工编号,e.ename 员工姓名,d.dname 部门 from dept d,emp e where d.deptno = e.deptno and emp.job = 'MANAGER';

 总结：

        1、表的别名在FROM子句中定义，别名放在表名之后，之间用空格隔开。

        2、别名一经定义，在整个查询语句中就只能使用表的别名而不能在使用表名。

        3、表的别名只在所定义的查询语句中有效。

        4、应该选择有意义的别名，表的别名最长为30个字符，但越短越好。

三、内连接

        内连接是一种常用的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中INNER关键字可以省略，当只使用JOIN关键字时，语句默认表示内连接操作。在使用内连接查询多个表时，必须在FROM子句之后定义一个ON子句，ON子句指定内连接操作列与连接条件匹配的数据行，使用比较运算符比较被连接列的值。简单来说，内连接就是使用JOIN指定用于连接的两个表，使用ON指定表的连接条件。若进一步限制查询范围，则可以直接在后面添加WHERE子句。内连接的语法格式如下：

SELECT columns_list
FROM table_name1 [INNER] JOIN table_name2
ON join_condition
[WHERE]

其中:columns_list：字段列表

        table_name1 和 table_name2 ：两个要实现内连接的表。

        join_condition：实现内连接的条件表达式

        WHERE ：使用where子句进一步限制查询范围。

【例3.1】在scott模式下，通过deptno字段来内连接emp表和dept表，查询dept表中部门名称为 SALES,并检索这两个表中相关的字段信息。

select e.empno 员工编号,e.ename 员工姓名,d.dname 部门 
from emp e inner join dept d 
on e.deptno = d.deptno
where d.dname = 'SALES';

四、外连接

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

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

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

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

与内连接不同的是，外连接不只列出与连接条件匹配的数据，还能够列出左表（左外连接时）、右表（右外连接时）、两个表（全部外连接时）中所有符合搜索条件的数据。

        4.1 左外连接

        左外连接的查询结果中不仅包含了满足连接条件的数据，还包含左表中不满足连接条件的数据。

        【例4.1.1】首先在emp表中插入一条新数据（注意没有为deptno和dname列插入值，即它们的值是null），然后实现emp表和dept表之间通过deptno列进行左外连接。

插入

insert into emp (empno,ename,job) values(9527,'EAST','SALESMAN');

查询

select e.empno,e.ename,e.job,d.deptno,d.dname 
from emp e left join dept d
on e.deptno = d.deptno;

结果

        从上面的查询结果中可以看到，虽然新插入的数据deptno值为null，但该行记录让然出现在查询结果中，说明左外连接的查询结果会包含左表中不满足“连接条件”的数据。 

        4.2   右外连接

        同样道理，右外连接的查询结果中不仅包含了满足连接条件的数据行，而且还包含右表中不满足连接条件的数据行。

         【例4.2.1】在SCOTT模式下，实现emp表和dept表之间通过deptno列进行右外连接。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e
right join dept d 
on e.deptno = d.deptno

        运行结果如下

          

 ·        从上面的查询结果中可以看到，虽然部门编号为40的数据行在emp表中还没有员工记录，但它却出现在查询结果中，这说明右外连接的查询结果会包含由表中不满足“连接条件”的数据行。

        在外连接中也可以使用外连接的连接运算符，外连接的连接运算符为“(+)”，该连接运算符可以放在等号的左边，也可以放在等号的右边，但是一定要放在缺少信息的那一边，比如放在e.deptno所在的一方。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e,dept d
where e.deptno (+)= d.deptno

运行结果

使用(+)操作符时应注意：

        1、当使用(+)操作符执行外连接时，如果在WHERE子句中包含多个条件，则必须在所有条件中都包含(+)操作符。

        2、(+)操作符只适用于列，而不能用在表达式上。

        3、(+)操作符不能与ON和IN操作符一起使用。

4.3 完全外连接

在执行完全外连接时，Oracle会执行一个完成的左外连接和右外连接查询，然后将查询结果合并，并消除重复的数据。

【例4.3.1】 在SCOTT模式下，实现emp表和dept表之间通过deptno列进行完全外连接。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e
full join dept d 
on e.deptno = d.deptno

 

4.4 自然连接

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

【4.4.1】 在emp表中检索工资(sal字段)大于2000的记录，并实现emp表与dept表的自然连接。

select empno,ename,job,dname
from emp natural join dept
where sal > 2000

    由于自然连接要求表之间必须具有相同的列名称，这样容易在设计表时出现不可预知的错误，所以在实际应用中很少用到自然连接。另外需要注意的是，在使用自然连接时，不能为列指定限定词（即表名或表的别名），否则Oracle会弹出“ORA-25155:NATURAL连接中使用的列不能有限定词”的错误提示.

4.5 自连接

     在实际应用中，用户可能会拥有“自引用式”的外键。“自引用式”外键是指表中的一列可以使该表住建的一个外键。

     自连接主要用在自参考表上显示上下级关系或者层次关系。自参照表是指在同一张表的不同列之间具有参照关系活着主从关系的表。例如，emp表包含empno（雇员号）和mgr(管理员号)列，两者之间就具有参照关系。这样用户就可以通过mgr列与empno列的关系，实现查询某个管理者所管理的下属员工信息。

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

select e2.ename 上层管理者,e1.ename 下属员工
from emp e1 
left join emp e2 
on e1.mgr = e2.empno
order by e1.mgr

 4.6 交叉连接

交叉连接实际上就是不需要任务连接条件的连接，它使用CROSS JOIN关键字来实现，语法格式：

select colums_list

from talble_name1 cross join table_name2

交叉连接的执行结果是一个笛卡尔积，这种查询结果是非常冗余的，但是可以通过WHERE子句来过滤出有用的记录信息。

【4.6.1】在SCOTT模式下，通过交叉连接dept表和emp表，计算出查询结果的行数。

select count(*)
from emp cross join dept