UNION vs UNION ALL in SQL: when duplicates are real

Why the usual answer is wrong

SELECT sale_date, SUM(amount) AS revenue
FROM (
  SELECT sale_date, amount FROM online_sales
  UNION
  SELECT sale_date, amount FROM store_sales
)
GROUP BY sale_date;

On 1 March both shops sold 100. UNION sees two identical rows and keeps one, so 1 March shows 100 instead of 200.

The answer

SELECT sale_date, SUM(amount) AS revenue
FROM (
  SELECT sale_date, amount FROM online_sales
  UNION ALL
  SELECT sale_date, amount FROM store_sales
)
GROUP BY sale_date;

UNION ALL keeps every row. Use it whenever the rows are separate events that only happen to look alike.

UNION (without ALL) is for lists where a repeat means the same thing, like a combined list of email addresses. It's also slower, because it has to find duplicates.