Finding Duplicates

Duplicates sneak into every real database: the same email twice, the same order imported twice. GROUP BY plus HAVING finds them.

Group by the value that should be unique, then keep the groups with more than one row:

SELECT last_name, COUNT(*) AS people
FROM employees
GROUP BY last_name
HAVING COUNT(*) > 1;

Martinez shows up twice: Robert and Nicole.

Group by several columns to check a combination:

SELECT hire_date, department_id, COUNT(*) AS people
FROM employees
GROUP BY hire_date, department_id
HAVING COUNT(*) > 1;

To see the full rows behind a duplicate, feed the result into IN:

SELECT first_name, last_name
FROM employees
WHERE last_name IN (
    SELECT last_name FROM employees
    GROUP BY last_name
    HAVING COUNT(*) > 1
);