我們經常使用 join相關語句做關聯查詢，那麼在join連線方式後邊，on  結合and 和 where結果會發生什麼變化呢？

在使用 join  on 時 注意  and  where 區別和如何使用

#### join on  and
join on and 方式 類似於  on 條件1 and  on 條件2，都是 基於join 關聯兩個表結果 ，取出關聯後資料。 舉例如下
``` sql
select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id and t1.object_id=1989;
--輸出結果太多，省略，看 下邊access部分
92937 rows selected.

Elapsed: 00:00:05.33

Execution Plan
----------------------------------------------------------
Plan hash value: 2539735012

--------------------------------------------------------------------------------
| Id  | Operation	      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   102K|  2609K|   372	 (1)| 00:00:05 |
|*  1 |  HASH JOIN RIGHT OUTER|        |   102K|  2609K|   372	 (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN    | T1_IDX |     1 |    13 |     1	 (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T2     |   102K|  1304K|   371	 (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."OBJECT_ID"(+)="T2"."OBJECT_ID")  -- t1 右連線 t2的 object_id，以t2輸出為主，也就是輸出t2所有內容
   2 - access("T1"."OBJECT_ID"(+)=1989)  --同上，輸出1989所有內容（這個條件在此可以忽略）

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

Statistics
----------------------------------------------------------
	 16  recursive calls
	  0  db block gets
       7580  consistent gets
	  1  physical reads
	  0  redo size
    1699670  bytes sent via SQL*Net to client
      68668  bytes received via SQL*Net from client
       6197  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      92937  rows processed1
	  
```

#### join on  where
join on where ，是 join on連線之後對結果再進行篩選(為達到執行效率最有，是先進性where條件篩選，再join關聯），舉例如下
``` sql
SQL> select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id where t1.object_id=1989;

T2_ID
----------
      1989

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2511910206

-------------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	      |     1 |    26 |     2	(0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|	      |     1 |    26 |     2	(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | T2_IDX |     1 |    13 |     1	(0)| 00:00:01 |
|   3 |   BUFFER SORT	     |	      |     1 |    13 |     1	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN  | T1_IDX |     1 |    13 |     1	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T2"."OBJECT_ID"=1989)
   4 - access("T1"."OBJECT_ID"=1989)

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

Statistics
----------------------------------------------------------
	 21  recursive calls
	  0  db block gets
	147  consistent gets
	  3  physical reads
	  0  redo size
	524  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
```

【SQL】Oracle SQL join on語句and和where使用區別

相關文章