【东软实训】SQL多表链接

如果一个查询同时涉及两个以上的表，则称之为链接查询，链接查询是关系数据库中最主要的查询，主要包括等值链接查询、非等值链接查询、自身链接查询、外链接查询和复合条件链接查询。 
这篇博文我们来对多表链接进行学习。

Outline

• 链接的基本概念
• Oracle自有的链接方法
  • 等值链接
  • 非等值链接
  • 自身链接
• SQL标准语法连接方法
  
  • 交叉连接（笛卡尔连接）
  • 自然连接
  • Using与On语句
  • 左外连接、右外连接、全连接

Notes

## 链接的基本概念

• 链接是在多个表之间通过一定的链接条件，使表之间发生关联，进而能从多个表之间获取数据。
• 语法为

  SELECT    table1.column, table2.column
  FROM    table1, table2
  WHERE    table1.column1 = table2.column2;

• 在 WHERE子句中书写链接条件。N个表相连时，至少需要N－1个链接条件。
• 如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。

【链接的分类】

　　一个用户查询请求涉及到多个表的时候，链接两个表的条件为=时，就是等值链接查询；其他的运算符链接的就是非等值查询。 注意：链接条件中的各链接字段类型必须是可比的，但不必是相同的，整型和实型是可比的，但是字符型和整型就不可比。

　　内连接，也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。

　　外连接不仅包含符合连接条件的行，还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。

 

## Oracle自有的连接方法（select from语句）

【等值连接】

 链接两个表的条件为=时，就是等值链接查询

SQL> SELECT     emp.empno,   emp.ename, emp.deptno, dept.deptno, dept.loc
  2  FROM       emp, dep
  3  WHERE      emp.deptno=dept.deptno;

 

注意：我们还可以使用And运算符添加其他查询条件，如：

注意：我们还可以用列别名提高性能和限制歧义列名

• 在用到多个表时可以使用表名作前缀来限定列；
• 通过使用表前缀可以提高性能；
• 通过使用列的别名可以区分来自不同表但是名字相同的列；

如：

 

【非等值连接】

 

SQL>    SELECT  e.ename, e.sal, s.grade
   2    FROM    emp e,   salgrade s
   3    WHERE   e.sal
   4    BETWEEN     s.losal AND s.hisal;

 

 

【自身连接】

　　 一个表与自己进行连接，这种连接称为表的自身连接查询。 
　　具体实现的时候，我们可以把自己的表起两个别名，一个是first, 一个是second.在设计的时候可以把这两个表想成是完全两个一样的表，但是各自的字段我们都可以只有调用访问。 

SQL> SELECT worker.ename||' leader is '||manager.ename
  2  FROM    emp worker, emp manager
  3  WHERE   worker.mgr = manager.empno;

 

 ## SQL标准语法连接方法

 【交叉连接】

• 交叉连接会产生连个表的交叉乘积，和两个表之间的笛卡尔积是一样的；
• 交叉连接使用 CROSS JOIN 子句完成。
• 笛卡尔积： 第一个表中的所有行和第二个表中的所有行都发生连接。
• 笛卡尔积在下列情况产生：
  • 连接条件被省略
  • 连接条件是无效的
• 为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件。

<--笛卡尔积写法-->
SQL> SELECT     emp.empno,   emp.ename, emp.deptno,dept.deptno, dept.loc
         FROM       emp, dept;


<--交叉连接写法-->
SELECT     emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc 
FROM       emp 
CROSS  JOIN dept; 

 

【自然连接】

• 自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；
• 如果两个表之间相同名称的列的数据类型不同，则会产生错误；
• 使用NATURAL JOIN子句来完成。如：

  SELECT  empno,ename,sal,deptno,loc 
  FROM    emp 
  NATURAL JOIN     dept; 

【Using和On语句】

自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件，如：

SELECT     e.ename,e.ename,e.sal,deptno,d.loc 
FROM       emp e JOIN dept d USING (deptno) 
WHERE      deptno = 20 ;

 

• 使用USING子句创建连接时，应注意以下几点：
  • 如果如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。
  • 有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。
  • USING子句中的用到的列不能使用表名和别名作为前缀。
  • NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用
• On语句：
• 自然连接条件基本上是具有相同列名的表之间的等值连接；
• 如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；
• 用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；
• ON子句可以提高代码的可读性。

SELECT     e.empno, e.ename, d.loc,m.ename 
FROM           emp e 
JOIN           dept d 
ON             e.deptno = d.deptno 
JOIN           emp m 
ON             e.mgr = m.empno; 

 

【左外连接、右外连接、全连接】

• 外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
• 三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
  • 左外连接（⟕）还返回左表中不符合连接条件单符合查询条件的数据行。
  • 右外连接（⋉）还返回右表中不符合连接条件单符合查询条件的数据行。
  • 全外连接（）还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
• 说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。在三种类型的外连接中，OUTER 关键字是可省略的。

<--左外连接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
LEFT OUTER JOIN dept d 
ON         (e.deptno = d.deptno); 

<--右外连接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
RIGHT OUTER JOIN dept d 
ON         (e.deptno = d.deptno); 

<--全连接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
FULL OUTER JOIN dept d 
ON         (e.deptno = d.deptno);