ORACLE学习笔记系列（6）Outer Join新旧语法对比分析
????? 从Oracle9i开始，支持ANSI/ISO SQL92标准，对OUTER JOIN有LEFT/RIGHT/FULL OUTER JOIN。而在9i之前，Oralce使用的是SQL86标准，Oracle使用了OUTRT JOIN 操作符‘+’来实现外连接，但是这个‘+’号的语法只支持LEFT/RIGHT OUTER JOIN，不支持FULL OUTER JOIN，要实现FULL OUTER JOIN可以通过UNION ALL实现。
?
--为了能够通过实际数据进行验证，也为后面的测试使用，在这里创建a、b、c 三张表。
SQL> DROP TABLE a;
表已删除。
SQL> DROP TABLE b;
表已删除。
SQL> DROP TABLE c;
表已删除。
SQL> CREATE TABLE a AS
? 2???? SELECT LEVEL ID, 'x' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 4;
表已创建。
SQL> CREATE TABLE b AS
? 2???? SELECT LEVEL ID, 'x' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 2;
表已创建。
SQL> CREATE TABLE c AS
? 2????? SELECT LEVEL ID, 'y' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 2;
表已创建。
SQL> UPDATE A SET NAME = NULL WHERE ID = 3;
已更新1行。
SQL> UPDATE B SET NAME = NULL WHERE ID = 2;
已更新 1 行。
SQL> commit;
提交完成。
SQL> SELECT * FROM A;
??????? ID NAME
---------- -----------------------------------------
???????? 1 x1
???????? 2 x2
???????? 3
???????? 4 x4
已选择4行。
SQL> SELECT * FROM B;
??????? ID NAME
---------- -----------------------------------------
???????? 1 x1
???????? 2
SQL> SELECT * FROM C;
??????? ID NAME
---------- -----------------------------------------
???????? 1 y1
???????? 2 y2
SQL>
?
?
Oracle从9i开始新旧语法并存，新语法的优点如下：
?
1、新语法可读性更强
?
?? 新语法的结构是：
SELECT? *? FROM TABLE/VIEW LEFT/RIGHT/FULL [OUTER] JOIN TABLE/VIEW...
ON JOIN_CONDITION [WHERE]...
?
其中ON是连接条件，WHERE是连接后的过滤条件，有LEFT/RIGHT/FULL标识连接类型，很容易理解。但是老语法如果连接条件复杂，会有一堆+号，有的+号还在表达式里，
比如：WHERE A.NAME=SUBSTR(B.NAME(+),0,4) AND B.ID(+)>20, 很不容易理解。
?
2、新语法支持OR条件连接
?
? 老语法：SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+);??? 会报错“ORA=01719:OR或IN操作数中不允许外部联接运算符（+）”，因为老语法的连接条件不能用OR组合。
?
? 新语法：SELECT * FROM A LEFT JOIN B ON A.ID=B.ID OR A.NAME=B.NAME;执行正常，因为新语法支持。
?
SQL> SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+);
SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+)
????????????????????????????????????????????? *
第 1 行出现错误:
ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID=B.ID OR A.NAME=B.NAME;
????? A_ID A_NAME????????????????????????????????????????? B_ID B_NAME
---------- ----------------------------------------- --------------------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 2 x2??????????????????????? ?????????????????????????2
???????? 3
???????? 4 x4
已选择4行。
SQL>
?
3、新语法支持FULL OUTER JOIN
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME(+) = B.NAME(+);
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME(+) = B.NAME(+)
???????????????????? ???????????????????????????????????????????????????????????????*
第 1 行出现错误:
ORA-01468: 一个谓词只能引用一个外部联接的表
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+);
????? A_ID A_NAME??????????????????????????????????? ??????B_ID B_NAME
---------- ----------------------------------------- -----------------------------------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 3
???????? 2 x2
???????? 4 x4
已选择4行。
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;
????? A_ID A_NAME????????????????????????????????????????? B_ID B_NAME
---------- ----------------------------------------- ------------------------------------
???????? 1 x1?????????????? ??????????????????????????????????1 x1
???????? 2 x2
???????? 3
???????? 4 x4
????????????????????????????????????????????????????????????? 2
已选择5行。
SQL>
?
从上面的结果可以看出老语法是不允许使用全外连接的，而新语法可以查询出9条行数据。
?
4、其他优点待整理
?
?
OUTER JOIN 概念：
?
? OUTER JOIN 和 INNER JOIN 不同，它有基表和从表的概念。
基表也就是参考表，此表的内容会在OUTER JOIN 里全部选中，然后基表根据JOIN的条件到从表中选出从表记录，如果满足条件则按从表实际内容选出，否则没有找到则从表的对应行的所有列值全为NULL。当然OUTER JOIN可用于表也可以用于视图。
?
外连接（OUTER JOIN）分为三种：
??????????? 左外连接(LEFT OUTER JOIN或LEFT JOIN)：左边的表是基表，右边的表是从表；
??????????? 右外连接(RIGHT OUTER JOIN或RIGHT JOIN)：右边的表是基表，左边的表是从表；
??????????? 全外连接(FULL OUTER JOIN或FULL JOIN)：左边的表和右边的表互为基表和从表；
?
全外连接(FULL OUTER JOIN）比较特殊，可以简单理解为先以A为基表，B为从表选出结果，然后以B为基表，A为从表选出结果，最后去除重复在两个步骤中都出现的结果。
从老语法上看，只支持左外连接和右外连接，在WHERE条件里无+号的那边表达式中的列所属表为基表，有+号的表达式中的列所属表为从表。
比如：WHERE A.ID=B.ID(+) 这时A表是基表，B表是从表。
?
下面通过实例来对比分析OUTER JOIN的新旧语法：
依据上面所建的表及数据，分析下面10组语句
?
第1组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+);
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+);
????? A_ID A_NAME????????????????????????????????????????? B_ID B_NAME
---------- ----------------------------------------- ---------- -----------------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 2 x2???????????????????????????????????????????????? 2
???????? 4 x4
???????? 3
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."ID"="B"."ID"(+))
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id ;
????? A_ID A_NAME ?????????????????????????????????????????B_ID B_NAME
---------- ----------------------------------------- ---------- -----------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 2 x2?????????????????????????????????????????????? ??2
???????? 4 x4
???????? 3
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER ??|????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."ID"="B"."ID"(+))
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL>
?
说明：从查询结果和执行计划上看，第1组新旧语句是一样的。
a.连接方式为左外连接：access("A"."ID"="B"."ID"(+))
b.执行计划显示为HASH JOIN OUTER；
c.查询结果为4行；
d.以A表为基表，因此A表一侧内容全部被选中，但是B表只完全匹配（1,'x1'）和（2,null）,对于A表的(3,null)和(4,'x4')在B表中没有找到匹配记录，因此这两行的B表所有列都为NULL。
?
第2组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) AND A.NAME = B.NAME(+);
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id AND A.NAME = B.NAME;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) AND A.NAME = B.NAME(+);
????? A_ID A_NAME????????????? ????????????????????????????B_ID B_NAME
---------- ----------------------------------------- ---------- -------------------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 4 x4
???????? 3
???????? 2 x2
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER? ?|????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."ID"="B"."ID"(+) AND "A"."NAME"="B"."NAME"(+))
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id AND A.NAME = B.NAME;
????? A_ID A_NAME????????????????????????????????????????? B_ID B_NAME
---------- ----------------------------------------- ---------- --------------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 4 x4
???????? 2 x2
???????? 3
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER ??|????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+) AND "A"."ID"="B"."ID"(+))
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL>
?
说明：从查询结果和执行计划上看，第2组新旧语句是一样的。
a.语句1的连接方式为左外连接：access("A"."ID"="B"."ID"(+) AND "A"."NAME"="B"."NAME"(+))
b.执行计划显示为HASH JOIN OUTER；
c.查询结果为4行；
d.以A表为基表，因此A表一侧内容全部被选中，但是B表只完全匹配（1,'x1'）,对于A表的（2,'x2'），(3,null)和(4,'x4')在B表中没有找到匹配记录，因此这3行的B表所有列都为NULL。
?
第3组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+);
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = B.ID OR A.NAME = B.NAME;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+);
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+)
?????????????????????????????????????????????????????????????????????????????????????????????????? *
第 1 行出现错误:
ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = B.ID OR A.NAME = B.NAME;
????? A_ID A_NAME????????????????????????????????????????? B_ID B_NAME
---------- ----------------------------------------- ---------- -----------
???????? 1 x1???????????????????????????????????????????????? 1 x1
???????? 2 x2???????????????????????????????????????????????? 2
???????? 3
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 2608930719
----------------------------------------------------------------------------
| Id? | Operation?????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??? |????? |???? 4 |?? 280 |??? 15?? (0)| 00:00:01 |
|?? 1 | ?NESTED LOOPS OUTER |????? |???? 4 |?? 280 |??? 15?? (0)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL | A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? VIEW????????????? |????? |???? 1 |??? 35 |???? 3?? (0)| 00:00:01 |
|*? 4 |??? TABLE ACCESS FULL| B??? |???? 1 |??? 35 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 4 - filter("A"."ID"="B"."ID" OR "A"."NAME"="B"."NAME")
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL>
?
说明：可看出老语法不支持OR条件连接。
a.语句2的连接方式为嵌套循环：filter("A"."ID"="B"."ID" OR "A"."NAME"="B"."NAME")
b.执行计划显示为NESTED LOOPS OUTER；
c.查询结果为4行；
d.以A表是基表，因此A表一侧内容全部被选中，但是B表只完全匹配（1,'x1'）(2,null),对于A表的 (3,null)和(4,'x4')在B表中没有找到匹配记录，因此这3行的B表所有列都为NULL。
?
第4组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.ID = '2';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.ID = '2';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.ID = '2' ;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = '2' AND A.NAME = B.NAME ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.ID = '2';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 2 x2
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 70 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER ??|????? |???? 1 |??? 70 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |??? 35 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |??? ?3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 2 - filter("A"."ID"=2)
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.ID = '2';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 2 x2
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation? ????????| Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 70 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER?? |????? |???? 1 |??? 70 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |??? 35 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 2 - filter("A"."ID"=2)
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.ID = '2' ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 4 x4
???????? 2 x2
???????? 3
?? ??????1 x1
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER ??|????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |?? 140 |???? 3?? (0)| 00:00:01 |
|?? 3 |?? TABLE ACCESS FULL| B??? |???? 2 |??? 70 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE
????????????? WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))
Note
-----
?? - dynamic sampling used for this statement (level=2)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = '2' AND A.NAME = B.NAME ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 4 x4
???????? 2 x2
???????? 3
???????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE
????????????? WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))
?? 3 - filter("B"."NAME"(+) IS NOT NULL)
SQL>
?
说明：
第1条语句和第2条语句结果是一样的，属于先筛选后连接
（先 filter("A"."ID"=2) 后 access("A"."NAME"="B"."NAME"(+))
第3条语句和第4条语句结果是一样的，
第3条语句但是却只有连接条件没有筛选条件，（由 Table A 驱动 HASH JOIN OUTER ，每一次循环按条件 access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE ??????????????WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END )) 与表B连接。
第4条语句，属于先筛选后连接
（先 filter("B"."NAME"(+) IS NOT NULL) 这个条件在查语句中并没有，是执行计划时添加上的，后 access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE ??????????????WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))。
?
第5组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.NAME = 'x1';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.NAME = 'x1';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |???? 6 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 2 - filter("A"."NAME"='x1')
?? 3 - filter("B"."NAME"(+)='x1')
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |???? 6 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 2 - filter("A"."NAME"='x1')
?? 3 - filter("B"."NAME"(+)='x1')
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
???????? 3
???????? 4 x4
???????? 2 x2
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER? ?|????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+) AND "A"."NAME"=CASE? WHEN
????????????? ("B"."NAME"(+) IS NOT NULL) THEN 'x1' ELSE 'x1' END )
?? 3 - filter("B"."NAME"(+) IS NOT NULL)
SQL>
?
说明：第5组语句与第4组语句是类似的。
?
第6组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 4090908061
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN???????? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| A??? |???? 3 |??? 18 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("B"."NAME" IS NOT NULL AND "B"."ID"=2)
?? 3 - filter("A"."NAME" IS NOT NULL)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 4090908061
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN ????????|????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| A??? |???? 3 |??? 18 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("B"."NAME" IS NOT NULL AND "B"."ID"=2)
?? 3 - filter("A"."NAME" IS NOT NULL)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 3
???????? 1 x1
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER ??|????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 3 - filter("B"."NAME"(+) IS NOT NULL AND "B"."ID"(+)=2)
SQL>
?
说明：
第1条语句和第2条语句查询结果是一样的，属于先筛选后连接，但连接方式已经就成了HASH JOIN 并且两个表连接的列都不能为NULL，因此没有查询出匹配的结果数据。
第3条语句连接方式依然是 HASH JOIN OUTER。
?
第7组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME = 'x1';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME = 'x1';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME = 'x1' ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME = 'x1';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN???????? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |???? 6 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("A"."NAME"='x1')
?? 3 - filter("B"."NAME"='x1')
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME = 'x1';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN?? ??????|????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| A??? |???? 1 |???? 6 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("A"."NAME"='x1')
?? 3 - filter("B"."NAME"='x1')
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME = 'x1' ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
???????? 3
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER? ?|????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 3 - filter("B"."NAME"(+)='x1')
SQL>
?
说明：第7组语句与第6组语句是类似的。
通过第6组语句与第7组语句，可以看出当从表B中存在限制特定条件的列时，两个表的连接方式就不再是外连接。
?
?
第8组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2' AND B.NAME IS NULL;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2' AND B.NAME IS NULL;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' AND B.NAME IS NULL;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2' AND B.N
AME IS NULL;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 4090908061
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN?? ??????|????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| A??? |???? 3 |??? 18 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("B"."NAME" IS NULL AND "B"."ID"=2)
?? 3 - filter("A"."NAME" IS NOT NULL)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2' AN
D B.NAME IS NULL;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 4090908061
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN ????????|????? |???? 1 |??? 11 |???? 7? (15)| 00:00:01 |
|*? 2 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| A??? |???? 3 |??? 18 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME")
?? 2 - filter("B"."NAME" IS NULL AND "B"."ID"=2)
?? 3 - filter("A"."NAME" IS NOT NULL)
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' AND
B.NAME IS NULL;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 3
???????? 1 x1
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 | ?HASH JOIN OUTER?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 3 - filter("B"."NAME"(+) IS NULL AND "B"."ID"(+)=2)
SQL>
?
说明：
第1条语句和第2条语句结果是一样的，属于先筛选后连接，连接方式就为HASH JOIN，因为筛选条件中从表B的连接列条件为NULL filter("B"."NAME" IS NULL AND "B"."ID"=2)
所以基表A的连接列不能为NULL ?filter("A"."NAME" IS NOT NULL)。
第3条语句属于先筛选后连接，连接方式就为HASH JOIN OUTER，因此能查询出基表A的4条信息。
?
第9组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME IS NULL;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME IS NULL;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME IS NULL;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME IS NULL;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 3
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 2020188187
----------------------------------------------------------------------------
| Id? | Operation?????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??? |????? |???? 2 |??? 22 |???? 7? (15)| 00:00:01 |
|*? 1 |? FILTER ????????????|????? |?????? |?????? |??????????? |????????? |
|*? 2 |? ?HASH JOIN OUTER? ?|????? |???? 2 |??? 22 |???? 7? (15)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|?? 4 |??? TABLE ACCESS FULL| B??? |???? 2 |??? 10 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("B"."NAME" IS NULL)
?? 2 - access("A"."NAME"="B"."NAME"(+))
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME IS NUL
L;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 3
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 2020188187
----------------------------------------------------------------------------
| Id? | Operation?????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??? |????? |???? 2 |??? 22 |???? 7? (15)| 00:00:01 |
|*? 1 | ?FILTER? ???????????|????? |?????? |?????? |??????????? |????????? |
|*? 2 |?? HASH JOIN OUTER? ?|????? |???? 2 |??? 22 |???? 7? (15)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|?? 4 |??? TABLE ACCESS FULL| B??? |???? 2 |??? 10 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("B"."NAME" IS NULL)
?? 2 - access("A"."NAME"="B"."NAME"(+))
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME IS NULL;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 3
???????? 1 x1
???????? 2 x2
???????? 4 x4
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|*? 1 |? HASH JOIN OUTER? ?|????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 2 |?? TABLE ACCESS FULL| A??? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
|*? 3 |?? TABLE ACCESS FULL| B??? |???? 1 |???? 5 |???? 3?? (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("A"."NAME"="B"."NAME"(+))
?? 3 - filter("B"."NAME"(+) IS NULL)
SQL>
?
说明：
第1条语句和第2条语句结果是一样的，属于先连接后筛选，连接方式就为HASH JOIN OUTER，（先access("A"."NAME"="B"."NAME"(+)) 后 filter("B"."NAME" IS NULL)
第3条语句，属于先筛选后连接，连接方式就为HASH JOIN OUTER，
（先filter("B"."NAME"(+) IS NULL) 后 access("A"."NAME"="B"."NAME"(+))
?
?
第10组语句：
?
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';
SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;
?
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
???????? 2 x2
???????? 3
???????? 4 x4
????????????????????????????????????? ???2
执行计划
----------------------------------------------------------
Plan hash value: 4118491158
----------------------------------------------------------------------------------
| Id? | Operation???????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 1 |? VIEW???????????????? | VW_FOJ_0 |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 2 |?? HASH JOIN FULL OUTER|????????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL? | B??????? |???? 2 |??? 10 |???? 3?? (0)| 00:00:01 |
|?? 4 |??? TABLE ACCESS FULL? | A??????? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? ?2 - access("A"."NAME"="B"."NAME")
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1??????????????????????????? 1 x1
执行计划
----------------------------------------------------------
Plan hash value: 4118491158
----------------------------------------------------------------------------------
| Id? | Operation???????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 1 |? VIEW???????????????? | VW_FOJ_0 |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 2 |?? HASH JOIN FULL OUTER|????????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL? | B??????? |???? 2 |??? 10 |???? 3?? (0)| 00:00:01 |
|?? 4 |??? TABLE ACCESS FULL? | A??????? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("A"."NAME"='x1')
?? 2 - access("A"."NAME"="B"."NAME")
SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;
????? A_ID A_NAME???????????????????? B_ID B_NAME
---------- -------------------- ---------- --------------------
???????? 1 x1???????????????????? ???????1 x1
???????? 2 x2
???????? 3
???????? 4 x4
???????????????????????????????????????? 2
执行计划
----------------------------------------------------------
Plan hash value: 4118491158
----------------------------------------------------------------------------------
| Id? | Operation???????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????? |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|?? 1 |? VIEW???????????????? | VW_FOJ_0 |???? 4 |?? 280 |???? 7? (15)| 00:00:01 |
|*? 2 |? ?HASH JOIN FULL OUTER|????????? |???? 4 |??? 44 |???? 7? (15)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL? | B??????? |???? 2 |??? 10 |???? 3?? (0)| 00:00:01 |
|?? 4 |??? TABLE ACCESS FULL? | A??????? |???? 4 |??? 24 |???? 3?? (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - access("A"."NAME"="B"."NAME")
?????? filter("A"."NAME"='x1')
SQL>
?
说明：
第10组语句都是新语法语句，连接方式都是全外连接 HASH JOIN FULL OUTER。
?
小结：
右外连接(RIGHT OUTER JOIN或RIGHT JOIN)与左外连接(LEFT OUTER JOIN或LEFT JOIN) 是类似的，在此不再具体分析。
对于OUTER JOIN 新旧语法大部分都是相同的，但是新语法对条件的限制更加灵活，可读性也更强。不管是新语法还是旧语法，连接的列或者限制条件的列不一样，都有可能导致整个查询的连接方式和查询结果的改变，所以在实际应用中都需要认真谨慎核对。