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 it doesn't really matter. The query optimiser will return the same plan either way.
EXISTS will tell you whether a query returned any results. eg:
SELECT * FROM Orders o WHERE EXISTS ( SELECT * FROM Products p where p.ProductNumber = o.ProductNumber)
IN is used to compare one value to several, and can use literal values, like this:
SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100)
You can also use query results with the IN clause, like this:
SELECT * FROM Orders WHERE ProductNumber IN ( SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0)
EXISTS is much faster than IN (when the sub-query results is very large)
IN is faster than EXISTS (when the sub-query results is very small)
