Cómo encontrar valores duplicados en una tabla de SQL Server

Resumen: en este tutorial, aprenderás a usar la cláusula GROUP BY o la función ROW_NUMBER() para encontrar valores duplicados en una tabla de SQL Server.

Introducción para encontrar los valores duplicados

Técnicamente, utiliza las restricciones UNIQUE para imponer la unicidad de las filas en una o más columnas de una tabla. Sin embargo, a veces puedes encontrar valores duplicados en una tabla debido al diseño deficiente de la base de datos, errores de la aplicación o datos sin limpiar de fuentes externas. En este tutorial veremos cómo identificar estos valores duplicados de manera efectiva.

Para encontrar los valores duplicados en una tabla, sigue estos pasos:

• Primero, define los criterios para los valores duplicados: valores en una sola columna o varias columnas.
• En segundo lugar, escribe una consulta para buscar duplicados.

Si también deseas eliminar las filas duplicadas, puede ir al tutorial de eliminación de duplicados de una tabla.

Configuración de una tabla de muestra

Primero, crea una nueva tabla llamada t1 que contenga tres columnas id, a y b.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    id INT IDENTITY(1, 1),
    a  INT,
    b  INT,
    PRIMARY KEY(id)
);

Luego, inserte algunas filas en la tabla t1:

INSERT INTO
    t1(a,b)
VALUES
    (1,1),
    (1,2),
    (1,3),
    (2,1),
    (1,2),
    (1,3),
    (2,1),
    (2,2);

La tabla t1 contiene las siguientes filas duplicadas:

(1,2)
(2,1)
(1,3)

Ahora el objetivo es escribir una consulta para encontrar las filas duplicadas anteriores.

Usando la cláusula GROUP BY para encontrar valores duplicados en una tabla

Esta declaración usa la cláusula GROUP BY, HAVING y COUNT para encontrar las filas duplicadas en las columnas a y b de la tabla t1:

SELECT
    a,
    b,
    COUNT(*) occurrences
FROM t1
GROUP BY
    a,
    b
HAVING
    COUNT(*) > 1;

Resultado:

Cómo funciona:

• Primero, la cláusula GROUP BY agrupa las filas en grupos por valores iguales en las columnas a y b.
• En segundo lugar, la función COUNT() devuelve el número de ocurrencias de cada grupo (a,b).
• Tercero, la cláusula HAVING mantiene solo grupos duplicados, que son grupos que tienen más de una aparición u ocurrencias.

Para devolver la fila completa por cada fila duplicada, une el resultado de la consulta anterior con la tabla t1 usando una expresión de tabla común (CTE):

WITH cte AS (
    SELECT
        a,
        b,
        COUNT(*) occurrences
    FROM t1
    GROUP BY
        a,
        b
    HAVING
        COUNT(*) > 1
)
SELECT
    t1.id,
    t1.a,
    t1.b
FROM t1
    INNER JOIN cte ON
        cte.a = t1.a AND
        cte.b = t1.b
ORDER BY
    t1.a,
    t1.b;

Resultado:

Generalmente, la consulta para encontrar los valores duplicados en una columna usando la cláusula GROUP BY es la siguiente:

SELECT
    col,
    COUNT(col)
FROM
    table_name
GROUP BY
    col
HAVING
    COUNT(col) > 1;

La consulta para encontrar los valores duplicados en varias columnas usando la cláusula GROUP BY:

SELECT
    col1,col2,...
    COUNT(*)
FROM
    table_name
GROUP BY
    col1,col2,...
HAVING
    COUNT(*) > 1;

Usando la función ROW_NUMBER() para encontrar duplicados en una tabla

La siguiente declaración usa la función ROW_NUMBER() para encontrar filas duplicadas basadas en las columnas a y b:

WITH cte AS (
    SELECT
        a,
        b,
        ROW_NUMBER() OVER (
            PARTITION BY a,b
            ORDER BY a,b) rownum
    FROM
        t1
)
SELECT
  *
FROM
    cte
WHERE
    rownum > 1;

Resultado:

Cómo funciona:

Primero, ROW_NUMBER() distribuye filas de la tabla t1 en particiones por valores en las columnas a y b. Las filas duplicadas tendrán valores repetidos en las columnas a y b, pero diferentes números de fila, como se muestra en la siguiente tabla:

En segundo lugar, la consulta externa elimina la primera fila de cada grupo.

Generalmente, esta declaración usa la función ROW_NUMBER() para encontrar los valores duplicados en una columna de una tabla:

WITH cte AS (
    SELECT
        col,
        ROW_NUMBER() OVER (
            PARTITION BY col
            ORDER BY col) row_num
    FROM
        t1
)
SELECT * FROM cte
WHERE row_num > 1;

En este tutorial, aprendiste a usar la cláusula GROUP BY o la función ROW_NUMBER() para encontrar valores duplicados en SQL Server.