ProgramandooIntentándolo

Buscar registros repetidos en SQL

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.


-- 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