Difference between EXISTS and IN in SQL?

exists, sql, sql-in

Solution

The `exists` keyword can be used in that way, but really it's intended as a way to avoid 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 ... )

This is most useful where you have `if` conditional statements, as `exists` can be a lot quicker than `count`.

The `in` is best used where you have a static list to pass:

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

When you have a table in an `in` statement it makes more sense to use a `join`, but mostly it shouldn't matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) `in` queries will always get a nested join plan, while `join` queries will use nested, merge or hash as appropriate. More modern implementations are smarter and can adjust the plan even when `in` is used.

Problem

Original source