oracle的left join，right join和full join的一點介紹(R1)
oracle的left join，right join和full join的一點介紹
以下是摘自oracle ocp9i文件：
outer join syntax:
1)you use an outer join to also see rows that do not meet the join condition
2)the outer join operator is the plus sign(+)
outer join restrictions:
1)the outer join operator can appear on only one side of the expression:the side that has information missing.it returns those rows from one table that have no direct match in the other table.
2)a condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator.
配置實驗環境：
hr@ORCL> drop table a;
hr@ORCL> drop table b;
hr@ORCL> create table a(id number,name varchar2(10));
hr@ORCL> create table b(id number,name varchar2(10));
hr@ORCL> insert into a values(1,'a');
hr@ORCL> insert into a values(2,'b');
hr@ORCL> insert into a values(3,'c');
hr@ORCL> insert into b values(1,'a');
hr@ORCL> insert into b values(2,'b');
hr@ORCL> insert into b values(4,'d');
hr@ORCL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
hr@ORCL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
hr@ORCL> commit;
--全外連線的結果是：
1）選出所有滿足條件的結果
2）以左表為準，將左表不滿足條件的結果接在左邊
3）以右表為準，把右表不滿足條件的結果接在右邊
4）將以上結果全部合起來
hr@ORCL> select a.id,b.id from a full join b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
--left out join和oracle的加號在右結果是相同。同理，right out join和加號在左是一樣的。（sql99的語法和oracle私有語法的比較）
hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+);
ID ID
---------- ----------
1 1
2 2
3
hr@ORCL> select a.id,b.id from a left outer join b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
--結果類似於from a left join b on a.col=b.col and a.coln=....。單個列選擇條件的列是基表（加號在誰身上誰是從表，沒有加號的一方是基表）的用decode和+改寫，不能用一般的改寫，若不是基表的可以簡單用+改寫。比如：
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID AND a.NAME='a';
ID ID
---------- ----------
1 1
2
3
1 1
4
2
改寫之：
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.id=decode(a.NAME,'a',b.ID(+))
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a' AND a.ID IS NULL;
ID ID
---------- ----------
1 1
2
3
1 1
2
4
用union來實現上面例子的full join結果，需要考慮表的關係。
1）如果是兩表1:1
加號在左，以右為準（相當於sql99的right join）
加號在右，以左為準（相當於sql99的left join）
hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+)
2 union
3 select a.id,b.id from a,b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
2）如果是兩表1:n，用union剔重就不正確。
hr@ORCL> insert into a values(1,'a');
hr@ORCL> commit;
hr@ORCL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
1 a
hr@ORCL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
--1:n用UNION不正確
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
1 1
2 2
3
4
--正確解法有三。注意，在使用sql時，任何時候，任何地方，一定要考慮null！！！切記。
法一：
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.ID IS NULL;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
法二：
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
3
1 1
1 1
2 2
4
法三：
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
--逗號和full join是不一樣的。另外，full join須加上關鍵字on，才是完整的語句。
hr@ORCL> select p.id,t.id from p,t where p.id=t.id;
ID ID
---------- ----------
1 1
3 3
hr@ORCL> select p.id,t.id from p full join t on p.id=t.id;
ID ID
---------- ----------
1 1
3 3
2
5
全外連線和union連線都可以實現相同結果。我們來看一下他們的執行計劃。
全外連線的執行計劃：
hr@ORCL> select a.id,b.id from a full join b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
Execution Plan
----------------------
Plan hash value: 2192011130
-----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 13 (8)| 00:00:01 |
| 1 | VIEW | | 4 | 104 | 13 (8)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 312 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 195 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN ANTI | | 1 | 26 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
union的執行計劃：
hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+)
2 union
3 select a.id,b.id from a,b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
Execution Plan
----------------------
Plan hash value: 891669117
-----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 15 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 156 | 15 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 78 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 3 | 78 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"(+)="B"."ID")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk)
4 rows processed
顯然，union連線的cpu代價要比full join連線來得多。此外，union還會暗含一個排序操作。當資料量海量時，估計會對效能帶來一定的影響。而且，在oracle的私有語法裡，是沒有全外連線的，只能透過union連線來模擬full join。所以，建議需要使用外連線時，請使用full join，不要用union模擬。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26613085/viewspace-1104739/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql中的join、left join、right joinSQL
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- sql left join 和 right join解釋SQL
- left join,right join,inner join的條件on和where的區別
- sql之left join、right join、inner join的區別SQL
- Oracle Left join right jionOracle
- sql的left join 、right join 、inner join之間的區別SQL
- 連線查詢簡析 join 、 left join 、 right join
- SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）SQL
- oracle知識整理(1) union和union all的區別,left join和right join的區別(各種join的區別)Oracle
- LEFT JOIN 和JOIN 多表連線
- Inner Join, Left Outer Join和Association的區別
- 兩種連線的表達 ：left(right) join 和 (+)
- mysql left join轉inner joinMySql
- 【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER JSQL
- mysql + left joinMySql
- ZT：簡單介紹join,outer-join,semi-join,anti-join的區別
- Oracle -- left join 什麼情況可以直接改成joinOracle
- LEFT JOIN 需要注意的點（Presto）REST
- oracle update left join查詢Oracle
- mysql的left join和inner join的效率對比，以及如何優化MySql優化
- mysql常用連線查詢join，left,right,crossMySqlROS
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- Mysql join 的本質，Nested Loop Join 和 Hash JoinMySqlOOP
- sql的left join 命令詳解SQL
- 深入Oracle的left join中on和where的區別詳解Oracle
- SQL JOIN 簡單介紹SQL
- sql server left join問題SQLServer
- 【原】Oracle中Left Outer Join和外關聯(+)的區別Oracle
- Oracle中left join中右表的限制條件Oracle
- MySQL 之 LEFT JOIN 避坑指南MySql
- mysql left join 優化學習MySql優化
- hash join\nest loop join\sort merge join的實驗OOP
- Oracle資料庫聯接(inner join ,outer join)和NOT IN的特殊情況Oracle資料庫
- ORACLE Hash JoinOracle
- Oracle Sort JoinOracle
- oracle natural joinOracle
- Oracle SQL JOINOracleSQL