Why Your SQL UNION Query Returns Fewer Rows Than Expected

It’s a common (and confusing) situation:
You run two separate SQL queries, each returning a known number of rows. When you combine them using UNION, the final result has fewer rows than the sum of the two queries. At first glance, this feels wrong — but in reality, SQL is doing exactly what it’s designed to do.

Let’s break down why this happens and how to fix it.

---

The Key Behavior of UNION

In SQL, UNION removes duplicate rows by default.

That means when you combine two result sets using UNION, SQL treats the output as a set, not a list. If the same row appears in both queries — where all selected columns match — it will appear only once in the final result.

This behavior is often overlooked and is the root cause of unexpected row counts.

---

A Typical Scenario

Imagine you have:

• Query A returns 1,437,724 rows
• Query B returns 20,537 rows

Naturally, you might expect the combined result to return:

1,437,724 + 20,537 = 1,458,261 rows

But instead, the UNION query returns:

1,455,579 rows

What happened to the missing rows?

The Answer: Overlapping Records

The difference between the expected and actual totals tells the story:

1,458,261 – 1,455,579 = 2,682 rows

Those 2,682 rows exist in both queries. Because UNION eliminates duplicates, they are included only once in the final output.

---

When You Should Use UNION ALL

If your intention is to combine result sets without removing duplicates, you should use:

UNION ALL

Unlike UNION, UNION ALL simply appends the second result set to the first, preserving every row — even if they are identical.

In this scenario, UNION ALL would return the full expected total of 1,458,261 rows.

---

Finding the Overlapping Rows

If you want to see which rows are being deduplicated, you can use:

INTERSECT

This returns only the rows that appear in both result sets:

select …

from first_query

intersect

select …

from second_query;

This is a powerful way to:

• Validate assumptions about uniqueness
• Identify unintended overlap between datasets
• Debug data modeling or filtering logic

---

Best Practices

• Use UNION when:
  • You want a distinct set of rows
  • Duplicate records should be removed intentionally
• Use UNION ALL when:
  • You expect the row counts to add up
  • Duplicate rows are valid or meaningful
  • Performance matters (since UNION ALL avoids the overhead of deduplication)

---

Final Thoughts

Unexpected row counts in UNION queries are almost always explained by duplicate elimination. Once you internalize that UNION implies distinctness, these situations become much easier to diagnose.

If row counts matter — and they usually do — choosing between UNION and UNION ALL is a decision you should make deliberately, not accidentally.