full join 与 left join 和 right join

/*建立环境*/

DROP TABLE L PURGE;

DROP TABLE R PURGE;

/*左表*/

CREATE TABLE L AS

SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL

SELECT 'left_2','2' AS v FROM dual UNION ALL

SELECT 'left_3','3' AS v FROM dual UNION ALL

SELECT 'left_4','4' AS v FROM dual;

/*右表*/

CREATE TABLE R AS

SELECT 'right_3' AS str,'3' AS v FROM dual UNION ALL

SELECT 'right_4','4' AS v FROM dual UNION ALL

SELECT 'right_5','5' AS v FROM dual UNION ALL

SELECT 'right_6','6' AS v FROM dual;

/*1、inner join，特点：只返回两表相匹配的数据，左表的1、2，右表的5、6都没有显示*/ 

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L INNER JOIN R ON L.v = R.v ORDER BY 1,2;

/*JOIN写法*/

LEFT_STR RIGHT_STR

------ -------

left_3  right_3

left_4  right_4

/*加WHERE条件写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L ,R WHERE L.v = R.v ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_3  right_3

left_4  right_4

/*2、left join，特点，左表返回所有数据，右表中只返回与左表匹配的数据3、4，5、6都没有显示*/

/*join写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L LEFT JOIN R ON L.v = R.v ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_1

left_2

left_3  right_3

left_4  right_4

/*(+)写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L,R WHERE L.v = R.v(+)ORDER BY1,2;

LEFT_STR RIGHT_STR

------ -------

left_1

left_2

left_3  right_3

left_4  right_4

/*3、right join，特点，左表中只返回与左表匹配的数据3、4，1、2都没有显示，右表返回所有数据*/

/*join写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L RIGHT JOIN R ON L.v = R.v ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_3  right_3

left_4  right_4

        right_5

        right_6

/*(+)写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L,R WHERE L.v(+) = R.v ORDER BY1,2;

LEFT_STR RIGHT_STR

------ -------

left_3  right_3

left_4  right_4

        right_5

        right_6

/*4、full join，特点，左右表均返回所有数据，但只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据*/

/*join写法*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L FULL JOIN R ON R.v = L.v ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_1

left_2

left_3  right_3

left_4  right_4

        right_5

        right_6

6 rows selected

/*full join无(+)写法*/

/*建议：如无特殊目的，写语句时尽量要用JOIN写法。表间关系简单明了*/

 

/*join语句中的过滤条件*/

/*当过滤条件在join 后where前时过滤范围是当前表，而非join后的结果集，示例如下*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L LEFT JOIN R ON R.v = L.v AND R.V = 3 ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_1

left_2

left_3  right_3

left_4

/*该语句可以改写为*/

SQL> SELECT L.str AS LEFT_str,R.str ASRIGHT_str FROM L LEFT JOIN (SELECT * FROM R WHERE R.V = 3)R ON R.v = L.v ORDERBY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_1

left_2

left_3  right_3

left_4

/*当过滤条件在where后时过滤范围是join后的结果集*/

SQL> SELECT L.str AS LEFT_str,R.str AS RIGHT_str FROM L LEFT JOIN R ON R.v =L.vWHERE R.V = 3 ORDER BY 1,2;

LEFT_STR RIGHT_STR

------ -------

left_3  right_3

/*这时我发看下sql的plan，发现cbo直接转为了innerjoin（id=2 hash join）*/

SQL> EXPLAIN PLAN FOR SELECT L.str ASLEFT_str,R.str AS RIGHT_str FROM L LEFT JOIN R ON R.v = L.v WHERE R.V = '3'ORDER BY 1,2;

Explained

 

SQL> SELECT * FROMTABLE(DBMS_XPLAN.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2830950017

----------------------------------------------------------------------------

| Id | Operation           | Name |Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0| SELECT STATEMENT    |      |    1 |    23 |     8 (25)| 00:00:01 |

|   1|  SORT ORDER BY      |     |     1 |    23 |    8  (25)| 00:00:01 |

|*  2|   HASH JOIN         |     |     1 |    23 |    7  (15)| 00:00:01 |

|*  3|    TABLE ACCESS FULL| L    |     1|    11 |     3  (0)| 00:00:01 |

|*  4|    TABLE ACCESS FULL| R    |    1 |    12 |     3  (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("R"."V"="L"."V")

   3- filter("L"."V"='3')

   4- filter("R"."V"='3')

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

   -dynamic sampling used for this statement (level=2)

22 rows selected

 

 

/*Join改写*/

/*案例1：如上例中的inner join中，仅返回表L的中的列*/

SQL> SELECT L.str AS left_str,L.v ASleft_v FROM L INNER JOIN R ON L.v = R.v ORDER BY 1,2;

LEFT_STR LEFT_V

-------- ------

left_3  3

left_4  4

 

SQL> SELECT L.str AS left_str,L.v ASleft_v FROM L WHERE EXISTS(SELECT NULL FROM R WHERE L.v = R.v) ORDER BY 1,2;

LEFT_STR LEFT_V

-------- ------

left_3  3

left_4  4

 

SQL> SELECT L.str AS left_str,L.v ASleft_v FROM L WHERE L.V IN (SELECT V FROM R WHERE L.v = R.v) ORDER BY 1,2;

LEFT_STR LEFT_V

-------- ------

left_3  3

left_4  4

 

/*下面我们看下改写后两个语句的plan*/

SQL> EXPLAIN PLAN FOR SELECT L.str ASleft_str,L.v AS left_v FROM L WHERE EXISTS(SELECT NULL FROM R WHERE L.v = R.v)ORDER BY 1,2;

Explained

 

SQL> SELECT * FROMTABLE(DBMS_XPLAN.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3485358467

----------------------------------------------------------------------------

| Id | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0| SELECT STATEMENT    |      |    4 |    56 |     8 (25)| 00:00:01 |

|   1|  SORT ORDER BY      |     |     4 |    56 |    8  (25)| 00:00:01 |

|*  2|   HASH JOIN SEMI    |     |     4 |    56 |    7  (15)| 00:00:01 |

|   3|    TABLE ACCESS FULL| L    |    4 |    44 |     3  (0)| 00:00:01 |

|   4|    TABLE ACCESS FULL| R    |    4 |    12 |     3  (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("L"."V"="R"."V")

Note

-----

   -dynamic sampling used for this statement (level=2)

20 rows selected

 

SQL> EXPLAIN PLAN FOR SELECT L.str ASleft_str,L.v AS left_v FROM L WHERE L.V IN (SELECT V FROM R WHERE L.v = R.v)ORDER BY 1,2;

Explained

 

SQL> SELECT * FROMTABLE(DBMS_XPLAN.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3485358467

----------------------------------------------------------------------------

| Id | Operation           | Name |Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0| SELECT STATEMENT    |      |    4 |    56 |     8 (25)| 00:00:01 |

|   1|  SORT ORDER BY      |     |     4 |    56 |    8  (25)| 00:00:01 |

|*  2|   HASH JOIN SEMI    |     |     4 |    56 |    7  (15)| 00:00:01 |

|   3|    TABLE ACCESS FULL| L    |    4 |    44 |     3  (0)| 00:00:01 |

|   4|    TABLE ACCESS FULL| R    |    4 |    12 |     3  (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("L"."V"="V")

Note

-----

   -dynamic sampling used for this statement (level=2)

20 rows selected

 

SQL>

 

In与exists两个语句的plan一样，说明cbo对语句进行了转换，那我们也就没必要争论in与exists哪个快哪个慢了。