You can suppress the display of duplicate rows using DISTINCT; you use EXISTS to check for the existence of rows returned by a subquery. Whenever possible, you should use EXISTS rather than DISTINCT because DISTINCT sorts the retrieved rows before suppressing the duplicate rows.
The following query uses DISTINCT (bad since EXISTS would work) to retrieve products that have been purchased:
-- BAD (uses DISTINCT when EXISTS would work) SELECT DISTINCT pr.product_id, pr.name FROM products pr, purchases pu WHERE pr.product_id = pu.product_id; PRODUCT_ID NAME ---------- ----------------------------- 1 Modern Science 2 Chemistry 3 Supernova
The next query rewrites the previous example to use EXISTS rather than DISTINCT :
-- GOOD (uses EXISTS rather than DISTINCT) 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