Baldwin Apps

Posted on Jul 28

SQL Pattern Series #18: The Duplicate Detection Pattern

#database #tutorial #beginners #sql

Finding values that appear more than once

SQL Pattern Series #18 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:

How to identify duplicate values
Why GROUP BY and HAVING work together
When duplicate detection is useful
Why duplicate data can create reporting problems

Many data problems are obvious.

Missing values.

Incorrect values.

Broken relationships.

Duplicate data is different.

Sometimes everything looks normal until the numbers stop making sense.

That is where the Duplicate Detection Pattern becomes useful.

The Problem

Imagine a table of users:

UserID	Email
1	alice@example.com
2	bob@example.com
3	alice@example.com

At first glance, the table appears fine.

But one email address appears twice.

If email addresses are supposed to be unique, that duplicate may indicate:

an import problem
an application bug
bad validation
data quality issues

The challenge is finding those repeated values.

The Duplicate Detection Pattern

A common solution uses:

GROUP BY

combined with:

HAVING

The pattern looks like this:

SELECT
    column_name,
    COUNT(*) AS DuplicateCount
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

The query groups identical values together and returns only groups that appear more than once.

Example

SELECT
    Email,
    COUNT(*) AS DuplicateCount
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;

This query asks:

Which email addresses occur more than once?

Example Result

Email	DuplicateCount
alice@example.com	2

The result identifies the duplicated value and shows how many times it appears.

Why This Pattern Matters

Duplicate data can quietly distort reports and analytics.

For example:

customer counts become inflated
revenue calculations become inaccurate
dashboards show incorrect totals
automated processes execute multiple times

The earlier duplicates are detected, the easier they are to fix.

GROUP BY vs HAVING

This pattern is a useful reminder of the difference between:

WHERE

and

HAVING

WHERE

Filters rows before grouping.

HAVING

Filters groups after grouping.

For example:

GROUP BY Email
HAVING COUNT(*) > 1

The database must first build the groups before it can determine which groups contain duplicates.

Common Uses

The Duplicate Detection Pattern is frequently used to identify:

Duplicate email addresses

GROUP BY Email
HAVING COUNT(*) > 1

Duplicate usernames

GROUP BY UserName
HAVING COUNT(*) > 1

Duplicate account numbers

GROUP BY AccountNumber
HAVING COUNT(*) > 1

Duplicate product identifiers

GROUP BY ProductCode
HAVING COUNT(*) > 1

Duplicate business keys

GROUP BY BusinessKey
HAVING COUNT(*) > 1

In each case, the goal is the same:

Find values that appear more than once.

Finding the Actual Rows

Sometimes you need more than the duplicated value.

You need the rows themselves.

One common approach is:

SELECT *
FROM Users
WHERE Email IN (
    SELECT Email
    FROM Users
    GROUP BY Email
    HAVING COUNT(*) > 1
);

This returns all rows that participate in a duplicate group.

A Note on Uniqueness Constraints

Ideally, many duplicates should never occur.

Database constraints such as:

UNIQUE

can help prevent duplicate values from being inserted.

However, duplicate detection remains useful when:

auditing existing data
validating imports
cleaning legacy databases
investigating reporting issues

When I Reach for This Pattern

I typically use the Duplicate Detection Pattern when:

validating imported data
troubleshooting reports
checking data quality
preparing data migrations
auditing business keys

Examples include:

duplicate emails
duplicate usernames
duplicate customer records
duplicate account numbers
repeated transaction identifiers

Key Takeaway

Many SQL problems begin with a simple question:

Does this value appear more than once?

The Duplicate Detection Pattern helps answer that question quickly.

Group the values.

Count the occurrences.

Keep the groups greater than one.

Sometimes the problem is not missing data.

Sometimes it is the same data appearing twice.