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