Baldwin Apps

Posted on Jul 4

SQL Pattern Series #11: The Merge Pattern

#sql #database #beginners #tutorial

Combining result sets without accidentally paying the deduplication tax

SQL Pattern Series #11 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

The difference between UNION and UNION ALL
Why UNION removes duplicates
Why UNION ALL is often faster
When to keep duplicates and when to remove them

Sometimes you need to combine rows from multiple queries.

For example:

Show active customers and archived customers together.

or:

Combine current orders with historical orders.

That is where the Merge Pattern becomes useful.

The Problem

Imagine two result sets:

Set A
-----
1
2
3

Set B
-----
3
4
5

You want one combined result.

But there is an important question:

Should duplicate rows be removed?

That question determines whether you should use UNION or UNION ALL.

The Merge Pattern

The Merge Pattern combines multiple result sets into one result.

The two most common tools are:

UNION

and:

UNION ALL

They look similar.

But they do different work.

UNION Removes Duplicates

UNION combines result sets and removes duplicate rows.

SELECT value
FROM SetA

UNION

SELECT value
FROM SetB;

Result:

value
-----
1
2
3
4
5

The duplicate 3 appears only once.

That can be exactly what you want.

But removing duplicates requires extra work.

UNION ALL Keeps Everything

UNION ALL combines result sets and keeps all rows.

SELECT value
FROM SetA

UNION ALL

SELECT value
FROM SetB;

Result:

value
-----
1
2
3
3
4
5

The duplicate 3 remains.

This is often faster because the database does not need to deduplicate the result.

Why This Pattern Matters

Many developers use UNION by default.

But that default can hide two problems.

First, it may remove rows you actually needed.

Second, it may require unnecessary sorting or deduplication work.

If duplicates are valid, UNION ALL is often the better choice.

The key question is:

Do I need a distinct combined result?

or:

Do I need to preserve every row?

A Practical Example

Suppose you have current and archived orders:

SELECT OrderID, CustomerID, OrderDate
FROM CurrentOrders

UNION ALL

SELECT OrderID, CustomerID, OrderDate
FROM ArchivedOrders;

If the tables represent separate time ranges, duplicates may not be a concern.

In that case, UNION ALL keeps all rows and avoids unnecessary deduplication.

But if the two sources may overlap, and duplicate rows should be removed, then UNION may be appropriate.

Column Rules

Both sides of a UNION or UNION ALL must return compatible columns.

For example:

SELECT CustomerID, CustomerName
FROM ActiveCustomers

UNION ALL

SELECT CustomerID, CustomerName
FROM ArchivedCustomers;

The number of columns should match.

The column positions should represent the same meaning.

The data types should be compatible.

SQL combines by column position, not by column name.

A Note on Performance

UNION often requires extra work because the database must identify duplicate rows.

That may involve sorting, hashing, or other internal operations.

UNION ALL avoids that deduplication step.

So when duplicates are acceptable or impossible, UNION ALL is usually the better default.

As always, validate performance with real execution plans and real workloads.

When I Reach for This Pattern

I typically use the Merge Pattern when:

combining current and historical records
merging active and archived data
stacking results from similar queries
building reporting datasets
combining data from multiple sources

I reach for UNION when:

duplicates must be removed
the final result should be distinct
overlapping sources are possible

I reach for UNION ALL when:

duplicates are valid
all rows should be preserved
the sources do not overlap
performance matters and deduplication is unnecessary

Key Takeaway

UNION and UNION ALL both merge result sets.

But they do not mean the same thing.

UNION says:

Combine the rows and remove duplicates.

UNION ALL says:

Combine the rows and keep everything.

Do not pay the deduplication tax unless you need to.