SQL leren: WITH (CTE)
Leesbaar met WITH
Sannes vragen worden steeds langer: subquery in subquery in subquery. "Kan dat niet overzichtelijker?" Jawel: je geeft elk tussenresultaat een naam.
Een tussenresultaat met een naam
Met WITH naam AS (query) maak je een tijdelijk resultaat met een naam, een CTE (common table expression). Daarna gebruik je die naam als een tabel.
Hier telt per_lid de uitleningen per lid. De echte query koppelt er de namen aan.
WITH per_lid AS (SELECT lid_id, COUNT(*) AS aantal FROM uitleningen GROUP BY lid_id) SELECT TRIM(l.naam) AS naam, p.aantal FROM per_lid p JOIN leden l ON p.lid_id = l.id ORDER BY p.aantal DESC| naam | aantal |
|---|---|
| Sanne Willems | 3 |
| Amira El Idrissi | 3 |
| Nina De Clercq | 3 |
| Marie Dewulf | 3 |
| Jef Wouters | 2 |
| Pieter Desmet | 2 |
| Lotte Vandamme | 2 |
| Hamid Benali | 2 |
Meerdere CTE's
Je kan meerdere CTE's maken, gescheiden door komma's, en een CTE mag de vorige gebruiken. Elke stap krijgt zo een naam die zegt wat ze doet, en je leest de query van boven naar onder.
Een CTE bestaat alleen tijdens die ene query. Er wordt niets bewaard.
WITH per_lid AS (SELECT lid_id, COUNT(*) AS aantal FROM uitleningen GROUP BY lid_id), te_laat AS (SELECT lid_id, COUNT(*) AS keer FROM uitleningen WHERE teruggebracht_op > terug_voor GROUP BY lid_id) SELECT p.lid_id, p.aantal, t.keer AS te_laat FROM per_lid p LEFT JOIN te_laat t ON t.lid_id = p.lid_id| lid_id | aantal | te_laat |
|---|---|---|
| 1 | 3 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 2 |
| 4 | 2 | NULL |
| 5 | 2 | NULL |
| 7 | 3 | 2 |
| 8 | 2 | 1 |
| 9 | 2 | NULL |
Probeer het zelf
Welke leden leenden meer boeken dan gemiddeld? Toon lid_id en hun aantal uitleningen.
Klaar voor meer?
De volledige les heeft 6 oefeningen, van makkelijk naar uitdagend, met hints, punten en een reeks die je kan opbouwen.
Begin aan de les