多表连接

一、什么是连接

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获得数据。

语法：

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

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

二、连接的类型

• 按照连接条件分：

等值连接
非等值连接

• 按其他连接方法分：

外部连接
内部连接

三、多表连接写法

多表连接包含多种写法，在这里主要介绍：

• Oracle写法：oracle公司提供的写法，绝大多数符合SQL标准，其他关系型数据也适用。
• ANNSI 99写法：ANSI标准提供的写法，所有关系型数据必须支持。

四、笛卡尔积

• 笛卡尔积是：
  第一个表中的所有行和第二个表中的所有行都发生连接
• 笛卡尔积在下列情况卡产生
  1.连接条件被省略
  2.连接条件是无效的
• 为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件。
• 笛卡尔积结果
  简单描述：
  表1中有14行数据，表2中有4行数据，笛卡尔积结果为56行数据

五、使用AND 运算符增加其他的查询条件

• 在WHERE子句的连接条件后添加AND条件

六、限制歧义列名

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

七、使用表的别名

• 通过使用表的别名来简化查询语句

2019-02-14_163746.png

八、多余两个表的连接

• 多个表连接和两个表的连接一样，在构造SQL语句时，需要多考虑一个表之间的关联条件。

九、多表连接的写法

• 分析要查询的列都来自哪个表，构成FROM子句
• 分析这些表之间的关联关系，如果表之间没有直接的关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表。
• 接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系。
• 分析是否还有其他限制条件，补充到WHERE子句的表关联关系之后，作为限定条件。
• 根据用户想要显示的信息，补充SELECT子句。
• 分析是否有排序要求，如果排序要求中还涉及到其他表，则也要进行第2步补充排序字段所需要的表，并且添加表之间的关联关系。

十、外部连接

• 在多表连接时，可以使用外部连接来查看哪些行，按照连接条件没有被匹配上。
• 外部连接的符号是（+），语法包括：

左外连接：

SELECT  table1.column, table2.column 
FROM   table1, table2 
WHERE  table1.column(+) = table2.column;

右外连接：

SELECT  table1.column, table2.column 
FROM   table1, table2 
WHERE  table1.column = table2.column(+);

• 外部连接就好像是为符号（+）所在边的表增加一个“万能”的行，这个行全部由NULL组成。他可以和另一边的表中所有不满足连接条件的进行连接。由于这个“万能”行的各列全是空值，因此在连接结果中，来自“万能”行属性值全部为空值。

十一、自身连接

• 自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。
• 例如：

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

十二、SQL：1999语法的连接

• ANSI SQL：1999标准的连接语法
  oracle除了上述自己的连接语法外，同时支持美国国家标准协会（ANSI）的SQL：1999标准的连接语法。

SELECT  table1.column, table2.column 
FROM  table1 
[CROSS JOIN table2] | 
[NATURAL JOIN table2] | 
[JOIN table2 USING (column_name)] | 
[JOIN table2  
ON(table1.column_name = table2.column_name)] | 
[LEFT|RIGHT|FULL OUTER JOIN table2  
ON (table1.column_name = table2.column_name)];

• 交叉连接
  交叉连接会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的。
  使用CROSS JOIN 子句完成。

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子句
  自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而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, e.deptno, d.deptno, d.loc  
FROM     emp e  
JOIN   dept d  
ON    (e.deptno = d.deptno);

• 外连接

左外连接
左外连接以FROM子句中左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来

SELECT e.ename,e.deptno,d.loc  
FROM emp e  
LEFT OUTER JOIN dept d  
ON (e.deptno = d.deptno);

右外连接
右外连接以FROM子句中右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来

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);