Curious to see what other’s thoughts are on this. It seems like it wouldn’t be best practice to create another sub-query to get the proper answer. Would the better query look more like this?
Create the union as a temporary table, with just the sales_price column, don’t use ID. Then AVG up sales_price to get the answer.
SELECT AVG(sale_price) AS avg_price FROM (SELECT sale_price FROM order_items UNION ALL SELECT sale_price FROM order_items_historic);