从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 新旧语法大部分都是相同的，但是新语法对条件的限制更加灵活，可读性也更强。不管是新语法还是旧语法，连接的列或者限制条件的列不一样，都有可能导致整个查询的连接方式和查询结果的改变，所以在实际应用中都需要认真谨慎核对。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
