SQL UPDATE met JOIN: zo werkt het

Consistente gegevens zijn belangrijk voor betrouwbaarheid en integriteit in relationele databases. Wanneer je bestaande data over gerelateerde tabellen wijzigt, gebruiken we de UPDATE-instructie met de JOIN-clausule om dataconsistentie te bereiken. De bewerking UPDATE met JOIN wordt ondersteund in SQL Server en is handig om nauwkeurige datasynchronisatie tussen tabellen te waarborgen.

De SQL-JOIN-bewerking maakt complexere updates mogelijk dan enkelvoudige tabelbewerkingen via foreign keys die de verschillende tabellen delen. De bewerking UPDATE met JOIN is nuttig voor developers en databasebeheerders omdat het de efficiëntie verbetert en het risico op inconsistenties verlaagt. 

Voordat je begint, raad ik DataCamps cursus Introduction to SQL Server aan om vertrouwd te raken met hoe SQL Server-databases werken. Deze tutorial is specifiek geschreven met SQL Server in gedachten. Bekijk ook onze tutorial, Introduction to SQL Joins, om te leren hoe je JOIN gebruikt wanneer je met meerdere tabellen in SQL werkt.

SQL UPDATE met JOIN gebruiken

In SQL Server kun je met UPDATE en JOIN records in de ene tabel bijwerken op basis van overeenkomende data uit een andere tabel. Deze techniek is vooral handig bij het synchroniseren van gegevens over meerdere tabellen.

SQL UPDATE met INNER JOIN gebruiken

De INNER JOIN in SQL haalt rijen/records op met overeenkomende data in beide tabellen. In combinatie met de UPDATE-instructie zorgt een INNER JOIN ervoor dat alleen die records in de doeltabel worden bijgewerkt die een overeenkomstige match hebben in de gekoppelde tabel.

De typische structuur van UPDATE met INNER JOIN houdt in dat je de doeltabel specificeert die moet worden bijgewerkt en de kolommen definieert die je wilt wijzigen. De structuur omvat ook het gebruik van INNER JOIN om te koppelen met een andere tabel op basis van een gemeenschappelijke kolom, zodat alleen de benodigde records worden bijgewerkt.

INNER JOIN-voorbeeld

Stel dat je een retaildatabase beheert en je de tabel Sales moet bijwerken om wijzigingen in de tabel Customer te synchroniseren. Een klant heeft onlangs zijn of haar voorkeurscontactmethode gewijzigd, en je wilt dat deze wijzigingen in beide tabellen worden doorgevoerd. Je gebruikt UPDATE met INNER JOIN om ervoor te zorgen dat alleen de verkooprecords die betrekking hebben op klanten die hun contactgegevens hebben bijgewerkt, worden aangepast zonder andere rijen te beïnvloeden.

-- Update the Sales table based on the new preferred contact methods in the Customers table
UPDATE s
SET s.ContactMethod = c.PreferredContactMethod
FROM Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
WHERE c.CustomerID IN (3, 7, 9);

Best practices voor UPDATE met INNER JOIN

Houd bij het gebruik van UPDATE met INNER JOIN rekening met de volgende best practices voor efficiënte queries.

• Gebruik indexen op joinkolommen: Zorg dat de kolommen in de JOIN-conditie zijn geïndexeerd om de performance te optimaliseren en de uitvoering te versnellen.

• Beperk de scope van de update: Gebruik de WHERE-clausule om alleen relevante rijen bij te werken en de queryperformance te verbeteren.

SQL UPDATE met LEFT JOIN gebruiken

Een LEFT JOIN wordt gebruikt om alle records uit de linker (eerste) tabel op te halen en de overeenkomende records uit de rechter (tweede) tabel. Een query met LEFT JOIN geeft NULL-resultaten terug vanuit de rechtertabel als er geen matches zijn. In combinatie met de UPDATE-instructie kan de LEFT JOIN records bijwerken, zelfs wanneer er geen overeenkomende data in de tweede tabel is, wat handig is om ontbrekende gegevens op te vullen. Bekijk onze SQL Joins Cheat Sheet als je SQL-joins wilt herhalen.

LEFT JOIN-voorbeeld

