UNION ALL - not necessary?


#1

Why should we use UNION ALL here if the following code achieves the same thing?

My solution:

SELECT id, sale_price FROM order_items
 UNION 
 SELECT id, sale_price FROM order_items_historic;

Official solution (isn't it unnecessarily complicated?):

SELECT id, AVG(a.sale_price) FROM (
  SELECT id, sale_price FROM order_items
  UNION ALL
  SELECT id, sale_price FROM order_items_historic) as a
  GROUP BY 1;

#2

The difference between UNION and UNION ALL is that UNION removes duplicates from the selection, whereas UNION ALL selects the lot of them.

Regards,
Barry Allen

P.S. Your solution probably works since there are no duplicates, in which case the two queries and functionally identical


#3