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 method 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. For example, the following

select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id and t1.object_id=1989; 92937 rows selected. Elapsed: 00:00:05. 33 Execution Plan (1) | 00:00:05 | | | * 1 HASH JOIN RIGHT OUTER | | 102 | 372 k | 2609 k (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”http://www.coubai.com(+)=1989) — same as above, output all 1989 (ignore this condition here) 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 is to filter the results after join on (in order to achieve the most efficient execution, it is advanced WHERE conditional screening, and then join association), as shown below

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 2-access (“T2”.”OBJECT_ID”=1989) 4-access (“T1”.”OBJECT_ID”=1989) Note: Elapsed: 00:00:00.06 Execution Plan 2-access (“T2”

  • dynamic sampling used for this statement (level=2)

Statistics

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