Stel dat je een database voor een online winkel beheert. De tabel Customers bevat een lijst met alle klanten, terwijl de tabel Orders details bevat van bestellingen die door sommige van deze klanten zijn geplaatst. Je gebruikt de LEFT JOIN om bestellingsgegevens voor alle klanten op te halen en bij te werken, inclusief klanten die nog niets hebben besteld. Deze methode vult de ontbrekende data voor klanten zonder bestellingen aan met NULL of een opgegeven waarde.

Ons LEFT JOIN-voorbeeld is net iets anders, omdat LEFT JOIN NULL-waarden retourneert als er geen overeenkomende records in de rechtertabel zijn. Daarom is het belangrijk om te weten hoe je met deze NULL-waarden omgaat. Je kunt de NULL-waarden afhandelen door een standaardwaarde in te stellen voor de kolommen die je bijwerkt. Met de functie COALESCE() voeg je in de LEFT JOIN een voorwaarde toe voor een standaardwaarde voor de ontbrekende data. De volgende query laat zien hoe je een standaardwaarde voor NULL-waarden instelt.

-- Update all customers to set default values where OrderStatus and OrderDate are NULL
UPDATE c
SET 
    c.OrderStatus = COALESCE(o.OrderStatus, 'No Orders'),
    c.OrderDate = COALESCE(o.OrderDate, '2023-01-01')   
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Select from the updated Customers table
SELECT * 
FROM Customers;

Je kunt ook de functies COALESCE() of IFNULL() gebruiken om NULL-waarden af te handelen die berekeningen omvatten voordat je de kolommen bijwerkt. Bekijk onze tutorial COALESCE() SQL Function om meer te leren.

Best practices voor UPDATE met LEFT JOIN

Houd bij het gebruik van UPDATE met LEFT JOIN rekening met de volgende best practices voor efficiënte queries.

• Gebruik expliciete kolomverwijzingen: Specificeer expliciet de kolommen die je wilt bijwerken in plaats van SELECT * te gebruiken, om je query duidelijker te maken en onbedoelde updates te voorkomen.

• Gebruik indexen op joinkolommen: Zorg dat de kolommen in de JOIN-conditie zijn geïndexeerd om de performance van de JOIN en de totale uitvoeringstijd te verbeteren.

Performance-overwegingen bij SQL Update met Join

Inzicht in de performance-impact bij het gebruik van UPDATE met JOIN in SQL is belangrijk om ervoor te zorgen dat je queries efficiënt draaien. De performance van deze bewerkingen kan worden beïnvloed door verschillende factoren, waaronder de grootte van de tabellen, het type join en of er passende indexen aanwezig zijn.

Indexen en querysnelheid

Het indexeren van kolommen die betrokken zijn bij de join-conditie is belangrijk om het updateproces te versnellen. Met de juiste indexen vindt de bewerking alleen de overeenkomende rijen tijdens de join, waardoor de tijd voor het scannen van tabellen afneemt.

Impact van grote datasets

Bij het werken met grote datasets kunnen query-uitvoeringen trager zijn door resource-intensieve bewerkingen. Overweeg de volgende best practices om performanceproblemen met grote datasets te beperken. Probeer ook het SQL Server for Database Administrators-skill track als je regelmatig databases ontwerpt of onderhoudt als onderdeel van je werk. 

• Batchupdates: Verdeel updates in kleinere batches om de belasting op de database te verlagen en records efficiënt bij te werken.

• Beperk de scope van de update: Met de WHERE-clausule specificeer je welke rijen je bijwerkt, waardoor de verwerkingstijd afneemt.

Veelgemaakte fouten en hoe je ze voorkomt

Er zijn een paar veelvoorkomende fouten die je kunt tegenkomen bij het gebruik van UPDATE met JOIN in SQL Server. Deze fouten kunnen leiden tot performanceproblemen of het bijwerken van onbedoelde data. Hieronder staan enkele van deze fouten en hoe je ze vermijdt.

Onbedoelde updates

Als je de join-conditie niet correct specificeert, zal UPDATE met JOIN meer records bijwerken dan bedoeld. Deze fout werkt een hele tabel bij in plaats van de vereiste subset. Voorkom dit door altijd een precieze JOIN-conditie te gebruiken om de benodigde rijen bij te werken.

