CTE in SQL: Een complete gids met voorbeelden

Hoe maak je een SQL CTE

Bij het maken van een CTE gebruiken we het WITH-keyword om de CTE-definitie te starten. De algemene syntaxis van een CTE is als volgt:

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Waarbij:

• WITH: Start de CTE-definitie.
• cte_name: De naam die aan de CTE wordt gegeven (om later naar te verwijzen).
• Optionele kolomlijst: Specificeert kolomnamen voor de resultatenset van de CTE.
• Hoofdquery: Verwijst naar de CTE bij naam en behandelt deze als een normale tabel.

Laten we naar een voorbeeld kijken. Stel, we hebben een tabel Employees en we willen werknemers selecteren die een salaris boven de $50.000 verdienen.

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 WITH-keyword om een CTE te maken

Gebruik het WITH-keyword 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 tot slot in een SELECT-statement naar de CTE door de hierboven gedefinieerde CTE-naam aan te roepen.

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

Samenvattend hebben we het WITH-keyword gebruikt om de CTE HighEarningEmployees te definiëren. De binnenste query genereerde de tijdelijke dataset. De hoofdquery verwijst naar HighEarningEmployees om de gespecificeerde kolommen EmployeeID, FirstName en LastName weer te geven.

Waarom SQL CTE’s handig zijn

Uit het bovenstaande voorbeeld vraag je je misschien af waarom we CTE’s gebruiken als eenvoudige queries dezelfde resultaten opleveren. De redenen zijn als volgt:

Complexe queries vereenvoudigen

CTE’s breken complexe SQL-statements op in kleinere, beter beheersbare onderdelen, waardoor de code makkelijker te lezen, te schrijven en te onderhouden is. 

Stel, we hebben drie tabellen: Orders, Customers en Products. We willen de totale omzet per klant vinden voor aankopen in 2024. Zonder CTE ziet de query er rommelig uit en is hij lastig te lezen en te begrijpen.

-- Standard SQL: Hard to read nested logic
SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
JOIN Customers c 
    ON o.CustomerID = c.CustomerID
JOIN Products p 
    ON o.ProductID = p.ProductID
WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Met een CTE kunnen we de logica opsplitsen in een leesbaarder formaat. We isoleren eerst de stap “filteren en joinen” en voeren daarna de aggregatie uit.

-- Standard SQL: Cleaner with 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 EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Codehergebruik

CTE’s helpen duplicatie te voorkomen doordat je dezelfde resultatenset kunt hergebruiken. Als je een aggregaat (zoals een som) moet berekenen en vervolgens op basis daarvan moet filteren, is een CTE ideaal.

Stel dat we de gemiddelde en totale verkoop per productcategorie moeten berekenen. We definiëren de berekening één keer in een CTE:

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
SELECT 
    Category, 
    TotalSales, 
    AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Andere toepassingen

Naast het vereenvoudigen van queries en codehergebruik hebben CTE’s nog andere toepassingen. Ik kan niet alle mogelijke toepassingen van CTE’s in detail behandelen. Onze cursus Data Manipulation in SQL is een goede optie als je verder wilt oefenen. Hieronder noem ik wel enkele van de andere voornaamste redenen:

• Structuur en leesbaarheid van queries: CTE’s verbeteren de leesbaarheid van SQL-code door queries op te delen in logische, opeenvolgende stappen. Elke stap in het queryproces kan door een eigen CTE worden weergegeven, waardoor de gehele query makkelijker te volgen is.
• Doorlopen van hiërarchische data: CTE’s kunnen helpen bij het navigeren door hiërarchische relaties, zoals organisatiestructuren, ouder-kindrelaties of elk datamodel met geneste niveaus. Recursieve CTE’s zijn handig voor het opvragen van hiërarchische data, omdat je hiermee niveaus iteratief kunt doorlopen.
• Aggregaties op meerdere niveaus: 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). Door deze aggregatiestappen te scheiden met CTE’s zorg je dat elk niveau onafhankelijk en logisch wordt berekend.
• Data combineren uit meerdere tabellen: Je kunt meerdere CTE’s gebruiken om data uit verschillende tabellen te combineren, waardoor de uiteindelijke combinatiestap gestructureerder wordt. Deze aanpak vereenvoudigt complexe joins en zorgt dat brondata logisch is georganiseerd voor betere leesbaarheid.

Geavanceerde SQL CTE-technieken

