`
weishaoxiang
  • 浏览: 96521 次
  • 性别: Icon_minigender_1
  • 来自: 广西
社区版块
存档分类
最新评论

ORACLE学习笔记系列(6)Outer Join新旧语法对比分析

 
阅读更多

      从Oracle9i开始,支持ANSI/ISO SQL92标准,对OUTER JOINLEFT/RIGHT/FULL OUTER JOIN。而在9i之前,Oralce使用的是SQL86标准,Oracle使用了OUTRT JOIN 操作符‘+’来实现外连接,但是这个‘+’号的语法只支持LEFT/RIGHT OUTER JOIN,不支持FULL OUTER JOIN,要实现FULL OUTER JOIN可以通过UNION ALL实现。

 

--为了能够通过实际数据进行验证,也为后面的测试使用,在这里创建abc 三张表。

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>

 

 

Oracle9i开始新旧语法并存,新语法的优点如下:

 

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:ORIN操作数中不允许外部联接运算符(+)”,因为老语法的连接条件不能用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 JOINLEFT JOIN):左边的表是基表,右边的表是从表;

            右外连接(RIGHT OUTER JOINRIGHT JOIN):右边的表是基表,左边的表是从表;

            全外连接(FULL OUTER JOINFULL 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,因此能查询出基表A4条信息。

 

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 JOINRIGHT JOIN)与左外连接(LEFT OUTER JOINLEFT JOIN) 是类似的,在此不再具体分析。

对于OUTER JOIN 新旧语法大部分都是相同的,但是新语法对条件的限制更加灵活,可读性也更强。不管是新语法还是旧语法,连接的列或者限制条件的列不一样,都有可能导致整个查询的连接方式和查询结果的改变,所以在实际应用中都需要认真谨慎核对。

分享到:
评论

相关推荐

    Oracle学习笔记

    从提供的标题、描述以及部分文本内容来看,这份“Oracle学习笔记”主要围绕Oracle数据库中的查询语句及其各种类型进行深入探讨。尽管描述部分重复了标题的文字,但主要内容段落包含了丰富的信息,下面将对这些知识点...

    Oracle学习笔记.doc

    ### Oracle学习笔记知识点详解 #### 一、SQL概述与Oracle简介 - **SQL**(Structured Query Language,结构化查询语言)是一种用于管理关系型数据库的标准语言。它被用来执行各种数据库操作,如查询数据、更新数据...

    OracleSQL笔记

    ### Oracle SQL 笔记知识点详解 #### 一、SQLPlus 命令及环境变量 Oracle_sid - **SQLPlus 命令位置**:在 Oracle 安装目录下的 `bin` 文件夹中,可以通过 SQLPlus 来执行 SQL 命令。 - **Oracle_sid 环境变量**:...

    SQL学习笔记(pdf)

    这份"SQL学习笔记"涵盖了SQL的基础概念、语法以及高级特性,是学习数据库管理和数据分析的宝贵资料。 1. **SQL基础** - 数据库概念:了解什么是数据库,它的作用以及数据库管理系统(DBMS)如何工作。 - SQL简介...

    Oracle笔记

    这篇笔记将带你逐步探索Oracle的学习之旅,从安装、表管理到复杂的SQL查询,深入理解Oracle的精髓。 ### 第一天:安装与运行 在安装Oracle时,可能会遇到一些问题,如配置环境变量、磁盘空间不足或依赖库缺失等。...

    Oracle数据库基础笔记大纲

    - **INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN**: 多表联接。 - **ON**: 指定联接条件。 - **WHERE**: 定义查询条件。 - **GROUP BY**: 对数据进行分组。 - **HAVING**: 对分组后的数据进行过滤。 - *...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...

    学习笔记——sql.zip

    "学习笔记——sql.zip"这个压缩包文件很可能包含了关于SQL的学习资料,如教程、笔记、示例代码等,旨在帮助用户掌握SQL的基本概念、语法和高级特性。 首先,SQL的基础知识包括数据类型,如整型(INT)、浮点型...

    oracle尚观培训文档

    ### Oracle SQL 基础知识点概述 #### 一、编写基本的 SQL...以上是Oracle SQL基础知识的核心知识点概览,通过这些内容的学习,可以掌握Oracle SQL的基本操作及高级功能,为进一步深入学习Oracle数据库打下坚实的基础。

    免费的数据库学习笔记

    根据给定的文件信息“免费的数据库学习笔记”、“数据库学习笔记”以及标签“select面试题”,本篇文章将深入探讨与数据库学习相关的知识点,并重点围绕SQL中的SELECT语句进行讲解,包括其基本用法、进阶技巧以及在...

    oracle数据库sql基础

    常见的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笔记

    ### Oracle SQL 基础知识点详解 #### 一、SQL 语句执行顺序与分组函数使用 ...以上内容覆盖了 Oracle 数据库的基础 SQL 语法、查询技巧以及常用的表连接、子查询等高级主题,适合初学者系统学习和实践。

    oracl笔记

    根据提供的信息,我们可以总结出...以上是基于所提供的“oracl笔记”文档中提取的关键知识点,涵盖了Oracle数据库的基础操作、权限管理、SQL查询等多个方面。这些知识点对于学习和使用Oracle数据库具有重要的参考价值。

    Oracle常用SQL语句复习

    Oracle数据库是全球广泛使用的大型关系型...`oracle笔记_2010-04-16.sql`可能是某次学习或工作过程的记录,里面可能包含了对这些概念的具体应用实例,通过阅读和分析这些笔记,可以加深对Oracle SQL的理解和运用。

    Java学习笔记-个人整理的

    {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}...

    最全面的sql数据库基础知识笔记

    4. JOIN操作:用于连接两个或多个表,如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等。 5. 分页查询:使用LIMIT和OFFSET关键字在SELECT语句中实现数据分页。 四、数据库设计原则 1. 第一范式(1NF):...

    java知识点汇总学习路线与笔记

    内外连接指的是内连接(INNER JOIN)和外连接(OUTER JOIN),用于连接两个表并返回匹配的行;子查询是在一个查询语句内部嵌套另一个查询语句。 - **管理表、视图、索引、序列、约束**:这些都是数据库管理的重要组成...

    sql-oracle-bhcc:BHCC学生-CIT 236-SQL编程-SPRING2020

    5. **联接操作**:JOIN语句用于合并两个或更多表中的数据,如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。 6. **视图**:创建虚拟表,方便数据的查询和管理。 7. **索引**:提高数据检索速度的特殊数据...

    黑马程序员关于SQL的初级及中级笔记摘要.docx

    根据给定文件的信息,我们可以推断出这是一份关于SQL(Structured Query Language,结构化查询语言)初级到中级的学习笔记摘要。尽管没有提供具体的部分内容,但基于标题与描述中的信息,我们可以围绕SQL的基础概念...

Global site tag (gtag.js) - Google Analytics