SQL Duplicaten verwijderen: uitgebreide methoden en best practices

Dubbele records zijn een veelvoorkomend probleem dat de datakwaliteit en databaseprestaties kan ondermijnen. Duplicaten verwijderen is essentieel om de nauwkeurigheid van data te behouden, opslag te optimaliseren en de queryprestaties te verbeteren. In dit artikel verkennen we verschillende technieken om dubbele rijen in SQL te verwijderen, afgestemd op uiteenlopende use-cases en databasebeheersystemen.

Als je net begint, raad ik je sterk aan om DataCamp’s cursussen Introduction to SQL en Learn SQL te volgen om basiskennis op te doen over het extraheren en analyseren van data met SQL. Ook vind ik de SQL Basics Cheat Sheet, die je kunt downloaden, een handige referentie omdat daarin alle meest gebruikte SQL-functies staan.

TL;DR

• Gebruik SELECT DISTINCT of GROUP BY om unieke rijen op te halen zonder de tabel te wijzigen
• Gebruik ROW_NUMBER() met een CTE en DELETE voor precieze controle over welke duplicaten je permanent verwijdert
• Gebruik DELETE met een subquery (NOT IN / MIN()) voor een eenvoudige deduplicatieaanpak in elke DBMS
• Gebruik voor grote datasets tijdelijke tabellen om het verwijderen van duplicaten veilig in batches te verwerken
• Voorkom duplicaten proactief met primaire sleutels, unieke constraints en correcte databasenaamnormalisatie

Duplicate rijen in SQL begrijpen

Dubbele rijen in SQL zijn records in een tabel met identieke waarden over alle of geselecteerde kolommen. Veelvoorkomende oorzaken van dubbele rijen in SQL zijn onder meer:

• Ontbrekende primaire sleutels: Als tabellen geen gedefinieerde primaire sleutel of unieke constraint hebben, is er geen mechanisme om het invoegen van dubbele data te voorkomen. Dit kan gebeuren wanneer een tabel niet genormaliseerd is en/of er transitieve afhankelijkheidsproblemen zijn.
• Problemen bij dataintegratie: Bij het samenvoegen van datasets uit verschillende bronnen kunnen onjuiste joins of inconsistenties in dataformaten per ongeluk duplicaten introduceren.
• Fouten bij handmatige invoer: Menselijke fouten, zoals hetzelfde record meerdere keren invoeren, zijn een andere veelvoorkomende oorzaak van dubbele rijen.

In de rest van het artikel bekijken we hoe je duplicaten in SQL kunt verwijderen, en we verdelen het artikel in twee blokken. In het eerste deel behandelen we hoe je duplicaten verwijdert in de data die je ophaalt voor een rapport of dashboard; in het tweede deel kijken we naar het verwijderen van duplicaten in de database.

Hoe dubbele rijen identificeren

Identificeer eerst welke rijen dubbel zijn voordat je ze verwijdert. Gebruik GROUP BY met HAVING COUNT(*) > 1 om rijen te vinden die meer dan eens voorkomen:

SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

Deze query geeft elke Name terug die meer dan eens voorkomt, samen met het aantal keer dat dat gebeurt. Je kunt dit uitbreiden naar meerdere kolommen door ze toe te voegen aan zowel de SELECT- als de GROUP BY-clausule.

Om alle rijen te zien met een rang die hun positie binnen elke duplicaatgroep aangeeft, gebruik je ROW_NUMBER():

SELECT ID, Name,
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS row_num
FROM customers;

Rijen waar row_num > 1 zijn duplicaten. Kies na identificatie de geschikte verwijdermethode in de onderstaande secties.

Methoden om duplicaten te verwijderen in de data die je ophaalt

Er zijn verschillende methoden om duplicaten te verwijderen tijdens het ophalen van records in SQL. Elke methode hangt af van het DBMS, zoals SQL Server, MySQL en PostgreSQL. In deze sectie bekijken we de methoden om duplicaten te verwijderen en lichten we eventuele bijzondere aandachtspunten per database uit. Onthoud: deze methoden filteren de data en geven unieke records terug; ze wijzigen de onderliggende tabel niet.

DISTINCT gebruiken

Het sleutelwoord DISTINCT gebruik je in een SELECT-statement om unieke rijen op te halen. De syntaxis van DISTINCT om duplicaten te verwijderen is vergelijkbaar voor MySQL-, PostgreSQL- en SQL Server-databases. De onderstaande query haalt unieke klantnamen op uit de tabel customers.

SELECT DISTINCT Name 
FROM customers;

GROUP BY gebruiken met aggregatiefuncties

De GROUP BY-clausule, gecombineerd met andere aggregatiefuncties zoals MAX(), MIN() of COUNT(), kan helpen om dubbele records uit tabellen te verwijderen. Met GROUP BY kun je specifieke records selecteren om te behouden, terwijl je andere duplicaten verwijdert.

Stel dat je dubbele klantrecords wilt verwijderen maar degene met de hoogste ID wilt behouden. Dan gebruik je de GROUP BY-clausule met de functie MAX(), zoals hieronder.

