表连接之连接的类型

    顾明思义，表连接就是指多个表之间用连接条件连接在一起，使用连接的目标sql的目的就是从多个表获取存储在这些表中的不同维度的数据。体现在sql语句上，含表连接的目标sql的from部分会出现多个表，而这些sql的where条件部分则会定义具体的表连接条件。

    当优化器解析含表连接的目标sql时，它除了会根据目标sql的sql文本的写法来决定表连接的类型之外，还必须决定如下三件事情才能得到最终的执行计划。

    （1）表连接顺序

    不管目标sql中有多少个表做连接，oracle在实际执行该sql时都是只能先两两表连接，再依次执行这样的两两表连接过程，直到目标sql中所有表都已连接完毕。所以从严格意义上来说，这里的表连接顺序包含两层含义：一层含义当两个表做表连接时，优化器需要决定两个表中谁是驱动表，谁是被驱动表；另外一层含义是当多表（超过两个以上的表）做表连接时，优化器需要决定这些中谁和谁先做表连接，然后决定这个表连接结果所在的结果集合剩余的表的哪一个再做表连接，这个两两做表连接的过程会一直持续下去去，直到目标sql中所有的表都已经连接完为止。

    （2）表连接的方法

    在oracle数据库中，两表之间的表连接方法有合并联结，嵌套循环联结，哈希连接和笛卡儿联结这四种，所以优化器在解析含表连接的目标sql时，都需要从上述四种方法中选择一种，作为每一对表两两做表连接时所以需要采用的方法。

    （3）访问单表的方法

    对于优化器而言，仅决定表连接顺序和连接方法是不够的，这还不足以得到目标sql的最终执行计划，因为优化器对目标sql中个各个表两两做表连接时，还必须决定如何去获取存储在这些表里的不同维度的数据，即优化器还要决定访问单表的方法。比如访问某个单表时，是采用全表扫描还是走索引，如果是索引，应该采用什么样的索引访问方法等。

    表连接的类型

    通常情况下，我们可以认为oralce数据库中的表连接分为内连接和外连接两种类型，表连接的类型会直接决定表连接的结果，而目标sql的sql文本的写法又直接决定了表连接的类型。

    （1）内连接

    内连接是指表连接的连接结果只包含那些完全满足条件的记录吗。对于包含表连接的目标sql而言，只要其where条件中没有写那些标准sql中定义或oracle中自定义的表示外连接的关键字（比如标准sql中的left outer join，right outer join,full outer join,或者oracle中自定义用来表示外连接的关键（+），则该sql的连接类型就是内连接

SQL> create table t1(col1 number,col2 varchar2(1));

Table created.

SQL> SQL> create table t2(col2 varchar2(1),col3 varchar2(2));

Table created.

SQL> insert into t1 values(1,'A');

1 row created.

SQL> insert into t1 values(2,'B');

1 row created.

SQL> insert into t1 values(3,'C');

1 row created.

SQL> insert into t2 values('A','A2');

1 row created.

SQL> insert into t2 values('B','B2');

1 row created.

