[20150423]left right join.txt

--oracle sql 语法里面存在left join,right join连接,而且这种写法是sql ansi标准.
--我个人工作习惯特别不喜欢这种写法,使用(+)方式更多一些.

-- 实际上我以前开始学习oracle的时候,对于使用(+)那边输出NULL非常混乱.每次都拿scott schema的dept,emp来测试一次.
-- 后来我简单的记忆是+表示多的意思,也就这个(+)对应的表不存在时输出NULL值.

--为了加强记忆,我把left right 也做一个总结,便于以后工作非方便:

1.建立测试环境:

SCOTT@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> create table t1 as select rownum id,rownum||'t1' data from dual connect by levelTable created.

SCOTT@test> create table t2 as select rownum+1 id,rownum||'t2' data from dual connect by levelTable created.

SCOTT@test> @&r/stats t1
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@test> @&r/stats t2
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@test> select * from t1;
          ID DATA
------------ -----
           1 1t1
           2 2t1
           3 3t1
           4 4t1
           5 5t1

SCOTT@test> select * from t2;
          ID DATA
------------ -----
           2 1t2
           3 2t2
           4 3t2
           5 4t2
           6 5t2

2.先学习+的用法:

SCOTT@test> set null NULL
SCOTT@test> select * from t1,t2 where t1.id(+)=t2.id ;
          ID DATA            ID DATA
------------ ----- ------------ -----
           2 2t1              2 1t2
           3 3t1              3 2t2
           4 4t1              4 3t2
           5 5t1              5 4t2
NULL         NULL             6 5t2

-- 可以发现t1.id没有6的值,在t1.id(+)=t2.id条件下. 使用NULL填充.

SCOTT@test> select * from t1,t2 where t1.id=t2.id (+);
          ID DATA            ID DATA
------------ ----- ------------ ----
           2 2t1              2 1t2
           3 3t1              3 2t2
           4 4t1              4 3t2
           5 5t1              5 4t2
           1 1t1   NULL         NULL

-- 可以发现t2.id没有1的值,在t1.id=t2.id(+)条件下. 使用NULL填充.
-- 我的记忆方法: +表示多的意思,也就这个表不存在对应信息时输出NULL值.

3.left right join.
SCOTT@test> select * from t1 left join t2 on t1.id=t2.id ;
          ID DATA            ID DATA
------------ ----- ------------ -----
           2 2t1              2 1t2
           3 3t1              3 2t2
           4 4t1              4 3t2
           5 5t1              5 4t2
           1 1t1   NULL         NULL

-- 记忆方法:left join是以左表为准的.左表(t1)的记录将会全部表示出来,而右表(t2)只会显示符合搜索条件的记录.T2表记录不满足条
   件的均为NULL.相当于+ 作用在右边的表.

SCOTT@test> select * from t1 right join t2 on t1.id=t2.id ;
          ID DATA            ID DATA
------------ ----- ------------ -----
           2 2t1              2 1t2
           3 3t1              3 2t2
           4 4t1              4 3t2
           5 5t1              5 4t2
NULL         NULL             6 5t2

-- 记忆方法:right join是以右表为准的.右表(t2)的记录将会全部表示出来,而左表(t1)只会显示符合搜索条件的记录.T1表记录不满足条
   件的均为NULL.相当于+ 作用在左边的表.

4.好像11g还是10.2.0.4还支持一种写法使用全连接:

SCOTT@test> select * from t1 full outer join t2 on t1.id=t2.id;
          ID DATA            ID DATA
------------ ----- ------------ ------
           2 2t1              2 1t2
           3 3t1              3 2t2
           4 4t1              4 3t2
           5 5t1              5 4t2
           1 1t1   NULL         NULL
NULL         NULL             6 5t2
6 rows selected.

5.是否存在等价的sql写法:

--从上面的例子可以看出left,right可以使用+写法来代替.
--但是我加入如下条件看看:
SCOTT@test> select * from t1 left join t2 on t1.id=t2.id and t1.id=2;
          ID DATA            ID DATA
------------ ----- ------------ -----
           1 1t1   NULL         NULL
           2 2t1              2 1t2
           3 3t1   NULL         NULL
           4 4t1   NULL         NULL
           5 5t1   NULL         NULL

--如果写成如下:
SCOTT@test> select * from t1 , t2 where t1.id=t2.id(+) and t1.id=2;
          ID DATA            ID DATA
------------ ----- ------------ -----
           2 2t1              2 1t2

--仅仅1行输出.明显跟前面的输出不同.

--仔细看看,这种写法使用+写出来存在一定难度的.而且我第1次看到很容易产生误解.查询的条件是t1.id=t2.id and t1.id=2.
-- 为什么t1.id=1,3,4,5都会输出.如果能理解前面的讲解很容易明白.

