Which is more faster - IN or EXISTS?

Advertisement

Which is more faster - IN or EXISTS?
Which is more faster - IN or EXISTS?
Repaly
It depends,
but for many simple cases the optimizer generates exactly the same plan for IN and EXISTS,
so there shouldn't be any difference in speed.
explain plan for
select * from hr.jobs j
where j.job_id in ( select job_id from hr.job_history );
select * from table(dbms_xplan.display );
plan FOR succeeded.
PLAN_TABLE_OUTPUT
Plan hash value: 3539156008         
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |   
|   0 | SELECT STATEMENT   |              |     8 |   336 |     3   (0)| 00:00:01 |   
|   1 |  NESTED LOOPS SEMI |              |     8 |   336 |     3   (0)| 00:00:01 |   
|   2 |   TABLE ACCESS FULL| JOBS         |    19 |   627 |     3   (0)| 00:00:01 |   
|*  3 |   INDEX RANGE SCAN | JHIST_JOB_IX |     4 |    36 |     0   (0)| 00:00:01 |   
Predicate Information (identified by operation id):          
   3 - access("J"."JOB_ID"="JOB_ID")
15 rows selected
explain plan for
select * from hr.jobs jb
where exists (
  select 1 from hr.job_history hi
  where jb.job_id = hi.job_id
select * from table(dbms_xplan.display );
plan FOR succeeded.
PLAN_TABLE_OUTPUT
Plan hash value: 3539156008         
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |   
|   0 | SELECT STATEMENT   |              |     8 |   336 |     3   (0)| 00:00:01 |   
|   1 |  NESTED LOOPS SEMI |              |     8 |   336 |     3   (0)| 00:00:01 |   
|   2 |   TABLE ACCESS FULL| JOBS         |    19 |   627 |     3   (0)| 00:00:01 |   
|*  3 |   INDEX RANGE SCAN | JHIST_JOB_IX |     4 |    36 |     0   (0)| 00:00:01 |   
Predicate Information (identified by operation id):          
   3 - access("JB"."JOB_ID"="HI"."JOB_ID")
15 rows selected
Read More: The other 6 answers