Cartesische producten

Een cartesisch product ontstaat wanneer de join-conditie onjuist is of ontbreekt, waardoor de query elke rij uit de linkertabel aan elke rij uit de rechtertabel koppelt. Dit kan de query vertragen en onnauwkeurige resultaten opleveren. Voorkom dit door de JOIN-conditie altijd correct te definiëren. Zorg er ook voor dat je de juiste kolommen aangeeft waarop je de join toepast.

Alternatieven voor SQL UPDATE met JOIN

Hoewel UPDATE met JOIN een krachtige methode is om records over gerelateerde tabellen bij te werken, zijn er alternatieve methoden om hetzelfde te bereiken. Hieronder staan enkele opties.

Subquery's

Subquery's zijn een belangrijk alternatief voor JOIN wanneer je updates wilt uitvoeren op basis van een eenvoudige voorwaarde. Met subquery's vermijd je meerdere complexe tabellen door de voorwaarde in de subquery te specificeren.

Het volgende voorbeeld laat zien hoe je met een subquery de tabel Sales bijwerkt met de meest recente CustomerStatus uit de tabel Customers.

-- Update the CustomerStatus in the Sales table
UPDATE Sales
-- Set the CustomerStatus in Sales to corresponding value from the Customers table
SET CustomerStatus = (
    -- Fetch CustomerStatus from the Customers table for the matching CustomerID
    SELECT CustomerStatus 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
)
-- Update rows in Sales where a matching CustomerID exists in the Customers table
WHERE EXISTS (
    -- Subquery to check if a matching CustomerID exists in the Customers table
    SELECT 1 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
);

MERGE-instructie

De MERGE-instructie is een veelzijdig SQL-commando waarmee je UPDATE-, INSERT- of DELETE-bewerkingen in één query kunt uitvoeren op basis van een vergelijking van twee tabellen. De MERGE-instructie is nuttig wanneer je data tussen twee tabellen moet synchroniseren, waarbij zowel overeenkomende als niet-overeenkomende rijen worden afgehandeld.

Het gebruik van de MERGE-instructie is handig omdat het een beknopte manier biedt om meerdere bewerkingen in één query uit te voeren. De syntaxis van de MERGE-instructie is als volgt.

-- Merge data from the source table into the target table
MERGE INTO target_table AS target
USING source_table AS source
    -- Define the common key between the source and target tables
    ON target.common_column = source.common_column
-- When a match is found in both tables based on the common key
WHEN MATCHED THEN
    -- Update the target table's column with the corresponding value from the source table
    UPDATE SET target.column = source.column
-- When a row exists in the source table but not in the target table
WHEN NOT MATCHED THEN
    -- Insert the new data into the target table
    INSERT (column1, column2) 
    VALUES (source.column1, source.column2)
-- When a row exists in the target table but not in the source table
WHEN NOT MATCHED BY SOURCE THEN
    -- Delete the row from the target table
    DELETE;

Conclusie

Begrijpen hoe je UPDATE met JOIN in SQL Server gebruikt, is belangrijk voor efficiënte kruistabel-updates in relationele databases. Met deze techniek kun je records in de ene tabel bijwerken op basis van overeenkomende data in een andere tabel, wat zorgt voor consistentie en synchronisatie over gerelateerde tabellen. Door deze technieken en best practices toe te passen, verbeter je je database-operations en zorg je voor nauwkeurig, efficiënt en betrouwbaar databeheer. 

Als je je SQL-vaardigheden wilt verdiepen, raad ik DataCamps SQL Server Fundamentals-skill track aan om je data-analysisvaardigheden te verbeteren. De cursus Writing Functions and Stored Procedures in SQL Server helpt je ook bij het leren van efficiënte datamanipulatie in SQL Server. Bereid je je voor op een SQL-sollicitatiegesprek, lees dan onze blogpost 20 Top SQL Joins Interview Questions om je voor te bereiden. Wil je je SQL-vaardigheden toepassen in een carrière als data engineer, overweeg dan ook onze Data Engineer in Python-carrièretrack, inclusief certificering aan het einde.