我们经常使用 join相关语句做关联查询，那么在join连接方式后边，on 结合and 和 where结果会发生什么变化呢？

在使用 join on 时注意 and where 区别和如何使用

join on and

join on and 方式类似于 on 条件1 and on 条件2，都是基于join 关联两个表结果，取出关联后数据。举例如下

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> 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使用区别

join on and

join on where