从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 新旧语法大部分都是相同的，但是新语法对条件的限制更加灵活，可读性也更强。不管是新语法还是旧语法，连接的列或者限制条件的列不一样，都有可能导致整个查询的连接方式和查询结果的改变，所以在实际应用中都需要认真谨慎核对。
相关推荐
从提供的标题、描述以及部分文本内容来看，这份“Oracle学习笔记”主要围绕Oracle数据库中的查询语句及其各种类型进行深入探讨。尽管描述部分重复了标题的文字，但主要内容段落包含了丰富的信息，下面将对这些知识点...
### Oracle学习笔记知识点详解 #### 一、SQL概述与Oracle简介 - **SQL**（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的标准语言。它被用来执行各种数据库操作，如查询数据、更新数据...
### Oracle SQL 笔记知识点详解 #### 一、SQLPlus 命令及环境变量 Oracle_sid - **SQLPlus 命令位置**：在 Oracle 安装目录下的 `bin` 文件夹中，可以通过 SQLPlus 来执行 SQL 命令。 - **Oracle_sid 环境变量**：...
这份"SQL学习笔记"涵盖了SQL的基础概念、语法以及高级特性，是学习数据库管理和数据分析的宝贵资料。 1. **SQL基础** - 数据库概念：了解什么是数据库，它的作用以及数据库管理系统（DBMS）如何工作。 - SQL简介...
这篇笔记将带你逐步探索Oracle的学习之旅，从安装、表管理到复杂的SQL查询，深入理解Oracle的精髓。 ### 第一天：安装与运行 在安装Oracle时，可能会遇到一些问题，如配置环境变量、磁盘空间不足或依赖库缺失等。...
- **INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN**: 多表联接。 - **ON**: 指定联接条件。 - **WHERE**: 定义查询条件。 - **GROUP BY**: 对数据进行分组。 - **HAVING**: 对分组后的数据进行过滤。 - *...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今五十年前。简单来说是本身可视...
"学习笔记——sql.zip"这个压缩包文件很可能包含了关于SQL的学习资料，如教程、笔记、示例代码等，旨在帮助用户掌握SQL的基本概念、语法和高级特性。 首先，SQL的基础知识包括数据类型，如整型（INT）、浮点型...
### Oracle SQL 基础知识点概述 #### 一、编写基本的 SQL...以上是Oracle SQL基础知识的核心知识点概览，通过这些内容的学习，可以掌握Oracle SQL的基本操作及高级功能，为进一步深入学习Oracle数据库打下坚实的基础。
根据给定的文件信息“免费的数据库学习笔记”、“数据库学习笔记”以及标签“select面试题”，本篇文章将深入探讨与数据库学习相关的知识点，并重点围绕SQL中的SELECT语句进行讲解，包括其基本用法、进阶技巧以及在...
常见的JOIN类型有INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。 例如，结合员工表和部门表： ```sql SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id...
### Oracle SQL 基础知识点详解 #### 一、SQL 语句执行顺序与分组函数使用 ...以上内容覆盖了 Oracle 数据库的基础 SQL 语法、查询技巧以及常用的表连接、子查询等高级主题，适合初学者系统学习和实践。
根据提供的信息，我们可以总结出...以上是基于所提供的“oracl笔记”文档中提取的关键知识点，涵盖了Oracle数据库的基础操作、权限管理、SQL查询等多个方面。这些知识点对于学习和使用Oracle数据库具有重要的参考价值。
Oracle数据库是全球广泛使用的大型关系型...`oracle笔记_2010-04-16.sql`可能是某次学习或工作过程的记录，里面可能包含了对这些概念的具体应用实例，通过阅读和分析这些笔记，可以加深对Oracle SQL的理解和运用。
{12.12}inner join与outer join比较}{172}{section.12.12} {12.12.1}非等值连接}{174}{subsection.12.12.1} {12.13}DML语句}{175}{section.12.13} {12.13.1}insert}{175}{subsection.12.13.1} {12.13.2}create}...
4. JOIN操作：用于连接两个或多个表，如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等。 5. 分页查询：使用LIMIT和OFFSET关键字在SELECT语句中实现数据分页。 四、数据库设计原则 1. 第一范式（1NF）：...
内外连接指的是内连接(INNER JOIN)和外连接(OUTER JOIN)，用于连接两个表并返回匹配的行；子查询是在一个查询语句内部嵌套另一个查询语句。 - **管理表、视图、索引、序列、约束**：这些都是数据库管理的重要组成...
5. **联接操作**：JOIN语句用于合并两个或更多表中的数据，如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。 6. **视图**：创建虚拟表，方便数据的查询和管理。 7. **索引**：提高数据检索速度的特殊数据...
根据给定文件的信息，我们可以推断出这是一份关于SQL（Structured Query Language，结构化查询语言）初级到中级的学习笔记摘要。尽管没有提供具体的部分内容，但基于标题与描述中的信息，我们可以围绕SQL的基础概念...