Use EXISTS Rather than DISTINCT


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 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net