CTE’s ondersteunen geavanceerde SQL-technieken, waardoor ze veelzijdig en bruikbaar zijn voor verschillende use-cases. Hieronder staan enkele geavanceerde toepassingen van CTE’s.

Meerdere CTE’s in één query

Je kunt meerdere CTE’s in één query definiëren, wat complexe transformaties en berekeningen mogelijk maakt. Deze methode is handig wanneer een probleem meerdere fasen van dataverwerking vereist, waarbij elke CTE een aparte fase vertegenwoordigt.

Stel dat we verkoopdata hebben in een tabel Sales en dat we de totale verkoop per product willen berekenen, producten met bovengemiddelde totale verkoop willen identificeren en deze producten willen rangschikken op basis van hun totale verkoop.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
SELECT 
    ProductID, 
    TotalSales, 
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

In het bovenstaande voorbeeld:

• De eerste CTE (ProductSales) 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 boven het gemiddelde ligt.
• De uiteindelijke query rangschikt deze producten op basis van hun totale verkoop.

CTE’s in UPDATE-, DELETE- en MERGE-statements

Wanneer ze worden toegepast in UPDATE-, DELETE- en MERGE-operaties, kunnen CTE’s datamanipulatietaken vereenvoudigen, vooral bij complexe filters of hiërarchische data.

CTE gebruiken met een UPDATE-statement

Stel dat we een Employees-tabel hebben met een kolom EmployeeSalary. We willen alle werknemers die al langer dan 5 jaar bij het bedrijf werken een loonsverhoging van 10% geven.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

De CTE LongTermEmployees identificeert werknemers die langer dan vijf jaar in dienst zijn. Het UPDATE-statement gebruikt deze CTE om hun salarissen selectief te verhogen.

CTE gebruiken met een DELETE-statement

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

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

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

CTE gebruiken met een MERGE-statement

Het MERGE-statement in SQL maakt voorwaardelijke updates, inserts of deletions mogelijk in een doeltabel op basis van data in een brontabel. In het volgende voorbeeld combineert de CTE MergedInventory nieuwe en bestaande voorraaddata. Het MERGE-statement werkt vervolgens hoeveelheden bij voor bestaande producten of voegt nieuwe producten in op basis van de CTE-data.

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

Recursieve Common Table Expressions (CTE’s)

Recursieve CTE’s zijn een speciaal type CTE dat binnen de eigen definitie naar zichzelf verwijst, waardoor de query herhaalde bewerkingen kan uitvoeren. Dit maakt ze ideaal voor het werken met hiërarchische data, zoals organogrammen.

Introductie tot recursieve CTE’s

Recursieve CTE’s zijn een speciaal type CTE dat binnen de definitie naar zichzelf verwijst, waardoor de query herhaalde bewerkingen kan uitvoeren. Dit maakt ze ideaal voor hiërarchische of boomgestructureerde data, zoals organogrammen, mappenstructuren of productassemblages. De recursieve CTE verwerkt data iteratief en geeft stap voor stap resultaten terug totdat het recursieve deel geen nieuwe rijen meer oplevert (de stopconditie).

Anchor- en recursieve leden

Een recursieve CTE bestaat uit twee hoofdonderdelen:

• Anchor-lid: Het deel dat de basisquery definieert waarmee de recursie start.
• Recursief lid: Het deel dat naar de CTE zelf verwijst, zodat de “recursieve” bewerkingen kunnen worden uitgevoerd.

Stel dat we een Employees-tabel hebben, waarin elke rij een EmployeeID, EmployeeName en ManagerID bevat. Als we alle directe en indirecte rapportages voor een specifieke manager willen vinden, beginnen we met het anchor-lid dat de manager op het hoogste niveau identificeert.

Let op: In standaard SQL (PostgreSQL, MySQL, SQLite) moet je het keyword RECURSIVE gebruiken.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Hoe het werkt:

1. Anchor: De query voert eerst het anchor-lid uit en vindt de werknemer met ID 1.
2. Recursie: Het recursieve lid draait en zoekt werknemers waarvan ManagerID overeenkomt met de EmployeeID uit de vorige stap.
3. Lus: Dit proces herhaalt zich (Niveau 1 vindt Niveau 2, Niveau 2 vindt Niveau 3) totdat er geen nieuwe werknemers meer worden gevonden.

Mogelijke problemen of beperkingen van CTE’s in SQL

Het is belangrijk om de mogelijkheden en beperkingen van CTE’s te begrijpen om logische en leesbare queries te schrijven. Laten we enkele beperkingen en potentiële problemen bekijken bij het gebruik van CTE’s in verschillende databases.

