Oracle的连接详解（左连接、右连接、全连接...）

https://www.cnblogs.com/guogl/articles/5929852.html

 

1  说明：

提到数据库必须要知道联接操作，这部分的内容在数据库查询操作中占到举足轻重的地位，今天我就来说说这些联结操作。

联接操作可以在WHERE中指定，也可以在FROM子句中指定，在FROM子句中指定联接条件时，SQL2将联接操作符分为联接类型和联接条件两部分。

联接类型决定了如何处理联接条件中不匹配的元组。

2  连接操作：

联接条件决定了两个关系中那些元素该匹配。

联接类型	说明
INNER JOIN	内联接，结果为两个联接表中的匹配行的联接
LEFT OUTER JOIN	左联接：结果包括左表（出现在JOIN子句最左边）中的所有行，不包括右表中的不匹配行。
RIGHT OUTER JOIN	右联接：结果包括右表（出现在JOIN子句最右边）中的所有行，不包括有左表中的不匹配的行。
FULL OUTER JOIN	完全联接：结果包括所有联接中的所有行，不论他们是否匹配。
CROSS JOIN	交叉联接：结果包括两个联接表中的所有可能的行组合。交叉连接返回的是两个表的笛卡儿积。（Oracle不支持）
NATURAL JOIN	自然连接时在两张表中寻找那些数据类型 和列名都相等的字段，然后自动地将他们连接起来。

通俗的说：在左连接和右连接时都会以一张表作为基表，该表的内容会全部的显示，然后加上两张表匹配的内容。

        

此图引用至：http://blog.csdn.net/caolaosanahnu/article/details/8080350

 

对于外连接，在Oracle中可以使用(+)来表示，使用的方式：

1、(+)操作符只可以出现在WHERE子句中，并且不可以和outer join语法同时使用。

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

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

4、(+)操作符不能与or和in操作符一起使用。

5、(+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

 

自然连接的注意事项：

（1）   如果做自然连接的两个表中有多个字段都满足有相同名称和类型，那么他们都会被作为连接的条件。

（2）   如果自然两个表中仅字段名称相同，但是数据类型不同时，此时会返回一个错误。

 

这里我再说明几种连接方式，也当作一种补充知识了：

1、 Self joins自连接：

使用自连接，可以将自己表的一个镜像作为另一张表来对待，进行连接后查询出数据。

2、 Cartesian Products 笛卡尔积

当两个表没有连接操作时，对这两个表进行查询得到的数据是什么样的呢？是这两个表的笛卡儿积。

 

3 多表连接操作

在进行多表联合查找时，会发现多表会进行连接操作的，而这些连接操作包括三种方式：

Hash Join（散列连接）、Nested Loops、Sort Merge join，对于这些连接方式的了解可以方便我们对于连接操作的进一步了解，下面进行一一介绍。

3.1  HASH JOIN（散列连接）

Hash Join散列连接主要是CBO（CBO: Cost-Based Optimization 基于代价的优化器， Oracle优化器之一，后续会进行介绍）做数据量比较大时进行连接操作比较常用的一种方式，优化器会选用两个表中较小那个表（小一点表或者数据源），利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描大的那个表，同样对JOIN KEY到hash表进行匹配，找出可以匹配的值，此时注意：如果HASH表比较大，无法一次存储到内存时则会分成不多个partition(段)，写入磁盘的temporary segment，此时性能会多出一次写的代价，有可能会降低效率。

该内容主要适用于较小的表（可以放入内存），此时相关效能就是访问了两个的性能的成本总和。

可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。

3.2  SORT MERGE JOIN：排序合并连接

Merge Join排序合并连接，是先将两个关联的表按照关联键（JOIN KEY）进行排序，然后从各自排序表中抽取数据，到另一个排序表中进行匹配。

相对来说，merge join需要完成排序操作，所以消耗的性能比较多，但是当源如果已经进行了排序，其将会取得较好的性能。适用于：不等价关联(>,<,>=,<=,<>)、HASH_JOIN_ENABLED=false等情况。

可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.

3.3  NESTED LOOP：嵌套循环连接

Nested Loops嵌套循环连接，工作方式是循环从一张表中读取数据（驱动表 outer table）,然后访问另一张表（被查找表 inner table， 希望有索引）。驱动表中每一行与inner表中相应记录进行链接（JOIN）。内表被外表驱动，外表中的每一行都会与内表进行匹配操作，所以查询结果集最好不要太大。将数据量小的表作为查询的驱动表（外表），可以利用ordered 来提示CBO默认的驱动表。

对于驱动表较小的情况时，嵌套循环连接相对比较好，同时inner表需要有效的访问索引（index）。

使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

4  举例:

Oracle的两张表：部门信息表：DEPTINFO，用户表：USERINFO

    

 

4.1  INNER JOIN连接操作：

INNER JOIN连接作为一种等值连接，其操作相当于where中的等至连接。

 

 

4.2  LEFT JOIN连接操作：

USERINFO 左连接DEPTINFO，结果将以USERINFO为基表，其内容将全部显示。

 

Oracle包含了3种连接方式：分别是：

4.3  RIGHT JOIN链接操作

USERINFO右连接DEPTINFO，结果将以DEPTINFO为基表，将其内容全部显示出来。

 

4.4  FULL OUTER JOIN连接操作：

USERINFO 全连接DEPTINFO，结果将USERINFO以及DEPTINFO的内容都显示出来。

  

4.5  NATURAL JION自然连接

 

4.6  利用(+)来实现连接操作:

1、无(+)连接：

 

 

2、左(+)连接：（相当于Right Join）

 

 

3、右(+)连接：（相当于Left Join）

 

注意：不可以左右都加(+)。

4.7  Self Join 自连接以及Cartesian Products笛卡儿积

          

                   自连接                                                        笛卡儿积