CTE: meerstapsquery's met WITH

Echte zoekopdrachten hebben de neiging om te groeien: drie JOINs, een GROUP BY, een subquery, nog een subquery... en ze worden onleesbaar. CTE's ("Gemeenschappelijke tabel Expressions", de clausule WITH) kunt u een naam geven aan een tussenstap en gebruik deze alsof het een tabel in de hoofdquery is.

Basissyntaxis

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

Een CTE is alleen geldig voor de zoekopdracht die erop volgt: deze blijft niet bestaan de executie.

Voorbeeld

Vind de 3 klanten die het meeste hebben uitgegeven (som van geleverde bestellingen):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Lees in het Engels: "bereken eerst de uitgaven per klant; doe dan mee klanten en neem de top 3". Zonder CTE zou je een inline nodig hebben subquery in de FROM (legaal, maar luidruchtiger).

Meerdere CTE's in cascade

U kunt meerdere CTE's definiëren, gescheiden door komma's, en elke CTE kan verwijzen naar de vorige:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average omdat average altijd één enkele rij heeft: de Een cartesiaans product voegt gewoon total_average toe aan elke rij van het resultaat.

CTE versus subquery: wanneer gebruik je welke?

CTE's schitteren wanneer:

• dezelfde subquery wordt twee of meer keer gebruikt in de hoofdquery;
• je wilt de structuur expliciet maken in benoemde stappen (zelfdocumentatie);
• ze zijn de vereiste voor recursieve CTE's (WITH RECURSIVE, de volgende les in deze module).

Een inline-subquery is beknopter voor een stap die slechts één keer wordt gebruikt.

Probeer het

Oefening beoordelen