SQL> insert into t2 values('D','D2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

      COL1 C

---------- -

1 A

2 B

3 C

SQL> select * from t2;

C CO

- --

A A2

B B2

D D2

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

      COL1 C CO

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

1 A A2

2 B B2

    从以上执行结果中我们可以看出，内连接的连接结果只包含了那些玩去满足条件的记录。

    标准sql的写法如下：   

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 using(col2);

select t1.col1,t1.col2,t2.col3 from t1 join t2 using(col2)

               *

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier

    这里需要注意的是，对于使用join using的标准sql而言，如果连接连接列同时又出现在查询列中，则该连接列前不能带上表名或表名的别名，否则oralce会抛出ORA-25154，以下是正确写法：

SQL> select t1.col1,col2,t2.col3 from t1 join t2 using(col2);

      COL1 C CO

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

1 A A2

2 B B2

    使用标准sql来表示表连接，那么有一种特殊的jion using，我们称之为NATURAL JOIN，NATURAL JOIN是一种特殊的JOIN USING，其含义是使用NATURAL JOIN的表连接的连接列是表连接的两个表所有的同名列。

SQL> select t1.col1,col2,t2.col3 from t1 natural join t2;

      COL1 C CO

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

1 A A2

2 B B2

    使用NATURAL JOIN的好处是无须在JOIN USING中写连接集合，但其坏处是增加了表连接的执行结果出错的风险，因为两个表之间的同名列不一定在含义上完全相同（也许它们只是恰好同名而已，其含义是完全不同的，所以不应该将它们作为连接列）而且即使含义相同，也不一定就需要他们做连接

    （2）外连接

    外连接是对内连接的一种扩展，它是指表连接的连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表中所有不满足该条件的连接的记录。

    标准sql的外连接分为左连接，右连接和全连接这三种，它们在标准sql中所对应的关键字分别是left outer join,right outer join和full outer join，都可以和join on或者join using连用。

    左连接语法：

    目标表1 left outer join 目标表2 on（连接条件）或

    目标表1 left outer join 目标表2 using（连接集合）

    “目标表1 left outer join 目标表2 on（连接条件）”的含义为目标表1和目标表2按括号中连接条件来做表连接，位于关键left outer join左边的目标表1会作为该表连接的驱动表（关键字“left outer”即表明位置处于left就是outer table，outer table指驱动表）。此时的连接除了包含目标表1和目标表2中所有满足该条件的记录外，还会包含驱动表（目标表1）中索引不满足该连接条件的记录，同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表（即目标表2）中查询列均以NULL值来填充。

    右连接语法：

    目标表1 right outer join 目标表2 on（连接条件）或

    目标表1 right outer join 目标表2 using（连接集合）

    全连接语法：

    目标表1 full outer join 目标表2 on（连接条件）或

    目标表1 full outer join 目标表2 using（连接集合）

    可以把全连接理解成先做左连接，再右连接，最后对左右连接的连接结果做一个union操作。

    左连接实例：

SQL> select t1.col1,t1.col2,t2.col3 from t1 left outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

3 C

    oracle自定义写法：

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2(+);

      COL1 C CO

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

1 A A2

2 B B2

3 C

关键字（+）出现在表T2的连接列col2后面，这就表示T2会以NULL值来填充那些不满足连接条件的t1.col2=t2.col2 并位于T2中的查询列（col3）。

    右连接实例：

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

    D2

    oracle自定义写法：

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2(+)=t2.col2;

      COL1 C CO

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

1 A A2

2 B B2

    D2

    全连接实例：  

SQL> select t1.col1,t1.col2,t2.col3 from t1 full outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

    D2

3 C

    上述实例sql中除了带了连接条件外，并没有带其他的额外连接条件，如果目标sql中除了表连接条件之外还带了其他额外的限制条件，则目标sql中表连接的类型和该额外条件在目标sql的sql文本中出现位置都可能会对最终执行计划产生影响。    

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2 and t1.col1=1);

      COL1 C CO

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

1 A A2

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2) where t1.col1=1;

      COL1 C CO

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

1 A A2

    上述结果说明对于内连接而言，除了表连接条件之外的额外限制条件在目标sql的sql文本中所处的位置并不会影响该sql的实际执行结果。

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2 and t1.col1=1);

      COL1 C CO

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

1 A A2

    D2

    B2

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2) where t1.col1=1;

      COL1 C CO

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

1 A A2

    对于例1，它的限制条件在sql文本中位于right outer join所对应的括号内，这表示该限制条件会在表T1和T2做右连接之前就被应用在表T1上，参与右连接的T1的数据是那些满足条件t1.col1=1的记录， 而例2的限制条件位于right outer join括号外，这表示该限制条件在T1和T2做完右连接之后，才会被应用在表T1和T2的连接结果集上，参与右连接的是表T1中所有数据。