Use UNION ALL Rather than UNION


You use UNION ALL to get all the rows retrieved by two queries, including duplicate rows; you use UNION to get all non-duplicate rows retrieved by the queries. Because UNION removes duplicate rows (which takes some time to do), use UNION ALL whenever possible.

The following query uses UNION (bad since UNION ALL would work) to get the rows from the products and more_products tables; notice all non-duplicate rows from products and more_products are retrieved:

 -- BAD (uses UNION rather than UNION ALL)  SELECT product_id, product_type_id, name   FROM products   UNION   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  3 Supernova  4 2 Lunar Landing  4 2 Tank War  5 2 Submarine  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 

The next query rewrites the previous example to use UNION ALL . Notice all the rows from products and more_products are retrieved, including duplicates:

 -- GOOD (uses UNION ALL rather than UNION)  SELECT product_id, product_type_id, name   FROM products   UNION ALL   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  4 2 Tank War  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line  1 1 Modern Science  2 1 Chemistry  3 Supernova  4 2 Lunar Landing  5 2 Submarine 



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