-- Verwijder dubbele rijen uit de tabel 'customers' (aliased als c1)
DELETE c1
FROM customers c1
-- Vind de maximale ID voor elke unieke Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Koppel rijen op basis van 'Name' en behoud de rij met de maximale ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;

MySQL en SQL Server ondersteunen bovenstaande syntaxis van GROUP BY met aggregatiefuncties en de JOIN-clausule. 

ROW_NUMBER() gebruiken met Common Table Expressions (CTE)

Met de functie ROW_NUMBER() gecombineerd met een Common Table Expression (CTE) kun je duplicaten filteren op basis van je criteria. De functie ROW_NUMBER kent, in combinatie met PARTITION BY en ORDER BY, een unieke volgnummering toe aan elke rij. Deze methode maakt het mogelijk de rijen uit te filteren die niet aan de vereiste criteria voldoen.

De volgende query identificeert duplicaten en verwijdert alle behalve de eerste voorkoming.

-- Common Table Expression (CTE) om rijen te rangschikken op basis van 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Selecteer alleen de unieke records waar RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;

Deze methode werkt goed voor moderne versies van SQL Server, MySQL en PostgreSQL. Ze is nuttig voor grotere datasets of complexere voorwaarden, omdat je precies kunt aangeven welke duplicaat je wilt behouden.

Duplicaten verwijderen met self-JOIN

Met een self-join kun je een tabel met zichzelf vergelijken, wat handig is voor het identificeren en verwijderen van dubbele rijen door records te vergelijken op basis van specifieke criteria. In het volgende voorbeeld wordt met de self-join de rij met de hogere ID verwijderd, zodat alleen de eerste voorkoming van elke naam overblijft.

-- Verwijder dubbele rijen met self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;

Bovenstaande methode werkt in de belangrijkste databases, waaronder SQL Server, MySQL en PostgreSQL. Bekijk onze cursus Intermediate SQL om meer te leren over het gebruik van aggregatiefuncties en joins om data te filteren.

Methoden om duplicaten te verwijderen in de database

Je kunt dubbele records met queries verwijderen, maar je kunt ze ook permanent verwijderen uit de database. Deze aanpak is belangrijk om de datakwaliteit te borgen. De volgende methoden worden gebruikt om duplicaten uit de database te verwijderen.

ROW_NUMBER() en DELETE gebruiken

De functie ROW_NUMBER() kent een volgnummer toe aan rijen binnen een gedefinieerde partitie. In combinatie met het DELETE-statement helpt dit duplicaten te identificeren door rijen te rangschikken op basis van specifieke kolommen en ongewenste records te verwijderen. Deze methode is toepasbaar op moderne versies van MySQL (vanaf 8.0), PostgreSQL en SQL Server.

Stel dat je dubbele klantrecords wilt verwijderen op basis van de kolom Name, waarbij je alleen de eerste voorkoming (kleinste ID) behoudt:

-- Common Table Expression (CTE) om rijen te rangschikken op basis van 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Verwijder rijen uit de tabel 'customers' waar het rijnummer groter is dan 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);

DELETE gebruiken met subquery

Soms kan een eenvoudige subquery met DELETE duplicaten uit de database verwijderen. Deze methode is geschikt voor oudere versies van MySQL of PostgreSQL waar ROW_NUMBER() mogelijk niet beschikbaar is.

De onderstaande query verwijdert rijen uit de tabel customers waar de ID niet de minimumwaarde is per Name, zodat alleen de rij met de kleinste ID per unieke Name behouden blijft.

-- Verwijder rijen uit de tabel 'customers'
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery om de minimale ID voor elke unieke Name te vinden
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

GROUP BY gebruiken met HAVING-clausule

Wanneer je dubbele waarden in specifieke kolommen wilt controleren, kun je de GROUP BY-clausule combineren met de HAVING-clausule om duplicaten te identificeren. Deze methode maakt het mogelijk specifieke rijen te verwijderen op basis van de opgegeven criteria. Deze methode is compatibel met SQL Server, MySQL en PostgreSQL.

De volgende queries identificeren eerst welke namen meer dan eens voorkomen en verwijderen vervolgens de duplicaten, waarbij de rij met de kleinste ID per Name behouden blijft.

-- Stap 1: Identificeer welke Names duplicaten hebben
SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

-- Stap 2: Verwijder dubbele rijen en behoud de kleinste ID per Name
DELETE FROM customers
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Tijdelijke tabellen gebruiken voor batchverwerking

Tijdelijke tabellen zijn efficiënt voor batchverwerking en het verwijderen van duplicaten in grote datasets. Deze methode is nuttig wanneer losse queries prestatieproblemen kunnen veroorzaken. De volgende query maakt een tijdelijke tabel om de minimale ID per Name op te slaan en verwijdert rijen uit de tabel customers waarvan de ID niet in de tabel temp_customers voorkomt.

-- Maak een tijdelijke tabel met unieke records
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(ID) AS KeepID, Name
FROM customers
GROUP BY Name;

