La necesidad de contar o buscar registros repetidos o duplicados en SQL es relativamente frecuente ya sea para eliminarlos o simplemente para conocer el número de veces que se repiten ciertos valores.
Para ver los distintos ejemplos sobre la búsqueda de elementos repetidos vamos a usar una tabla sencilla de usuarios.
-- Tabla de ejemplo
CREATE TABLE usuarios (
id serial,
apellido character varying,
email character varying NOT NULL,
nombre character varying NOT NULL,
telefono character varying,
CONSTRAINT usuarios_pkey PRIMARY KEY (id)
);
-- Insert de usuarios con datos repetidos
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Luis', 'Perez', 'luisperez@gmail.com', '600100100');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Antonio', 'Perez', 'antonioperez@gmail.com', '699230671');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Marcos', 'Fernandez', 'luisperez@gmail.com', '600100100');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Luis', 'Perez', 'luisperez@gmail.com', '699230671');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Pedro', 'Sanchez', 'pedrosanchez@gmail.com', '600100100');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Ana', 'Alvarez', 'anaalvarez@gmail.com', '876123000');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Maria', 'Garcia', 'mariagarcia@gmail.com', '902130823');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Victor', 'Prado', 'victorprado@gmail.com', '654100601');
INSERT INTO usuarios(nombre, apellido, email, telefono) VALUES ('Carlos', 'Sandoval', 'carlossandoval@gmail.com', '600100100');
Buscar repetidos en 1 columna en SQL
Para obtener los registros repetidos la consulta SQL que debemos a hacer es la siguiente:
-- Telefonos repetidos
SELECT telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1;
-- Resultado
-- 699230671
-- 600100100
Es decir, lo que necesitamos es agrupar los registros por el valor para el que queremos buscar los repetidos y con HAVING COUNT(*)>1 obtendremos todos los valores que estén al menos repetidos una vez.
Para saber además de los registros repetidos la cantidad de veces que se repite cada uno simplemente hay que añadir el contador al resultado de la consulta
-- Nº de veces que esta repetido cada telefono
SELECT telefono, count(*) FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1;
-- Resultado
-- telefono count
-- 699230671 2
-- 600100100 4
Y usando una subconsulta con los registros repetidos podemos saber el número de usuarios del ejemplo que tienen un teléfono repetido.
-- Numero de usuarios con telefonos repetidos
SELECT COUNT(*) FROM usuarios u
WHERE telefono IN (
SELECT telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1
);
-- Resultado
-- 6
Del mismo modo que en el ejemplo anterior se puede sacar los datos de los usuarios que tienen el teléfono repetido.
-- Datos de los usuarios con telefonos repetidos
SELECT u.* FROM usuarios u
WHERE telefono IN (
SELECT telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1
);
-- Resultado
-- apellido email nombre telefono id
-- Perez luisperez@gmail.com Luis 600100100 1
-- Perez antonioperez@gmail.com Antonio 699230671 2
-- Fernandez luisperez@gmail.com Marcos 600100100 3
-- Perez luisperez@gmail.com Luis 699230671 4
-- Sanchez pedrosanchez@gmail.com Pedro 600100100 5
-- Sandoval carlossandoval@gmail.com Carlos 600100100 9
Buscar repetidos en varias columnas en SQL
Buscar los registros en los que se repiten al mismo tiempo los valores de varias columnas es igual de simple que buscar los repetidos por un solo valor, la única diferencia es que la agrupación hay que hacerla por todos los campos para los que se quieren comprobar los repetidos.
-- Usuarios con el mismo nombre y apellido
SELECT nombre, apellido, COUNT(*) as num_repeticiones FROM usuarios u
GROUP BY nombre, apellido
HAVING COUNT(*)>1;
-- Resultado
-- nombre apellido num_repeticiones
-- Luis Perez 2
Buscar registros NO repetidos en SQL
¿Y buscar los que no se repiten? Pues del mismo modo que se buscan los repetidos se puede saber los que no se repiten, solo hay que cambiar <1 por =1 para tener los usuarios que se repiten 1 vez, es decir, que no se repiten.
-- Usuarios con telefono unico
SELECT telefono, COUNT(*) as num_repeticiones FROM usuarios u
GROUP BY telefono
HAVING COUNT(*)=1;
-- Resultado
-- telefono num_repeticiones
-- 902130823 1
-- 654100601 1
-- 876123000 1
Buscar registros que se repiten X veces en SQL
Cambiando los valores de comparación del HAVING COUNT(*) se puede obtener el nº de registros que se repiten el número de veces concreto que se quiere conocer, que se repitan más de x veces, menos de x veces, …
-- Nº de registros con telefonos unicos, repetidos 1 vez, 2 veces,...
SELECT
(SELECT COUNT(*) FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=1) AS sin_repetidos) AS sin_repetidos,
(SELECT COUNT(*) FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=2) AS _2_repetidos) AS _2_repetidos,
(SELECT COUNT(*) FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=3) AS _3_repetidos) AS _3_repetidos,
(SELECT COUNT(*) FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=4) AS _4_repetidos) AS _4_repetidos,
(SELECT COUNT(*) FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=5) AS _5_repetidos) AS _5_repetidos;
-- Resultado
-- sin_repetidos _2_repetidos _3_repetidos _4_repetidos _5_repetidos
-- 3 1 0 1 0
-- Nº de registros con telefonos unicos, repetidos 1 vez, 2 veces,...
(SELECT COUNT(*) AS cantidad, 'Sin repetidos' AS num_repetidos FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=1) AS sin_repetidos)
UNION ALL
(SELECT COUNT(*) AS cantidad, '2 repetidos' AS num_repetidos FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=2) AS _2_repetidos)
UNION ALL
(SELECT COUNT(*) AS cantidad, '3 repetidos' AS num_repetidos FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=3) AS _3_repetidos)
UNION ALL
(SELECT COUNT(*) AS cantidad, '4 repetidos' AS num_repetidos FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=4) AS _4_repetidos)
UNION ALL
(SELECT COUNT(*) AS cantidad, '5 repetidos' AS num_repetidos FROM (SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)=5) AS _5_repetidos)
-- Resultado
-- cantidad num_repetidos
-- 3 Sin repetidos
-- 1 2 repetidos
-- 0 3 repetidos
-- 1 4 repetidos
-- 0 5 repetidos
Buscar registros distintos usando DISTINCT en SQL
Con DISTINCT se puede obtener los registros sin duplicados, es decir, se recuperan los registros que no tienen ningún repetido y los que tienen repetidos solo se recuperan 1 única vez.
-- Registros distintos
SELECT DISTINCT telefono FROM usuarios;
-- Resultado
-- 902130823
-- 699230671
-- 654100601
-- 876123000
-- 600100100
Como se puede ver la consulta que muestra los teléfonos sin repetidos solo hay 3 teléfonos y en este caso aparecen 5, los 3 sin repetidos y los 2 que se repiten varias veces.
-- Numero de telefonos distintos
SELECT COUNT(*) FROM (
SELECT DISTINCT telefono FROM usuarios
) telefonos_distintos;
-- Resultado
-- 5
SELECT COUNT(DISTINCT telefono) FROM usuarios;
-- Resultado
-- 5
Borrar registros repetidos en SQL
Para borrar todos los registros repetidos basta con usar como condición de borrado la consulta que nos devuelve los registros repetidos.
-- Borrar todos los repetidos
DELETE FROM usuarios
WHERE telefono IN (SELECT telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1);
Esta consulta es muy simplemente pero es bastante improbable que se quieran borrar todos los repetidos, como mínimo lo normal es que se quiera mantener uno de los valores (el primero, el ultimo, …) y borrar el resto de registros duplicados.
-- Borrar repetidos (Manteniendo el primer registro)
WITH telefono_repetidos AS (
SELECT MIN(id) as id, telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1
)
SELECT * FROM usuarios
WHERE id not IN (
SELECT id FROM telefono_repetidos
) and telefono IN (SELECT telefono FROM telefono_repetidos);
-- Resultado (Usuarios con telefonos repetidos que hay que borrar)
-- apellido email nombre telefono id
-- Fernandez luisperez@gmail.com Marcos 600100100 3
-- Perez luisperez@gmail.com Luis 699230671 4
-- Sanchez pedrosanchez@gmail.com Pedro 600100100 5
-- Sandoval carlossandoval@gmail.com Carlos 600100100 9
Con este ejemplo mantendríamos los registros más bajos con teléfonos repetidos y borramos el resto haciendo la intersección de los usuarios que tienen un teléfono repetido y que no son el usuario con el menor id para ese teléfono.
La misma consulta pero ahora con el delete.
-- Borrar repetidos (Manteniendo el primer registro)
WITH telefono_repetidos AS (
SELECT MIN(id) as id, telefono FROM usuarios
GROUP BY telefono
HAVING COUNT(*)>1
)
DELETE FROM usuarios
WHERE id not IN (
SELECT id FROM telefono_repetidos
) and telefono IN (SELECT telefono FROM telefono_repetidos);
-- Resultado (Tabla despues del borrado)
-- apellido email nombre telefono id
-- Perez luisperez@gmail.com Luis 600100100 1
-- Perez antonioperez@gmail.com Antonio 699230671 2
-- Alvarez anaalvarez@gmail.com Ana 876123000 6
-- Garcia mariagarcia@gmail.com Maria 902130823 7
-- Prado victorprado@gmail.com Victor 654100601 8