Use EXISTS Rather than IN

Use IN to check if a value is contained in a list. EXISTS is different from IN : EXISTS just checks for the existence of rows, whereas IN checks actual values. EXISTS typically offers better performance than IN with subqueries. Therefore you should use EXISTS rather than IN whenever possible.

The following query uses IN (bad since EXISTS would work) to retrieve products that have been purchased:

 -- BAD (uses IN rather than EXISTS)  SELECT product_id, name   FROM products   WHERE product_id IN   (SELECT product_id   FROM purchases);  PRODUCT_ID NAME ---------- -----------------------------  1 Modern Science  2 Chemistry  3 Supernova 

The next query rewrites the previous example to use EXISTS :

 -- GOOD (uses EXISTS rather than IN)  SELECT product_id, name   FROM products outer   WHERE EXISTS   (SELECT 1   FROM purchases inner   WHERE inner.product_id = outer.product_id);  PRODUCT_ID NAME ---------- -----------------------------  1 Modern Science  2 Chemistry  3 Supernova