-- Verwijder duplicaten die niet in de tijdelijke tabel staan
DELETE FROM customers
WHERE ID NOT IN (SELECT KeepID FROM temp_customers);

-- Opruimen
DROP TABLE temp_customers;

Bovenstaande syntaxis met CREATE TEMPORARY TABLE wordt alleen ondersteund in MySQL- en PostgreSQL-databases. 

Duplicaten verwijderen in SQL Server

SQL Server biedt verschillende methoden om dubbele records uit de database te verwijderen. Deze methoden omvatten het gebruik van DISTINCT met INTO, ROW_NUMBER() en tijdelijke tabellen.

DISTINCT gebruiken met INTO

Je kunt het sleutelwoord DISTINCT in een SELECT-statement gebruiken om een nieuwe tabel met unieke records te maken. Je kunt de oude tabel verwijderen zodra je hebt gecontroleerd dat de nieuwe tabel de gewenste records bevat. In het volgende voorbeeld wordt de tabel unique_customers gemaakt met unieke records uit de tabel customers.

-- Selecteer unieke rijen uit 'customers' en maak een nieuwe tabel 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Verwijder de oorspronkelijke tabel 'customers' uit de database
DROP TABLE customers;
-- Hernoem de tabel 'unique_customers' naar 'customers' om de oorspronkelijke tabel te vervangen
EXEC sp_rename 'unique_customers', 'customers';

ROW_NUMBER() gebruiken

Je kunt ook de functie ROW_NUMBER() gebruiken om dubbele records uit SQL Server te verwijderen. Stel dat je een tabel Customers hebt met dubbele rijen op basis van de kolom CustomerName, en je wilt alle voorkomingen verwijderen behalve de eerste per duplicaatgroep.

-- Common Table Expression (CTE) om een rijnummer toe te kennen aan elke klant 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Verwijder rijen uit de CTE
DELETE FROM CTE
WHERE RowNum > 1;

Tijdelijke tabel gebruiken

Omdat SQL Server CREATE TEMPORARY TABLE niet ondersteunt, gebruik je SELECT INTO. Tijdelijke tabellen in SQL Server gebruiken # als prefix voor de tabelnaam.

-- Maak een tijdelijke tabel
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Verwijder rijen uit de tabel 'customers' waarvan de ID niet in de tijdelijke tabel staat
DELETE FROM customers
WHERE CustomerID NOT IN (SELECT ID FROM #temp_customers);
-- Verwijder optioneel de tijdelijke tabel na gebruik
DROP TABLE #temp_customers;

Ik raad je aan om onze skill track SQL Server Fundamentals te proberen om je vaardigheden in tabellen joinen en data-analyse te verbeteren. De career track SQL Server Developer geeft je de skills om queries te schrijven, te troubleshooten en te optimaliseren met SQL Server.  

Snelreferentie: SQL-deduplicatiemethoden

De onderstaande tabel vat alle deduplicatiemethoden in dit artikel samen, zodat je snel de juiste aanpak voor jouw situatie kunt kiezen.

Best practices

Dubbele rijen zijn een veelvoorkomend probleem dat de datakwaliteit en databaseprestaties beïnvloedt. Overweeg de volgende best practices om te voorkomen dat dubbele records in je database worden ingevoegd.

• Gebruik primaire sleutels: De kolom primary key zorgt ervoor dat elk record unieke informatie bevat, waardoor dubbele waarden de tabel niet binnenkomen.
• Implementeer unieke constraints: Door unieke constraints op een kolom toe te passen, zorg je dat er geen duplicaten bestaan in niet-primaire kolommen, zoals e-mailadressen of telefoonnummers.
• Goede databaseontwerp en normalisatie: Effectief schemadesign en databasenaamnormalisatie helpen redundantie en dubbele data te verminderen. Deze aanpak zorgt ervoor dat elk record in specifieke tabellen wordt opgeslagen.
• Gebruik unieke indexen: Gebruik unieke indexen om te garanderen dat bepaalde kolomcombinaties uniek zijn zonder volledige tabelbrede constraints voor de hele dataset te vereisen.
• Regelmatige data-audits: Voer regelmatig data-audits uit door queries te draaien om potentiële duplicaten te identificeren op basis van je bedrijfsregels.

Conclusie

Het identificeren en verwijderen van dubbele rijen is belangrijk om de efficiëntie van je database en de nauwkeurigheid van data te behouden. Het is altijd een best practice om een back-up van je data te maken voordat je wijzigingen doorvoert, zodat er geen onbedoeld dataverlies optreedt.

Als je een bekwame data-analist wilt worden, bekijk dan onze career track Associate Data Analyst in SQL om de benodigde skills te leren. De cursus Reporting in SQL is ook geschikt als je wilt leren hoe je professionele dashboards bouwt met SQL. Tot slot raad ik aan om de SQL Associate Certification te behalen om je beheersing van SQL voor data-analyse aan te tonen en op te vallen tussen andere dataprofessionals.