oracle 外連線的使用
外連線分為left outer join 左外連線，right outer join 右外連線,full outer join全外連線，oracle9i之前用(+)去支援，而ANSI SQL-92標準的sql用的是left/right/full outer join關鍵字 ，從oracle9i之後，我們既可以使用原有的(+)外連線符合，也可以使用ANSI SQL-92標準的外連線。需要提醒的是，在使用外連線的時候，用where子句和and條件去做過濾限制的時候,需要注意語句的邏輯是否符合我們的預期,總結如下:
如果是ANSI SQL-92 標準的外連線語句
(1) 當在內連線查詢中加入條件時，無論是將它加入到join子句，還是加入到where子句，其效果是完全一樣的，但對於外連線情況就不同了。
(2) 當把條件加入到 join子句時，oracle首先對相關表進行連線,然後再對連線結果做過濾操作,符合條件的紀錄會返回完整的記錄結果,不符合過濾條件的,從表相應的欄位置為空,總之,sql出來的紀錄數肯定是和連線結果集的紀錄數一致。
(3) 如果將條件放到where子句中，oracle優化器可能會先對相應表應用where子句，進行篩選,然後進行連線操作，也可能會在做完表與表的連線後，再來對結果集合做篩選。你可以理解為oracle會先做連線，然後對連線的結果集做過濾，過濾後的記錄數可能會減少，和放在join區別是: where語句會過濾掉不符合條件的記錄，sql出來的記錄數要比連線結果集的紀錄數少，join不會改變連線的結果集記錄數。
如果是oracle傳統的(+)外連線語句
(1) 在傳統的(+)操作中，如果有類似於 and a.col1=xxx這樣的限制條件，其功能和處理方式，和ANSI SQL-92 標準中限制條件放在where子句的情況相同。
(2) 如果是類似於 and a.col1(+)=xxx,則oracle會先做外連線，然後再在外連線的基礎上做過濾，如果不符合過濾條件，只是相應欄位為空，如果符合，則置相應的值，記錄數在此並不會被過濾掉。相對應於ANSI SQL-92 標準中限制條件放在join子句的情況。
ANSI SQL-92：
用where條件去限制
在這種情況下，oracle會按照連線條件去做連線，對於不符合過濾條件的記錄，直接過濾掉。當然，oracle優化器再處理這種情況的時候，在不改變sql功能的情況下，可能會在連線前先應用where條件過濾一些資料，以此來提高sql效率
SQL> insert into dept(deptno,dname) values (99,'test');
SQL> commit;
左連線left join
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno
2 where b.deptno=99;
未選定行
執行計劃
1 Plan hash value: 568005898
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
9 |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
10 |* 4 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 3 - access("B"."DEPTNO"=99)
17 4 - filter("A"."DEPTNO"=99)
如果把上面的sql where 該為對a表的限制 即where a.deptno=99
1 Plan hash value: 1301846388
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS OUTER | | 1 | 19 | 4 (0)| 00:00:01 |
8 |* 2 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 2 - filter("A"."DEPTNO"=99)
17 4 - access("B"."DEPTNO"(+)=99)
可以看到,oracle首先通過索引訪問dept,並且限制了條件b.deptno=99,然後對emp表做全表掃描的時候,自動加了filter,也就是先應用where條件,然後再去做表與表的關聯操作.這樣看來，如果過濾條件放在where子句中，是不是oracle就是先應用where過濾，然後再做連線操作，是不是完全這樣的呢？再看下面的這個例子
SQL> select a.deptno,a.dname,b.empno,b.ename from deptbak a left join empbak b on a.deptno=b.deptno where b.deptno is null;
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
99 test
40 OPERATIONS
1 Plan hash value: 241861675
2
3 -------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 280 | 7 (15)| 00:00:01 |
7 |* 1 | FILTER | | | | | |
8 |* 2 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
9 | 3 | TABLE ACCESS FULL | DEPTBAK | 5 | 50 | 3 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS FULL | EMPBAK | 14 | 140 | 3 (0)| 00:00:01 |
11 -------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - filter("B"."DEPTNO" IS NULL)
17 2 - access("A"."DEPTNO"="B"."DEPTNO"(+))
這個sql的執行計劃清楚的表明，oracle是先把a，b表的記錄都取出來，然後做hash連線，最後才去filter。
可以看到，用where去限制的話，oracle可能先過濾再連線，也有可能先連線再過濾。
用and條件去限制
把where b.deptno=99改為join 中的and b.deptno=99
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno
2 and b.deptno=99;
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
7934 10
7839 10
7782 10
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
7902 20
7876 20
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
7788 20
7566 20
7369 20
1 Plan hash value: 657582733
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 5 (20)| 00:00:01 |
7 |* 1 | HASH JOIN OUTER | | 14 | 266 | 5 (20)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
17 4 - access("B"."DEPTNO"(+)=99)
再來看看對a表的deptno做限制
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno and a.deptno=10;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10 10 ACCOUNTING
7788 20
7839 10 10 ACCOUNTING
7844 30
7876 20
7900 30
7902 20
7934 10 10 ACCOUNTING
1 Plan hash value: 2962868874
2
3 -----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 8 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS OUTER | | 14 | 266 | 8 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | VIEW | | 1 | 12 | 0 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
11 |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
12 -----------------------------------------------------------------------------------------
13
14 Predicate Information (identified by operation id):
15 ---------------------------------------------------
16
17 5 - access("B"."DEPTNO"=10)
18 filter("A"."DEPTNO"=10)
可以看到,在訪問a表的時候並沒有用過濾條件,取出的總的紀錄數還是和a表紀錄數一致.
其實,當deptno的過濾條件放在join子句中的時候, b.deptno=條件為任何值,結果都回出來14條紀錄,
因為此時相當於告訴oracle,我要先對a表全表掃描方式訪問a表所有紀錄,然後再去和b表做左關聯,關聯上的話，就把從表對應欄位的值填上，關聯不上的就置空值。
用oracle 傳統的方式(+)
用傳統(+) 也需要主要一些細節，如下
現在我要根據emp，dept表的資料，來查詢所有emp 資訊，如果dept.deptno=10，則把dname取出來，其他的就為null，用外連線操作，以emp表作為主表，
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno=b.deptno(+) and b.deptno=10;--直接對b.deptno做限制
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7782 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7934 10 10 ACCOUNTING
但是，這個sql結果並不是我們想要的，丟失了deptno<>10的記錄，看其執行計劃
1 Plan hash value: 568005898
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 3 | 57 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS | | 3 | 57 | 4 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
9 |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
10 |* 4 | TABLE ACCESS FULL | EMP | 3 | 21 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 3 - access("B"."DEPTNO"=10)
17 4 - filter("A"."DEPTNO"=10)
從執行計劃來看，oracle自動對a表也加了filter，然後對a，b表做inner join。
如果sql語句改為
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno=b.deptno(+) and b.deptno(+)=10;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7934 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7782 10 10 ACCOUNTING
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
7902 20
7876 20
7788 20
7566 20
7369 20
1 Plan hash value: 657582733
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 5 (20)| 00:00:01 |
7 |* 1 | HASH JOIN OUTER | | 14 | 266 | 5 (20)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
17 4 - access("B"."DEPTNO"(+)=10)
或者sql語句改為
select empno,a.deptno,b.deptno,b.dname from emp a , (select * from dept where deptno=10) b where a.deptno=b.deptno(+) ;
再來看下面的例子
SQL> select empno,b.deptno,a.deptno,a.dname from emp b , dept a where a.deptno=b.deptno(+) and b.deptno is null;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
99 test
40 OPERATIONS
1 Plan hash value: 2146709594
2
3 ----------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 7 (15)| 00:00:01 |
7 |* 1 | FILTER | | | | | |
8 |* 2 | HASH JOIN OUTER | | 14 | 266 | 7 (15)| 00:00:01 |
9 | 3 | TABLE ACCESS FULL | DEPT | 5 | 60 | 3 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - filter("B"."DEPTNO" IS NULL)
17 2 - access("A"."DEPTNO"="B"."DEPTNO"(+))
可以看到，過濾放在了連線操作後，也只有這樣，sql出來的結果才是正確的。
在傳統的(+)操作中，類似於 and a.col1=xxx這樣的條件，oracle會按ANSI SQL-92中的where條件過濾情況去處理；如果是類似於 and a.col1(+)=xxx,則oracle會先做外連線，然後再在外連線的基礎上做過濾，如果不符合過濾條件，只是相應欄位為空，如果符合，則置相應的值，記錄數在此並不會被過濾掉。
right join
right join的情況和left join類似
1 select empno,a.deptno,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno
2* where a.empno=1111
SQL> /
未選定行
1 select empno,a.deptno,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno
2* and a.empno=1111
SQL> /
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
30 SALES
99 test
40 OPERATIONS
20 RESEARCH
10 ACCOUNTING
1 select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno(+)=b.deptno
2* and a.empno=1111
SQL> /
未選定行
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno(+)=b.deptno
2 and a.empno(+)=1111;
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
30 SALES
99 test
40 OPERATIONS
20 RESEARCH
10 ACCOUNTING
因此，
如果是ANSI SQL-92 標準的外連線語句
(4) 當在內連線查詢中加入條件時，無論是將它加入到join子句，還是加入到where子句，其效果是完全一樣的，但對於外連線情況就不同了。
(5) 當把條件加入到 join子句時，oracle首先對相關表進行連線,然後再對連線結果做過濾操作,符合條件的紀錄會返回完整的記錄結果,不符合過濾條件的,相應的欄位為空,總之,總的紀錄數肯定是和主表紀錄數一致。
(6) 如果將條件放到where子句中，oracle優化器可能會先對相應表應用where子句進行篩選,然後進行連線操作，也可能會做完表與表的連線再來對結果集合做篩選，在這種情況下，和放在join中的and區別是: where語句會過濾掉不符合條件的記錄，總的記錄數要比主表的記錄數少。
如果是oracle傳統的(+)外連線語句
(3) 在傳統的(+)操作中，如果有類似於 and a.col1=xxx這樣的限制條件，其功能和處理方式，和ANSI SQL-92 標準中限制條件放在where子句的情況相同。
(4) 如果是類似於 and a.col1(+)=xxx,則oracle會先做外連線，然後再在外連線的基礎上做過濾，如果不符合過濾條件，只是相應欄位為空，如果符合，則置相應的值，記錄數在此並不會被過濾掉。相對應於ANSI SQL-92 標準中限制條件放在join子句的情況。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-584604/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL】Oracle的內連線、左外連線、右外連線及全外連線SQLOracle
- Oracle內連線、外連線、右外連線、全外連線小總結Oracle
- oracle外連線Oracle
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- Oracle 左外連線、右外連線、全外連線小總結Oracle
- 多表外連線的使用
- Oracle 表連線方式詳解（外連結、內連線、自連線）Oracle
- SQL的四種連線:內連線 左外連線 右外連線 全連線SQL
- 深入理解SQL的四種連線-左外連線、右外連線、內連線、全連線SQL
- Oracle 內外連線 join 總結Oracle
- oracle sql內連線_左(右)連線_全外連線_幾種寫法OracleSQL
- 內連線、外連線
- sybase的外連線
- python連線Oracle的外掛cx_Oracle安裝PythonOracle
- Oracle中的外連線簡單介紹(轉)Oracle
- 一分鐘搞懂ORACLE 外連線Oracle
- sql 內連線和外連線SQL
- 外連線與連線順序
- 內連線、外連線總結
- SQL SERVER 自連線、外連線SQLServer
- 連線位置國外的遠端桌面，使用anydesk
- 例項解析外連線 內連線 自連線 全連線
- 【JDBC】使用OracleDataSource建立連線池用於連線OracleJDBCOracle
- 外連線轉換為內連線的情況
- 轉載：內連線與外連線的區別
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- MySQL筆記3——內連線/外連線、多表連線MySql筆記
- Oracle的左連線和右連線Oracle
- sql和hql中join語句區別,以及hibernate中內連線，迫切內連線，左外連線，迫切左外連線,右外連線的區別（合集）...SQL
- 用實驗方法加深理解Oracle的外連線(left/right/full)和內連線(inner)Oracle
- 使用外連線時應注意重複的問題
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別
- sql內連結，外連線SQL
- mysql左外連線MySql
- 行的儲存（塊內連線與塊外連線）
- SQL Server中內連線和外連線的區別SQLServer
- Oracle(+)連線與Join連線Oracle
- Oracle左連線,右連線Oracle