Difference between EXISTS and IN in SQL?

Question

What is the difference between the `EXISTS` and `IN` clause in SQL? When should we use `EXISTS`, and when should we use `IN`?

Answer 1

EXISTS is much faster than IN , when the sub-query results is very large,the EXISTS operator provides a better performance. IN is faster than EXISTS , when the sub-query results is very small. The Exists keyword evaluates true or false, but IN keyword compare all value in the corresponding sub query column.

Answer 2

Use IN when you want to include a list of values in a WHERE clause. For example:

SELECT * FROM Customers WHERE State IN ('IL', 'WI');

This is the same as writing

SELECT * FROM Customers WHERE State = 'IL' OR State = 'WI';

EXISTS returns either true or false, so you can use it to test for the presence of certain records. For example:

The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

SELECT SupplierName

FROM Suppliers

WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.SupplierID AND Price < 20);