[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语法.
--好在实际的语句中很少写这么复杂,或者表达的语义不会这么复杂.