Beperkingen in SQL Server en Azure

Er zijn enkele omgevingsspecifieke beperkingen voor SQL CTE’s bij gebruik van SQL Server of Azure Synapse Analytics. Ze omvatten het volgende:

• SQL Server: Het standaardmaximum voor het recursieniveau van recursieve CTE’s is 100. Als deze limiet wordt overschreden zonder aanpassing, treedt er een fout op. CTE-definities kunnen niet direct genest worden binnen een andere CTE-definitie (maar je kunt wel meerdere CTE’s sequentieel ketenen).
• Azure Synapse Analytics: Ondersteuning varieert per type pool. Recursieve CTE’s worden momenteel niet ondersteund in Dedicated SQL Pools (voorheen SQL DW). Ze worden wel ondersteund in Serverless SQL Pools. Bovendien kunnen sommige DML-bewerkingen (zoals UPDATE of DELETE met CTE’s) syntaxisbeperkingen hebben ten opzichte van standaard SQL Server.

Als je met SQL Server werkt, weet dan dat DataCamp veel goede bronnen heeft om je te helpen. Om te beginnen raad ik DataCamp’s cursus Introduction to SQL Server aan om de basis van SQL Server voor data-analyse onder de knie te krijgen. Je kunt ook ons SQL Server Developer-carrièrepad proberen, dat alles behandelt van transacties en foutafhandeling tot tijdreeksanalyse. Onze cursus Hierarchical and Recursive Queries in SQL Server gaat direct in op het schrijven van geavanceerde queries in SQL Server, inclusief methoden met CTE’s.

Andere potentiële problemen

Hoewel CTE’s handig zijn om complexe queries te vereenvoudigen, zijn er enkele veelvoorkomende valkuilen waarvan je op de hoogte moet zijn. Deze omvatten het volgende:

• Oneindige lussen in recursieve CTE’s: Als aan de stopconditie van een recursieve CTE niet wordt voldaan, kan dit resulteren in een oneindige lus, waardoor de query onbepaald blijft draaien. Gebruik de hint OPTION (MAXRECURSION N) om het maximale aantal recursieve iteraties te beperken, waarbij N de ingestelde limiet is, om te voorkomen dat de recursieve CTE oneindig blijft lopen.

  • Oplossing: Gebruik in SQL Server de hint OPTION (MAXRECURSION N) om het maximale aantal recursieve iteraties te beperken. In PostgreSQL kun je de CYCLE-clausule gebruiken om lussen automatisch te detecteren.

• Prestatie-overwegingen: Recursieve CTE’s kunnen veel resources verbruiken als de recursiediepte hoog is of als er grote datasets worden verwerkt. Beperk voor betere prestaties de hoeveelheid data per iteratie en zorg voor passende filtering om overmatige recursieniveaus te vermijden.

Prestaties: CTE’s vs. subqueries

Een veelvoorkomende mythe is dat CTE’s inherent sneller zijn dan subqueries. In werkelijkheid “inlinen” de meeste moderne query-optimizers (zoals die in SQL Server en PostgreSQL) standaard CTE’s, wat betekent dat ze precies zoals subqueries worden verwerkt, zonder prestatiedifferentie.

Toch kunnen CTE’s een prestatievoordeel opleveren via materialisatie, waarbij de database het CTE-resultaat één keer berekent en in cache plaatst voor meerdere verwijzingen binnen de hoofdquery.

Ik raad aan CTE’s vooral voor de leesbaarheid te gebruiken. Prestatievoordelen zijn situationeel en hangen af van hoe jouw specifieke database caching afhandelt.

Wanneer CTE’s gebruiken vs. andere technieken

Hoewel CTE’s geschikt zijn om queries met herhaalde taken te vereenvoudigen, hebben derived tables, views en tijdelijke tabellen vergelijkbare doelen. De onderstaande tabel belicht de voor- en nadelen van elke methode en wanneer je welke inzet.

Conclusie

CTE’s beheersen kost, zoals alles, oefening: ik raad je aan om DataCamp’s Associate Data Analyst in SQL-carrièrepad te volgen om een bekwame data-analist te worden. De cursus Reporting in SQL helpt je ook om vaardig te worden in het bouwen van complexe rapporten en dashboards voor effectieve datapresentatie. Tot slot kun je de SQL Associate Certification behalen om je beheersing van SQL bij het oplossen van zakelijke vraagstukken te tonen en je te onderscheiden van andere professionals.