数据库的连接操作
在我们使用多个表示是常常要用到表与表之间的连接，所以我们要熟练的掌握Join的特性。两个表连接方式主要分为内连接（inner join）和外连接(outer join)。外连接又分为三种，left outer join、right outer join和full outer join。在Select语句中inner和outer关键字可以省略。一些数据库还提供了其他的联系方式，比如Oracle还有两种连接方式：cross join，结果就是两个表的笛卡儿集；natural inner join，也是一种inner join系统自动匹配两个表中字段名相同的列。
我们用下面的两个表来作为示例好好的看看各种连接方式的特性。
SQL> select * from t_left; SQL> select * from t_right;
ID SVALUE ID SVALUE
---------- ---------------- ---------- ----------------
1 s1 1 s2
2 s2 2 s3
3 s2 3 (null)
4 (null)
先看看inner join：
SQL> select a.id,a.svalue,b.id,b.svalue from t_left a join t_right b on(a.sValue=b.sValue);
ID SVALUE ID SVALUE
---------- ---------------- ---------- ----------------
2 s2 1 s2
3 s2 1 s2
我们看到上面没有 4 (null) 3(null) 记录，再一次说明 数据库中的NULL值(一) 对NULL操作中提到的null和任何值包括null做比较操作（含等于操作）返回结果都是Unknow。
Left join语句：
SQL> select a.id,a.svalue,b.id,b.svalue from t_left a left join t_right b on (a.sValue=b.sValue);
ID SVALUE ID SVALUE
---------- ---------------- ---------- ----------------
3 s2 1 s2
2 s2 1 s2
4 (null) (null) (null)
1 s1 (null) (null)
SQL> select a.id,svalue,b.id from t_left a left join t_right b using(sValue);
ID SVALUE ID
---------- ---------------- ----------
3 s2 1
2 s2 1
4 (null) (null)
1 s1 (null)
我们看到在left join语句中右边表中没有匹配都补以null。在jion...using语句中对关联字段的处理等同于下面的join语句：
SQL> select a.id,nvl(a.svalue,b.svalue) as svalue,b.id from t_left a left join t_right b on(a.sValue=b.sValue);
ID SVALUE ID
---------- ---------------- ----------
3 s2 1
2 s2 1
4 (null) (null)
1 s1 (null)
right join语句和Left join语句在功效上可以互换，只是要同时把两个表的位置调换一下。
full outer jion：
SQL> select a.id,a.svalue,b.id,b.svalue from t_left a full join t_right b on(a.sValue=b.sValue);
ID SVALUE ID SVALUE
---------- ---------------- ---------- ----------------
3 s2 1 s2
2 s2 1 s2
4 (null) (null) (null)
1 s1 (null) (null)
(null) (null) (null) 3
(null) (null) 2 s3
已选择6行。
我们看到Outer join语句类似于left与right取并集再出去他们重复的部分，但是在Oracle中没发通过 Union（all）和minus 来实现，在DB2中可以，因为DB2种的全查询中有 EXCEPT ALL 语句，可以通过下面的语句来实现：
select a.id,a.svalue,b.id,b.svalue from t_left a left join t_right b on(a.sValue=b.sValue)
union all
select a.id,a.svalue,b.id,b.svalue from t_left a right join t_right b on(a.sValue=b.sValue)
except all
select a.id,a.svalue,b.id,b.svalue from t_left a join t_right b on(a.sValue=b.sValue)
我们来看看数据库是怎么连接两个表的，有哪些方式。我们还是通过下面的示例看看（这里讨论的内容仅适用于Oracle，目的并不是要搞清楚数据库到底是怎么做的，主要是为了在脑子中留下一些规则性的印象，以便可以写出高效的SQL语句）：
先看看两个表的笛卡儿集：
SQL> explain plan for select a.*,b.* from t_left a cross join t_right b;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1578314324
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 552 | 8 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 12 | 552 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T_RIGHT | 3 | 69 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 4 | 92 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_LEFT | 4 | 92 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
已选择15行。
从上面可以看出数据库采用行数较小的表主表开始做Merge Join，对两个表分别作全表扫描。
SQL> explain plan for select a.*,b.* from t_left a join t_right b on (a.id=b.id);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2858533051
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 138 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL |T_RIGHT | 3 | 69 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_LEFT | 1 | 23 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007465 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
已选择20行。
可以看出采用的连接方式变化了，变成Nested Loops。因为两边都是主键所以还是用行数小的表作为主表，如果只有一边是主键则会用非主键的那个表作为主表，以使得后面可以通过主键的索引直接读取。
SQL> explain plan for select a.*,b.* from t_left a left join t_right b on (a.id=b.id);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2128124934
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 184 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 184 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T_LEFT | 4 | 92 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_RIGHT | 1 | 23 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007470 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID"(+))
Note
-----
- dynamic sampling used for this statement
已选择20行。
将jion变为Left join后有两个显著的变化，一连接方式变为Nested Loops Outer，还有一个就是全扫描的主表不再是一行数少的表开始了，而是改用左边的表作为主表，这个不难理解。
SQL> explain plan for select a.*,b.* from t_left a join t_right b on (a.svalue=b.svalue);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3561401177
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 138 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_RIGHT | 3 | 69 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_LEFT | 4 | 92 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."SVALUE"="B"."SVALUE")
Note
-----
- dynamic sampling used for this statement
已选择19行。
如果连接的都没有索引，数据库就用Hash Join 来优化。
