How do you find duplicate records in a table?

🧾 What Are Duplicate Records?

Duplicate records are rows that have the same values in one or more columns. For example, if two users have the same email address, they might be duplicates.

Example

Suppose you have a table employees with columns id, name, and email, and you want to find duplicate email values.

Table: employees

Query to Find Duplicate Emails

SELECT email, COUNT(*) AS count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

Output

This shows alice@email.com and bob@email.com are duplicates.

✅ Step 1: Create the employees table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

✅ Step 2: Insert the sample data

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

✅ Step 3: Query to find duplicate email values

SELECT email, COUNT(*) AS count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;