Oracle Database 10g SQL (Osborne ORACLE Press Series)

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