Oracle-表连接类型和表连接方式

表连接类型：

Oracle - 表连接的几种类型：

• 内连接(自然连接)

• 外连接

  • 左外连接 (左边的表不加限制)
  • 右外连接(右边的表不加限制)
  • 全外连接(左右两表都不加限制)
• 自连接（同一张表内的连接）

SQL的标准语法：

select table1.column,table2.columnfrom table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;inner join 表示内连接；left join表示左外连接；right join表示右外连接；full join表示完全外连接；on子句 用于指定连接条件。注意：1.如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；2.如果使用（+）操作符指定外连接，则必须使用where子句指定连接条件。

示意图如下：

---

表连接方式：

Oracle - 表连接的几种方式：

• SORT MERGE JOIN（排序-合并连接）
• NESTED LOOPS（嵌套循环）
• HASH JOIN（哈希连接）
• CARTESIAN PRODUCT（笛卡尔积）

连接说明：

1. Oracle一次只能连接两个表。不管查询中有多少个表，Oracle 在连接中一次仅能操作两张表。
2. 当执行多个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完所有表为止。

举例：

注：这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2；

（1） SORT MERGE JOIN（排序-合并连接）：

假设有查询：select a.name, b.name from table_A a join table_B b on (a.id = b.id)

内部连接过程：

a) 生成 row source 1 需要的数据，按照连接操作关联列（如示例中的a.id）对这些数据进行排序

b) 生成 row source 2 需要的数据，按照与 a) 中对应的连接操作关联列（b.id）对数据进行排序

c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）

延伸：

如果示例中的连接操作关联列 a.id，b.id 之前就已经被排过序了的话，连接速度便可大大提高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。

故可以考虑在 a.id，b.id 上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处理（如仅仅只查询有索引的列等）。

排序-合并连接的表无驱动顺序，谁在前面都可以；

排序-合并连接适用的连接条件有： < <= = > >= ，不适用的连接条件有： <> like

（2） NESTED LOOPS（嵌套循环）：

内部连接过程：

a) 取出 row source 1 的 row 1（第一行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

b) 取出 row source 1 的 row 2（第二行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

c) ……

若 row source 1 （即驱动表）中返回了 N 行数据，则 row source 2 也相应的会被全表遍历 N 次。

因为 row source 1 的每一行都会去匹配 row source 2 的所有行，所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。

延伸：

嵌套循环的表有驱动顺序，注意选择合适的驱动表。

嵌套循环连接有一个其他连接方式没有的好处是：可以先返回已经连接的行，而不必等所有的连接操作处理完才返回数据，这样可以实现快速响应。

应尽可能使用限制条件（Where过滤条件）使驱动表（row source 1）返回的行数尽可能少，同时在匹配表（row source 2）的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接操作关联列上存在索引，连接效率也不会很高。

（3）HASH JOIN（哈希连接） :

哈希连接只适用于等值连接（即连接条件为 = ）

HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；

内部连接过程简述：

a) 取出 row source 1（驱动表，在HASH JOIN中又称为Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）。

b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据。

---

补充：

还想进一步加深学习的同学可以点击下列链接，写的算比较详细的了。

1. Oracle表三种连接方式(sql优化)

2. 看懂Oracle执行计划