0
0
SQLquery~5 mins

Finding duplicates efficiently in SQL

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
Finding duplicates helps keep data clean and avoid mistakes by showing repeated information.
When you want to find customers who registered more than once.
When checking if a product appears multiple times in the inventory list.
When cleaning up a mailing list to remove repeated email addresses.
When verifying if any orders have been entered twice by mistake.
Syntax
SQL
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Use GROUP BY to group rows with the same value in the column.
HAVING filters groups that appear more than once, showing duplicates only.
Examples
Finds duplicate emails in the users table.
SQL
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Shows products listed more than once in inventory.
SQL
SELECT product_id, COUNT(*)
FROM inventory
GROUP BY product_id
HAVING COUNT(*) > 1;
Finds orders entered multiple times.
SQL
SELECT order_number, COUNT(*)
FROM orders
GROUP BY order_number
HAVING COUNT(*) > 1;
Sample Program
This creates a customers table, adds some entries including a duplicate email, then finds emails that appear more than once.
SQL
CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  email VARCHAR(50)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com'),
(3, 'Charlie', 'charlie@example.com'),
(4, 'Alice', 'alice@example.com'),
(5, 'Eve', 'eve@example.com');

SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
OutputSuccess
Important Notes
HAVING is like WHERE but works on grouped data.
Counting duplicates helps find errors or repeated entries quickly.
Summary
Use GROUP BY to group rows by the column you want to check.
Use HAVING COUNT(*) > 1 to find values that appear more than once.
This method helps keep your data clean and reliable.