We often use join related statements to make associative queries. What happens to the result when on is combined with and and where?

When using join on, pay attention to the difference and where and how to use it

JOIN ON AND JOIN ON AND The mode of JOIN ON and JOIN ON and is similar to ON condition 1 and ON condition 2, both of which are based on JOIN to associate the results of two tables and extract the data after association. Select * from t1 right join t2 on t1.object_id=t2.object_id and t1.object_id=1989; select * from t1 right join t2 on t1.object_id=t2.object_id and t1.object_id=1989; Elapsed: 00:00:05.33 Execution Plan 92937 rows selected. Elapsed: 00:00:05

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):

Mysql > select (“T1″.”OBJECT_ID”(+)=”T2″.”OBJECT_ID”); 2 – access(“T1”.”OBJECT_ID”(+)=1989) – access(“T1”.”OBJECT_ID”(+)=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 SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SELECT * FROM WHERE SQL> select t2.object_id t2_id from t1 www.sangpi.comright join t2 on t1.object_id=t2.object_id where id= 0 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