Difference Between In and Exists - Part 2

SQL> select * from smalltable1 where data1 in (select data5 from largetable);

100 rows selected.

 

SQL> select * from smalltable1 st where exists (select 1 from largetable lt where st.data1=lt.data5);

100 rows selected.

SQL> select * from largetable lt where data5 in (select data1 from smalltable1 st);

100 rows selected.

SQL> select * from largetable lt where exists (select 1 from smalltable1 st where st.data1=lt.data5);

100 rows selected.

Inference

From the preceding example, it can be inferred that usage of IN and EXISTS provided us with the same results for both Small table to large table and large table to small table.

Scenario 3: Large table to Large table

Now, the last scenario is to compare large table with another large table and observe the results. Let us create another table largetable2 and compare the results.

SQL> select * from largetable where data5 in (select data5 from largetable2);

100000 rows selected.

SQL> select * from largetable lt where exists (select 1 from largetable2 lt2 where lt.data5=lt2.data5);

100000 rows selected.

To Conclude:

In all the three scenarios, except for the negligible difference in statistics, it can be observed that both IN and EXISTS behave equally.