Finding duplicates efficiently in SQL - Step-by-Step Execution

Concept Flow - Finding duplicates efficiently

Start with table data

↓

Group rows by target column(s)

↓

Count rows in each group

↓

Filter groups where count > 1

↓

Return duplicate values and counts

↓

End

This flow groups data by the column(s) to check duplicates, counts each group, then filters to keep only those with more than one entry.

Execution Sample

SQL

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

This query finds duplicate values in 'column_name' by grouping and counting, then selecting groups with more than one row.

Execution Table

Step	Action	Group By Result	Count	Filter Condition	Output
1	Scan all rows in table	N/A	N/A	N/A	All rows read
2	Group rows by column_name	Groups: A, B, C, A, B, A	N/A	N/A	Groups formed
3	Count rows in each group	A, B, C	3, 2, 1	N/A	Counts calculated
4	Filter groups with count > 1	A, B	3, 2	True	Groups A and B kept
5	Return duplicates with counts	A, B	3, 2	N/A	Output rows: (A,3), (B,2)
6	End	N/A	N/A	N/A	Query complete

💡 All groups processed; only those with count > 1 returned as duplicates.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Groups	None	A, B, C, A, B, A	A, B, C	A, B	A, B
Counts	None	N/A	3, 2, 1	3, 2	3, 2
Output	None	None	None	None	(A,3), (B,2)

Key Moments - 3 Insights

Why do we use GROUP BY before HAVING?

GROUP BY collects rows into groups by the column value, so HAVING can filter groups based on aggregate functions like COUNT. Without GROUP BY, HAVING cannot filter duplicates.

Why do we use HAVING COUNT(*) > 1 instead of WHERE?

WHERE filters rows before grouping, but duplicates are identified after grouping. HAVING filters groups based on aggregate results like COUNT, so it's needed here.

What if the column has NULL values? Are they counted as duplicates?

Yes, NULLs are grouped together by GROUP BY and counted. If multiple NULLs exist, they appear as duplicates in the output.

Visual Quiz - 3 Questions

Look at the execution table, what is the count for group 'B' after Step 3?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Count' column in Step 3 row in the execution_table.

At which step are groups with only one row removed?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look at the 'Filter groups with count > 1' action in the execution_table.

If the table had no duplicates, what would the output be after Step 5?

A All groups with count 1

B Empty result set

C All rows from the table

D Error message

💡 Hint

Recall that HAVING COUNT(*) > 1 filters out groups with count 1 or less.

Concept Snapshot

Finding duplicates efficiently:
Use GROUP BY on the column(s) to group rows.
Use COUNT(*) to count rows per group.
Use HAVING COUNT(*) > 1 to keep only duplicates.
Returns duplicate values and their counts.
Works even if NULLs are present.

Full Transcript

To find duplicates efficiently in SQL, we group rows by the column we want to check duplicates in. Then we count how many rows are in each group. Groups with more than one row mean duplicates exist. We use HAVING COUNT(*) > 1 to filter these groups. The query returns the duplicate values and how many times they appear. This method works well even if the column has NULL values, as NULLs are grouped together. The process starts by scanning all rows, grouping them, counting each group, filtering groups with count greater than one, and finally returning those duplicates.