In vs Exists?

Does using In or Exists make a difference in how a query is executed?  Let's take a look.

The query will count on suboject_names there are for a match on object_id between big_tab and small_tab. We'll take a look at these two ways to write the query:

select count(b.subobject_name)
  from big_tab b
 where exists ( select null from small_tab s where s.object_id = b.object_id )

select count(subobject_name)
  from big_tab
 where object_id in ( select object_id from small_tab )

The test is really simple, in SQLPlus we'll use the AUTOTRACE feature to see what happens for each query.  I've run each statement a couple of times to warm up the buffer cache and to get all the hard parsing taken care of. 

Here are the results from AUTOTRACE:

SQL> set autotrace on
SQL> get ainex1.sql
  1  select count(subobject_name)
  2    from big_tab
  3*  where object_id in ( select object_id from small_tab )
SQL> r
  1  select count(subobject_name)
  2    from big_tab
  3*  where object_id in ( select object_id from small_tab )

COUNT(SUBOBJECT_NAME)
---------------------
                    0

Execution Plan
----------------------------------------------------------
Plan hash value: 2588413792

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |    11 |  5836   (3)| 00:01:11 |
|   1 |  SORT AGGREGATE        |           |     1 |    11 |            |          |
|*  2 |   HASH JOIN RIGHT SEMI |           | 16000 |   171K|  5836   (3)| 00:01:11 |
|   3 |    INDEX FAST FULL SCAN| SMALL_IDX |   500 |  2000 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | BIG_TAB   |  2302K|    15M|  5805   (2)| 00:01:10 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"="OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      32777  consistent gets
      32768  physical reads
          0  redo size
        538  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> get ainex2.sql
  1  select count(b.subobject_name)
  2    from big_tab b
  3*  where exists ( select null from small_tab s where s.object_id = b.object_id )
SQL> r
  1  select count(b.subobject_name)
  2    from big_tab b
  3*  where exists ( select null from small_tab s where s.object_id = b.object_id )

COUNT(B.SUBOBJECT_NAME)
-----------------------
                      0

Execution Plan
----------------------------------------------------------
Plan hash value: 2588413792

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |    11 |  5836   (3)| 00:01:11 |
|   1 |  SORT AGGREGATE        |           |     1 |    11 |            |          |
|*  2 |   HASH JOIN RIGHT SEMI |           | 16000 |   171K|  5836   (3)| 00:01:11 |
|   3 |    INDEX FAST FULL SCAN| SMALL_IDX |   500 |  2000 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | BIG_TAB   |  2302K|    15M|  5805   (2)| 00:01:10 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."OBJECT_ID"="B"."OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      32777  consistent gets
      32768  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

What do we see?  The two plans are identical and both did the exact same amount of work (same number of consistent gets).  Will it make a difference if you us IN or EXISTS?  Likely not.  There was a time that it did (version 8 and earlier I think), but today it's very likely you will end up with the same plan.  If you think it matters, test it!  You might be surprised that it doesn't.