CTE in SQL: Een Complete Gids met Voorbeelden

Hoe maak je een SQL CTE

Wanneer je een CTE maakt, gebruiken we het WITH trefwoord om de CTE-definitie te starten. De algemene syntaxis van een CTE is als volgt:

WITH cte_name (column1, column2, ...)
AS (
    -- Query die de CTE definieert
    SELECT ...
    FROM ...
    WHERE ...
)
-- Hoofdquery
SELECT ...
FROM cte_name;

Waar:

• WITH: Initieert de CTE-definitie en geeft aan dat de volgende naam een tijdelijke resultaatset vertegenwoordigt.

• cte_name: De naam wordt toegewezen aan de CTE om ernaar te verwijzen in de hoofdquery.

• Optionele kolommenlijst (kolom1, kolom2, …): Specificeert kolomnamen voor de resultaatset van de CTE. Dit is handig wanneer kolomnamen moeten worden aangepast.

• Query die de CTE definieert: De binnenste query die gegevens selecteert en het tijdelijke resultaat vormgeeft.

• Hoofdquery: Verwijst naar de CTE met zijn naam, waarbij het als een tabel wordt gebruikt.

Laten we eens kijken naar het volgende voorbeeld van het maken van een CTE met behulp van een gelaagde aanpak. Laten we aannemen dat we een tabel Werknemers hebben en een CTE willen maken die werknemers selecteert die een salaris verdienen boven de $50.000.

Stap 1: Schrijf de basisquery

We beginnen met het schrijven van de basis SELECT-query:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Stap 2: Wikkel de query in met het TREFWOORD WITH om een CTE te maken

Gebruik het trefwoord WITH om de CTE een naam te geven.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Stap 3: Gebruik de CTE in de hoofdquery

Verwijs ten slotte in een SELECT-verklaring naar de CTE door de hierboven gedefinieerde CTE-naam aan te roepen.

