In SQL wordt de WITH-clausule ook wel CTE genoemd. Het is een krachtige functie waarmee we tijdelijke resultatensets binnen een query kunnen maken. Een belangrijke rol van CTE's is het vereenvoudigen van de complexe query's in kleinere en herbruikbare subquery's. Dit helpt om de code op de lange termijn beter leesbaar en onderhoudbaar te maken.
Neem deel aan deze tutorial terwijl we de werking van de Common Table-expressies verkennen met behulp van de WITH-clausule en ondersteunde functionaliteit.
Vereisten:
Voor demonstratiedoeleinden gebruiken we het volgende:
- MySQL versie 8.0 en hoger
- Sakila-voorbeelddatabase
Als aan de gegeven vereisten is voldaan, kunnen we verdergaan met meer informatie over CTE's en de WITH-clausule.
SQL WITH-clausule
Met de WITH-clausule kunnen we een of meer tijdelijke resultaatsets definiëren die bekend staan als algemene tabelexpressies.
We kunnen in de hoofdquery naar de resulterende CTE's verwijzen, net als elke andere tabel of resultatenset. Dit speelt een cruciale rol bij het creëren van modulaire SQL-query's.
Hoewel de syntaxis van CTE enigszins kan variëren, afhankelijk van uw vereisten, toont het volgende de basissyntaxis van CTE in SQL:
WITH cte_name (kolom1, kolom2, ...) AS (
-- CTE-query
SELECTEER ...
VAN ...
WAAR ...
)
-- Hoofdvraag
SELECTEER ...
VAN ...
WORD LID VAN cte_name OP ...
WAAR ...
We beginnen met het sleutelwoord WITH dat de SQL-database vertelt dat we CTE willen maken en gebruiken.
Vervolgens specificeren we de naam voor de CTE, zodat we ernaar kunnen verwijzen in andere zoekopdrachten.
We specificeren ook een optionele lijst met kolomnamen als de CTE de kolomaliassen bevat.
Vervolgens gaan we verder met het definiëren van de CTE-query. Hierin staan tussen haakjes alle taken of gegevens die de CTE uitvoert.
Ten slotte specificeren we de hoofdquery die verwijst naar de CTE.
Voorbeeldgebruik:
Een van de beste manieren om te begrijpen hoe u CTE's kunt gebruiken en ermee kunt werken, is door naar een praktisch voorbeeld te kijken.
Neem bijvoorbeeld de voorbeelddatabase van Sakila. Stel dat we de top 10 van klanten met het hoogste aantal verhuringen willen vinden.
Kijk eens naar de volgende getoonde CTE.
Gebruik de SQL WITH-clausule om de top 10 klanten met het hoogste huuraantal te vinden:
MET CustomerRentals AS (SELECT c.klant_id, c.voornaam, c.achternaam, COUNT(r.huur_id) AS verhuur_telling
VAN klant c
WORD LID VAN verhuur r OP c.customer_id = r.customer_id
GROEPEREN OP c.klant_id, c.voornaam, c.achternaam
)
SELECTEER *
VAN KlantVerhuur
BESTEL OP verhuur_count DESC
LIMIET 10;
In het gegeven voorbeeld beginnen we met het definiëren van een nieuwe CTE met behulp van het trefwoord WITH, gevolgd door de naam die we aan de CTE willen toewijzen. In dit geval noemen we het “CustomerRentals”.
Binnen de CTE-instantie berekenen we het huuraantal voor elke klant door de klant- en huurtabel samen te voegen.
Ten slotte selecteren we in de hoofdquery alle kolommen van de CTE, rangschikken we de resultaten op basis van het huuraantal (aflopende volgorde) en beperken we de uitvoer tot alleen de bovenste 10 rijen.
Hierdoor kunnen we de klanten met het hoogste aantal verhuur ophalen, zoals weergegeven in de volgende uitvoer:
Recursieve CTE's
In sommige andere gevallen heeft u mogelijk te maken met hiërarchische gegevensstructuren. Dit is waar de recursieve CTE's een rol gaan spelen.
Laten we bijvoorbeeld een geval nemen waarin we door de hiërarchische organisatie willen navigeren of een boomachtige structuur willen weergeven. We kunnen het sleutelwoord WITH RECURSIVE gebruiken om een recursieve CTE te maken.
Omdat er geen hiërarchische gegevens zijn die we in de Sakila-database kunnen gebruiken om een recursieve CTE aan te tonen, gaan we een eenvoudig voorbeeld opstellen.
CREATE TABLE-afdeling (afdeling_id INT PRIMAIRE SLEUTEL AUTO_INCREMENT,
afdelingsnaam VARCHAR(255) NIET NULL,
ouder_afdeling_id INT,
BUITENLANDSE SLEUTEL (bovenliggende_afdeling_id) REFERENTIES afdeling(afdeling_id)
);
INSERT INTO afdeling (afdelingsnaam, parent_department_id)
WAARDEN
('Bedrijf', NULL),
('Financiën', 1),
('HR', 1),
('Boekhouding', 2),
'Rekruteren', 3),
('Loonlijst', 4);
In dit geval hebben we een voorbeeld van een ‘afdelingstabel’ met enkele willekeurige gegevens. Om de hiërarchische structuur van de afdelingen te vinden, kunnen we als volgt een recursieve CTE gebruiken:
MET RECURSIEVE AfdelingHiërarchie AS (SELECT afdelingsid, afdelingsnaam, bovenliggende afdelingsid
VAN afdeling
WAAR parent_department_id NULL is
UNIE ALLEMAAL
SELECTEER d.afdeling_id, d.afdelingsnaam, d.ouder_afdeling_id
VAN afdeling d
WORD LID VAN AfdelingHierarchie dh ON d.parent_department_id = dh.department_id
)
SELECTEER *
VAN AfdelingHiërarchie;
In dit geval begint de recursieve CTE met afdelingen met een NULL “parent_department_id” (hoofdafdelingen) en haalt recursief de onderliggende afdelingen op.
Conclusie
In deze zelfstudie hebben we kennis gemaakt met de meest fundamentele en nuttige functies in SQL-databases, zoals de algemene tabelexpressies, door te begrijpen hoe we met het sleutelwoord WITH moeten werken.