In or inner join vs exists

This article is based on Oracle database.

IN is similiar to Inner join, "exists" is similiar to a loop statement.

generally say, "exists" works better in most of situation since it requires you to specify a join condition, which can invoke an INDEX scan.

But it's not always true. when the result set of subquery is small enough, inner join or IN works better. first of all, subquery can also use index scan as no exception. sencondly, using IN or inner join, you're telling the rule-based optimizer that you want the inner query to drive the outer query. the engine will run out the driving query that returns small result set the first and that will make filtering rows in outer query faster.

which way is better? checking the execution plan will give you final answer.

please note that in Oracle, the query parser will always change subquery to be "select 0" whatever value you specify in the select list(common ones are 0,1,X,null).