Difference between EXISTS and IN in SQL

Question

What distinguishes the SQL IN clause from the EXISTS clause?

When should we employ IN and when should we employ EXISTS?

Answer 1

Although the exists keyword can be used in such manner, its primary purpose is to prevent counting:

--this statement needs to check the entire table
select count(*) from [table] where ...

--this statement is true as soon as one match is found
exists ( select * from [table] where ... )

When you have if conditional statements, this is particularly helpful because exists can be completed much more quickly than count.

When passing a static list, the in is best used:

 select * from [table]
 where [field] in (1, 2, 3)

It makes more sense to use a join when a table is in an in statement, but generally, it shouldn't matter. In either case, the query optimizer ought to produce the same strategy. In some systems (usually older ones, like Microsoft SQL Server 2000), join queries may employ nested, merge, or hash as necessary, while queries always receive a nested join plan. Modern implementations are more intelligent and can modify the plan even as it is being utilized.