Oracle检索数据——多表关联查询

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

1、表别名

　　在进行多表查询时，如果多个表之间存在同名的列，则必须使用表名来限定列的引用。SQL 语言提供了设定表别名的机制，使用简短的表别名就可以替代原有较长的表名称，可以大大缩短语句的长度。

　　SQL > select e.empno as 员工编号，e.ename as 员工名称， d.dname as 部门 from emp e,dept d where e.deptno = d.deptno and e.job ="Manager";

　表示通过部门号（deptno）来管理emp表和dept表，并查询这两个表中相关字段的信息。

　注意：一旦在from 子句中为表指定了列名，则必须在剩余的子句中都使用表别名。　　

2、内连接

　　内连接是常用的多表关联查询，使用关键字inner join来实现，其中，inner 关键字可以省略，使用 join 即代表内联接。使用内联接查询多个表时，必须在 from 子句之后定义一个 on 子句，用来指定两个表实现内联接的“连接 条件”。

　　使用内联接进行多表查询时，返回的查询结果是只包含查询条件和连接条件的行，消除了与另一个表中任何行不匹配的行。

　　注意：在内联接的查询结果中，所有记录行都是满足连接条件的。

　　语法：

　　　　select column_list

　　　　from table_name1 [ innter ] join table_name2

　　　　on join_condition;

• 　columns_list : 字段列表
• 　table_name1 和 table_name2 :两个要实现内连接的表。
• join_condition: 实现内连接的条件表达式。

SQL > select e.empno as 员工编号， e.ename as 员工名称，d.dname as 部门 from emp e inner join dept d on e.deptno = d.deptno;

表示通过 deptno 字段来连接emp 表和 dept表，并查询两个表中相关的字段。

3、外连接

　　多表之间进行外连接时，除了返回所有匹配的行外，还会返回一部分或全部不匹配的行，这主要取决于外连接的种类，主要由以下3种：

　 (1) 左外连接：关键字为left Outer join 或 left join

　　左外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

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

　　表示从员工表（emp）表中查询出来所有的记录并查询部门表(dept)中两个deptno值相等的记录，如果没有相匹配的行，均为空值。

（2）右外连接：关键字为 right outer join 或 right join

　　右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

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

　　表示从部门表（dept）中查询出来所有的记录，并查询员工表（emp）表中与之deptno 对应的记录，如果没有匹配的行，均为空值。

（3） 完全外连接：关键字为 full outer join 或 full join

　　完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

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

　　表示查询两个表中所有的deptno的记录，如果对方的表中没有匹配的行，置为空值。

4、自然连接

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

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

　　表示在emp表中查询工资（sal）大于2000的记录，并实现emp表与dept 表的自然连接。

　注意：自然连接强制要求表之间必须具有相同的列名称，但是在实际开发中很少用到。在使用自然连接时，不能为列指定限定词（即表名或表的别名），否则Oracle 会报错。

5、自连接

　　在开发中，用户可能会拥有“自引用式”的外键，是指表中的一个列可以是该表主键的一个外键。

6、交叉连接

　　交叉连接实际上就是不需要任何连接条件的连接，它使用 cross join 关键字来实现。

　　语法： select colums_list from table_name1 cross join table_name2;

• colums_list ： 字段列表
• table_name： 两个实现交叉连接的表名。

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

　图解：

-------------------------------------------------------------------------------------------------------------------

　　　表A Id name 表B ID job p_id

　　　 1 张三 1 25 1

2 李四 2 36 2

3 王五 3 36 4

a.id与b.p_id存在关系

-------------------------------------------------------------------------------------------------------------------（1）内连接

SQL > select a.*,b.* from a inner join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　　 a.Id a. name b.id b.job b.p_id

　　　 1 张三 1 25 1

2 李四 2 36 2

----------------------------------------------------------------------------------

（2）左外连接

SQL > select a.*，b.* from a left join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　 a.Id a. name b.id b.job b.p_id

　　　1 张三 1 25 1

2 李四 2 36 2

　　　3 王五 null null null

----------------------------------------------------------------------------------

（3）右外连接

SQL > select a.*，b.* from a right join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　　a.Id a. name b.id b.job b.p_id

　　　1 张三 1 25 1

2 李四 2 36 2

　　　null null 3 36 4

----------------------------------------------------------------------------------

（4）完全连接

SQL > select a.*,b.* from a full join b on a.id=b.p_id

---------------------------------------------------------------------------------

　　a.Id a. name b.id b.job b.p_id

　　 1 张三 1 25 1

2 李四 2 36 2

　　null null 3 36 4

　　3 王五 null null null

----------------------------------------------------------------------------------