SQL: Why UNION Drops Rows From a Total

The symptom

You check Garden Shop's revenue by adding up the lines of shipped orders and the lines of every other order, stacked with UNION. The shop has 48 order lines worth 1,129.00 in all. Your query counts 26 lines and 637.50, and nothing errors.

Why it happens

UNION stacks two results and then removes duplicate rows, the way DISTINCT does. A duplicate means every selected column is equal, and here the only column is the amount. Five different lines are worth 24.00 each, so UNION keeps one of them. It removes repeats inside each half too, not only between the halves.

Rows that look like duplicates in the result aren't duplicates in the shop: they're different lines with the same total. That's why a UNION around amounts, counts, or dates breaks totals.

The fix

Use UNION ALL, which stacks the results and keeps every row:

with line_amounts as (
  select oi.quantity * oi.unit_price as amount
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status = 'shipped'
  union
  select oi.quantity * oi.unit_price
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status <> 'shipped'
)
select count(*) as lines, sum(amount) as revenue
from line_amounts

with line_amounts as (
  select oi.quantity * oi.unit_price as amount
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status = 'shipped'
  union all
  select oi.quantity * oi.unit_price
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status <> 'shipped'
)
select count(*) as lines, sum(amount) as revenue
from line_amounts

That's 48 lines and 1,129.00 again. Choose UNION only when removing repeats is the point, such as a list of every state that has a customer or a supplier.

Keeping the key also works

Selecting the row's id makes every row distinct, so UNION has nothing to remove:

with line_amounts as (
  select oi.order_item_id, oi.quantity * oi.unit_price as amount
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status = 'shipped'
  union
  select oi.order_item_id, oi.quantity * oi.unit_price
  from order_items as oi
  join orders as o on o.order_id = oi.order_id
  where o.status <> 'shipped'
)
select count(*) as lines, sum(amount) as revenue
from line_amounts

It still spends work comparing every row to find repeats that can't exist. UNION ALL says what you mean and skips that step.

Learn more

• Lesson 15.1 · UNION ALL
• Lesson 15.2 · UNION
• Reference · Set operations