← Terug naar Kennisbank← Vorige vraagVolgende vraag →
ProgrammatieBackend ontwikkelaar

Wat doet de WITH-instructie (Common Table Expressions, CTE)? Wat zijn de voordelen van het gebruik van CTE in vergelijking met subquery's? Geef een voorbeeld.

Slaag voor sollicitatiegesprekken met de Hintsage AI-assistent

Antwoord

De WITH-instructie (of CTE — Common Table Expression) creëert tijdelijk een resultaatset die kan worden gebruikt in de hoofd-SELECT of zelfs recursief. CTE's maken query's makkelijker te begrijpen, verhogen de leesbaarheid en stellen ons in staat om tussenresultaten opnieuw te gebruiken.

Voordelen ten opzichte van gewone subquery's:

  • Verbeteren de leesbaarheid van complexe query's (makkelijker te debuggen en onderhouden).
  • Het is mogelijk om recursieve query's te gebruiken.
  • CTE kan meerdere keren bij naam worden aangesproken in plaats van het dupliceren van dezelfde subquery.

Voorbeeld:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Vraag met een haakje

"Is het belangrijk om MATERIALIZED/NOT MATERIALIZED aan te geven bij het declareren van een CTE? Hoe kan dit de prestaties beïnvloeden?"

Antwoord: In PostgreSQL kan je expliciet MATERIALIZED aangeven (CTE altijd één keer berekenen en opslaan, zelfs als deze meerdere keren wordt gebruikt) of NOT MATERIALIZED (CTE zal inline worden aanroepende in de hoofdquery). Gewoonlijk kiest de optimizer zelf of het de moeite waard is om CTE te materialiseren, maar gedwongen aanwijzingen beïnvloeden soms aanzienlijk de snelheid.

Voorbeeld:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Verhaal

In een groot project verving een analist alle subquery's door CTE's, niet wetende dat CTE's in PostgreSQL tot versie 12 altijd gematerialiseerd worden. Het resultaat — de query's vertraagden met 2-3 keer. Na terugkeer naar subquery's of overstappen op nieuwe versies verdween het probleem.

Verhaal

Een rapport met meerdere niveaus van geneste subquery's werd onleesbaar voor een collega. We herschreven het naar CTE met zinvolle namen — de discussie en ondersteuning van query's werden aanzienlijk eenvoudiger.

Verhaal

Twee CTE's met dezelfde namen veroorzaakten verwarring in een groot script: de CTE werd al gebruikt voordat deze werd gedeclareerd. Het resultaat — compilatiefout en veel tijdverspilling bij het zoeken naar de oorzaak.