我们经常使用 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 ```