-- Definieer een Common Table Expression (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Gebruik de CTE om werknemers met een hoog inkomen te selecteren
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Om de bovenstaande stappen samen te vatten, hebben we het trefwoord WITH gebruikt om de CTE met de naam HighEarningEmployees te definiëren. De binnenste query werd gebruikt om de tijdelijke gegevensset te genereren. De hoofdquery verwijst naar HighEarningEmployees om de gespecificeerde kolommen WerknemerID, Voornaam en Achternaam weer te geven.

Waarom SQL CTE’s handig zijn

Vanuit het bovenstaande voorbeeld vraag je je misschien af waarom we CTE’s gebruiken wanneer zelfs eenvoudige queries dezelfde resultaten opleveren. Hier zijn de redenen:

Vereenvoudig complexe queries

CTE’s breken complexe SQL-statements af in kleinere, meer beheersbare delen, waardoor de code gemakkelijker te lezen, schrijven en onderhouden is. 

Stel dat we drie tabellen hebben: Orders, Customers en Products. We willen de totale omzet vinden die door elke klant is gegenereerd die in 2024 heeft gekocht. Wanneer we de query schrijven zonder CTE te gebruiken, ziet het er rommelig uit en is het moeilijk te lezen en te begrijpen.

-- Selecteer klantnamen en totale omzet van hun orders
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join om klant- en productentabel te krijgen
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Door een CTE te gebruiken, kunnen we de logica scheiden in een meer leesbaar formaat:

-- Definieer de CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Hoofdquery
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Herbruikbaarheid van code

CTE’s helpen duplicatie te vermijden door hetzelfde resultaatset opnieuw te gebruiken in verschillende delen van een query. Als meerdere berekeningen of bewerkingen zijn gebaseerd op dezelfde dataset, kunt u deze eenmaal definiëren in een CTE en ernaar verwijzen indien nodig.

Stel dat we het gemiddelde en de totale verkoop moeten berekenen voor elke productcategorie in een e-commerce database. We kunnen een CTE gebruiken om de berekeningen eenmaal te definiëren en ze opnieuw te gebruiken in daaropvolgende query’s.

-- Definieer een CTE om totale en gemiddelde verkoop voor elke categorie te berekenen
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Selecteer categorie, totale verkoop en gemiddelde verkoop uit de CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Andere toepassingen

Naast het vereenvoudigen van query’s en het hergebruiken van code, hebben CTE’s ook andere toepassingen.Ik kan niet alle mogelijke toepassingen van CTE’s in detail behandelen. Onze Data Manipulatie in SQL cursus is een geweldige optie als je wilt blijven oefenen. Ik zal echter hier enkele van de belangrijkste andere redenen documenteren:

• Zoekopdrachtorganisatie en leesbaarheid: CTE’s verbeteren de leesbaarheid van SQL-code door zoekopdrachten op te delen in logische, opeenvolgende stappen. Elke stap in het zoekproces kan worden vertegenwoordigd door zijn eigen CTE, waardoor de hele zoekopdracht gemakkelijker te volgen is.
• Hiërarchische gegevenstraversie: CTE’s kunnen helpen bij het navigeren door hiërarchische relaties, zoals organisatiestructuren, ouder-kindrelaties, of elk gegevensmodel dat geneste niveaus bevat. Recursieve CTE’s zijn handig voor het opvragen van hiërarchische gegevens omdat ze u in staat stellen niveaus iteratief te doorlopen.
• Meerlaagse aggregaties: CTE’s kunnen helpen bij het uitvoeren van aggregaties op meerdere niveaus, zoals het berekenen van verkoopcijfers op verschillende granulariteiten (bijv. per maand, kwartaal en jaar). Het gebruik van CTE’s om deze aggregatiestappen te scheiden zorgt ervoor dat elk niveau onafhankelijk en logisch wordt berekend.
• Gegevens combineren vanuit meerdere tabellen: Meerdere CTE’s kunnen worden gebruikt om gegevens van verschillende tabellen te combineren, waardoor de uiteindelijke combinatiestap gestructureerder wordt. Deze aanpak vereenvoudigt complexe joins en zorgt ervoor dat de brongegevens logisch zijn georganiseerd voor een betere leesbaarheid.

Geavanceerde SQL CTE-technieken

CTE’s ondersteunen geavanceerde SQL-technieken, waardoor ze veelzijdig en nuttig zijn voor verschillende gebruiksscenario’s. Hier zijn enkele geavanceerde toepassingen van CTE’s.

Meerdere CTE’s in één query

U kunt meerdere CTE’s (Common Table Expressions) definiëren in één query, wat complexe transformaties en berekeningen mogelijk maakt. Deze methode is handig wanneer een probleem meerdere stadia van gegevensverwerking vereist, waarbij elke CTE een afzonderlijk stadium vertegenwoordigt.

Stel dat we verkoopgegevens hebben in een tabel genaamd Verkoop en we willen de totale verkoop voor elk product berekenen, producten identificeren met bovengemiddelde totale verkoop en deze producten rangschikken op basis van hun totale verkoop.

WITH ProductSales AS (
    -- Stap 1: Bereken totale verkoop voor elk product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Stap 2: Bereken het gemiddelde van de totale verkoop over alle producten
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Stap 3: Filter producten met bovengemiddelde totale verkoop
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Stap 4: rangschik de producten met hoge verkoopcijfers
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

In het bovenstaande voorbeeld;

• De eerste CTE (ProductVerkoop) berekent de totale verkoop per product.

• De tweede CTE (AverageSales) berekent de gemiddelde totale verkoop over alle producten.

• De derde CTE (HighSalesProducts) filtert producten waarvan de totale verkoop hoger is dan het gemiddelde.

• De laatste query rangschikt deze producten op basis van hun totale verkoop.

Gemeenschappelijke Tabel Expressies in UPDATE, DELETE en MERGE Statements

Wanneer opgenomen in UPDATE, DELETE en MERGE operaties, kunnen CTEs gegevensmanipulatietaken vereenvoudigen, vooral bij het omgaan met complexe filters of hiërarchische gegevens.

Het gebruik van CTE met een UPDATE statement

Stel dat we een Werknemers tabel hebben met een WerknemerSalaris kolom. We willen een salarisverhoging van 10% geven aan alle werknemers die langer dan 5 jaar voor het bedrijf hebben gewerkt.

-- Definieer een CTE om werknemers te vinden die meer dan 5 jaar geleden zijn aangenomen
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Update salarissen met 10% voor langdurige werknemers geïdentificeerd in de CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

De CTE LangdurigeWerknemers identificeert werknemers die meer dan vijf jaar hebben gewerkt. Het UPDATE statement gebruikt deze CTE om salarissen selectief te verhogen.

Het gebruik van CTE met een DELETE-opdracht

Stel dat we een tabel hebben met de naam Producten en alle producten willen verwijderen die in de afgelopen 2 jaar niet zijn verkocht. We kunnen een CTE gebruiken om de producten te filteren:

-- Definieer een CTE om producten te identificeren die niet in de afgelopen 2 jaar zijn verkocht
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Gebruik DATEADD om producten te vinden met een LastSoldDate van meer dan 2 jaar geleden
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Verwijder producten die als oud zijn geïdentificeerd uit de hoofdtabel
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

De CTE OudeProducten identificeert producten die niet in de afgelopen twee jaar zijn verkocht, en vervolgens gebruikt de DELETE-opdracht deze CTE om die producten te verwijderen.

Het gebruik van CTE met een MERGE-opdracht

De MERGE-opdracht in SQL maakt conditionele updates, invoegingen of verwijderingen in een doeltabel mogelijk op basis van gegevens in een brontabel. In het volgende voorbeeld combineert de CTE GecombineerdeVoorraad nieuwe en bestaande voorraadgegevens. De MERGE-opdracht werkt vervolgens de hoeveelheden bij voor bestaande producten of voegt nieuwe producten in op basis van de CTE-gegevens.

-- CTE om nieuwe en bestaande voorraadgegevens samen te voegen
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Gebruik LEFT JOIN om alle nieuwe gegevens op te nemen, zelfs als ze niet in de huidige voorraad staan
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Voeg de voorbereide gegevens samen met de Inventaris tabel
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Werk bestaande producten bij met nieuwe hoeveelheden
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Voeg nieuwe producten in als ze niet in de voorraad staan
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Recursieve Common Table Expressions (CTE’s)

Recursieve CTE’s helpen bij het uitvoeren van geavanceerde en herhaalde bewerkingen.

Introductie tot recursieve CTE’s

Recursieve CTE’s zijn een speciaal type CTE dat naar zichzelf verwijst binnen de definitie, waardoor de query herhaalde bewerkingen kan uitvoeren. Dit maakt ze ideaal voor het werken met hiërarchische of boomgestructureerde gegevens, zoals organisatiediagrammen, mappenstructuren of productassemblages. De recursieve CTE verwerkt gegevens iteratief en geeft resultaten stap voor stap terug totdat aan een stopvoorwaarde is voldaan.

Anker- en recursieve leden

Een recursieve CTE bestaat uit twee hoofdonderdelen:

• Ankerlid: Het deel dat de basisquery definieert die de recursie start.
• Recursief element: Het deel dat verwijst naar de CTE zelf, waardoor het “recursieve” bewerkingen kan uitvoeren.

Stel dat we een Werknemers tabel hebben, waar elke rij een WerknemerID, WerknemerNaam en ManagerID bevat. Als we alle directe en indirecte rapporten voor een specifieke manager willen vinden, beginnen we met het ankerlid dat de manager op het hoogste niveau identificeert. Het ankerlid begint met de werknemer met WerknemerID = 1.

Het recursieve lid vindt werknemers waarvan de ManagerID overeenkomt met de WerknemerID van de vorige iteratie. Elke iteratie haalt het volgende niveau van de hiërarchie op.

WITH EmployeeHierarchy AS (
    -- Ankerlid: selecteer de manager op het hoogste niveau
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Beginnend met de manager op het hoogste niveau
    UNION ALL
    -- Recursief lid: vind werknemers die rapporteren aan de huidige managers
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Mogelijke problemen of beperkingen van CTE’s in SQL

Het is belangrijk om de functies en beperkingen van CTE’s te begrijpen om logische en leesbare queries te schrijven. Laten we eens kijken naar enkele beperkingen en mogelijke problemen bij het gebruik van CTE’s in verschillende databases.

Beperkingen van SQL Server en Azure

Er zijn enkele omgevingsspecifieke beperkingen voor SQL CTE’s bij het werken met SQL Server of Azure Synapse Analytics. Deze omvatten het volgende:

• SQL Server: Het standaard maximale recursieniveau voor recursieve CTE’s is 100, wat kan worden aangepast met de OPTION (MAXRECURSION)-hint. Als deze limiet wordt overschreden zonder aanpassing, treedt er een fout op. CTE’s kunnen niet rechtstreeks binnen elkaar genest worden of binnen een andere CTE worden gedefinieerd.

• Azure Synapse Analytics: CTE’s hebben beperkte ondersteuning voor bepaalde SQL-operaties zoals INSERT, UPDATE, DELETE en MERGE. Ook worden recursieve CTE’s niet ondersteund in de Azure Synapse Analytics cloud-omgevingen, wat de mogelijkheid beperkt om bepaalde hiërarchische gegevensbewerkingen uit te voeren.

Als je merkt dat je met SQL Server werkt, weet dan dat DataCamp veel geweldige bronnen heeft om je te helpen. Om te beginnen, raad ik aan om de cursus Introduction to SQL Server van DataCamp te volgen om de basis van SQL Server voor gegevensanalyse onder de knie te krijgen. Je kunt onze carrièretrack SQL Server Developer proberen, die alles behandelt van transacties en foutafhandeling tot tijdreeksanalyse. Onze cursus Hierarchical and Recursive Queries in SQL Server gaat recht op het schrijven van geavanceerde queries in SQL Server in, inclusief methoden met CTE’s.

Andere mogelijke problemen

Hoewel CTE’s handig zijn voor het vereenvoudigen van complexe queries, zijn er enkele veelvoorkomende valkuilen waarvan u op de hoogte moet zijn. Ze omvatten het volgende:

• Oneindige lussen in recursieve CTE’s: Als de beëindigingsvoorwaarde voor een recursieve CTE niet wordt voldaan, kan dit resulteren in een oneindige lus, waardoor de query onbepaald blijft uitvoeren. Om te voorkomen dat de recursieve CTE oneindig wordt uitgevoerd, gebruikt u de hint OPTION (MAXRECURSION N) om het maximale aantal recursieve iteraties te beperken, waarbij N een gespecificeerde limiet is.

• Prestatieoverwegingen: Recursieve CTE’s kunnen resource-intensief worden als de recursiediepte hoog is of grote datasets worden verwerkt. Om de prestaties te optimaliseren, beperk de data die in elke iteratie wordt verwerkt en zorg voor passende filtering om overmatige recursieniveaus te voorkomen.

Wanneer CTE’s te Gebruiken vs. Andere Technieken

Hoewel CTE’s geschikt zijn voor het vereenvoudigen van queries met herhaalde taken, kunnen afgeleide tabellen, views en tijdelijke tabellen ook soortgelijke doelen dienen. De volgende tabel belicht de voordelen en nadelen van elke methode en wanneer elke methode te gebruiken.

Conclusie

Het beheersen van CTE’s vergt oefening, zoals bij alles: ik raad aan om de carrièretrack Associate Data Analyst in SQL van DataCamp uit te proberen om een bekwame data-analist te worden. De cursus Reporting in SQL zal je ook helpen om bedreven te worden in het opbouwen van complexe rapporten en dashboards voor effectieve datavisualisatie. Ten slotte zou je de SQL Associate Certification moeten behalen om je beheersing van SQL aan te tonen bij het oplossen van bedrijfsproblemen en je te onderscheiden van andere professionals.