--我第1次看到实际上源于开发的写错.实际他想表达的是select * from t1 left join t2 on t1.id=t2.id where t1.id=2;

--好了,看看如何使用+号实现.先看看执行计划:

SCOTT@test> select * from t1 left join t2 on t1.id=t2.id and t2.id=2;
          ID DATA            ID DATA
------------ ----- ------------ ------
           2 2t1              2 1t2
           5 5t1   NULL         NULL
           4 4t1   NULL         NULL
           3 3t1   NULL         NULL
           1 1t1   NULL         NULL

SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bzjahpxh02m63, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t2.id=2
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER   |      |      5 |    70 |     7  (15)| 00:00:01 |  1269K|  1269K|  983K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |     7 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9E43CB6E
   2 - SEL$9E43CB6E / T1@SEL$2
   3 - SEL$9E43CB6E / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   3 - filter("T2"."ID"=2)

SCOTT@test> select * from t1,t2 where t1.id=t2.id(+) and t2.id(+)=2;
          ID DATA                                                 ID DATA
------------ ------------------------------------------ ------------ ------------------------------------------
           2 2t1                                                   2 1t2
           5 5t1                                        NULL         NULL
           4 4t1                                        NULL         NULL
           3 3t1                                        NULL         NULL
           1 1t1                                        NULL         NULL

--如果写成这样呢?使用+号. 10g下生成的计划是这样,我没有办法转化成hash join outer.估计是bug之类的问题.

SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  082zsnprdkj5a, child number 0
-------------------------------------
select  * from t1 left join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 3158630456
------------------------------------------------------------------------------
| Id  | Operation            | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER  |      |      5 |   215 |    18   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |      5 |    35 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |      1 |    36 |     3   (0)| 00:00:01 |
|*  4 |    FILTER            |      |        |       |            |          |
|*  5 |     TABLE ACCESS FULL| T2   |      1 |     7 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$64EAE176
   2 - SEL$64EAE176 / T1@SEL$2
   3 - SEL$1        / from$_subquery$_004@SEL$2
   4 - SEL$1
   5 - SEL$1        / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T1"."ID"=2)
   5 - filter("T2"."ID"=2)

--换成11g下测试:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  082zsnprdkj5a, child number 0
-------------------------------------
select  * from t1 left join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     5 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      5 |    70 |     5  (20)| 00:00:01 |  1156K|  1156K|  633K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9E43CB6E
   2 - SEL$9E43CB6E / T1@SEL$2
   3 - SEL$9E43CB6E / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T1"."ID"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN 2 ELSE
              2 END )

--注意看access条件,access("T1"."ID"="T2"."ID" AND "T1"."ID"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN 2 ELSE 2 END ) 很奇
--怪吧!

--如果使用10053分析,最终得到的是:

SELECT "T1"."ID" "ID","T1"."DATA" "DATA","T2"."ID" "ID","T2"."DATA" "DATA" FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE "T1"."ID"="T2"."ID"(+) AND "T1"."ID"=CASE  WHEN ("T2"."ID"(+) IS NOT NULL) THEN 2 ELSE 2 END;

--也就是写成这样:
SELECT * FROM T1,T2 WHERE T1.ID=T2.ID(+) AND T1.ID=CASE  WHEN (T2.ID(+) IS NOT NULL) THEN 2 ELSE 2 END;

SCOTT@test> set null null
SCOTT@test> SELECT * FROM T1,T2 WHERE T1.ID=T2.ID(+) AND T1.ID=CASE  WHEN (T2.ID(+) IS NOT NULL) THEN 2 ELSE 2 END;
        ID DATA          ID DATA
---------- ----- ---------- -----
         2 2t1            2 1t2
         4 4t1   null       null
         3 3t1   null       null
         1 1t1   null       null
         5 5t1   null       null

--如果拿这个到10.2.0.4下执行,结果如下(估计是bug):

SCOTT@test> SELECT * FROM T1,T2 WHERE T1.ID=T2.ID(+) AND T1.ID=CASE  WHEN (T2.ID(+) IS NOT NULL) THEN 2 ELSE 2 END;
          ID DATA            ID DATA
------------ ----- ------------ -----
           2 2t1              2 1t2

--我在想如果写成这样SELECT * FROM T1,T2 WHERE T1.ID=T2.ID(+) AND T1.ID=CASE  WHEN (T2.ID(+) IS NOT NULL) THEN 2 ELSE 2 END;
--有多少人能读懂正确的含义.

--从个人喜好讲,我更加喜欢使用+,但是从标准讲许多人建议使用left join,right join语法.
--好在实际的语句中很少写这么复杂,或者表达的语义不会这么复杂.