Una vista dona nom a una consulta per sempre i per a tothom. Moltes vegades el que necessites és el contrari: donar nom a un pas intermedi aquí, ara i només dins d'aquesta consulta, sense crear cap objecte ni demanar permís a ningú. Això és una expressió de taula comuna —Common Table Expression, CTE—, i s'escriu posant WITH davant del SELECT.

Amb elles tancaràs dues promeses del curs. La primera és de 07-04 i 07-05: la llegibilitat es trenca a partir de tres nivells de taules derivades, i aquí veuràs la mateixa consulta escrita de les dues maneres, l'una al costat de l'altra. La segona és de 03-06: un SELF JOIN recorre un nivell de la jerarquia, i per recórrer un arbre de profunditat desconeguda cal WITH RECURSIVE — amb el qual trauràs per fi l'organigrama complet de BotigaVerda amb el seu nivell i la seva ruta, la cadena de referits de tres salts que va de la Lucía a la Núria, i una sèrie de dotze mesos generada del no-res.

1. WITH: la subconsulta amb nom, posada al davant
2. Llegibilitat: tres nivells de taules derivades enfront de tres CTE
3. Diverses CTE encadenades: la consulta per passos
4. CTE, vista i taula derivada: quina fer servir
5. Materialització: el que va canviar a PostgreSQL 12
6. CTE a INSERT, UPDATE i DELETE; el patró "moure files"
7. WITH RECURSIVE: l'anatomia
8. Els tres casos de BotigaVerda
9. Bucles infinits i com protegir-se'n
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Una CTE és una subconsulta a la qual es dona un nom abans de la consulta principal. Viu només durant aquella sentència i es fa servir després com si fos una taula.

És exactament la taula derivada canònica de 07-04, amb les mateixes xifres, però llegida de dalt a baix: primer es defineix totals_comanda, després es fa servir. La sintaxi mínima és WITH nom1 AS ( SELECT ... ), nom2 AS ( SELECT ... ) SELECT ... FROM nom1 JOIN nom2 ..., on nom2 pot fer servir nom1.

Quatre regles que convé fixar des del principi: cada CTE necessita nom (i opcionalment en pot reanomenar les columnes, WITH t (comanda_id, total) AS (...)); se separen per comes, i WITH s'escriu una sola vegada encara que n'hi hagi cinc; una CTE pot referir-se a les anteriors, mai a les posteriors (tret que hi hagi RECURSIVE, apartat 7); i una CTE es pot fer servir diverses vegades a la mateixa consulta, cosa que una taula derivada no permet.

Aquí es tanca la promesa de 07-04. La pregunta: dels clients que han comprat, quins superen la facturació mitjana per client, i per quant? Són tres passos —total per comanda, total per client, mitjana d'aquests totals— i amb taules derivades queden imbricats:

Compta els parèntesis, i fixa't que el mateix càlcul de totals per comanda hi apareix dues vegades, copiat, perquè una taula derivada no es pot reutilitzar. Ara el mateix amb CTE:

Cinc dels dotze clients amb compres superen la mitjana de 60,66 € (727,95 € entre 12). El resultat és idèntic al de la versió anterior; el que ha canviat és que ara es pot llegir, revisar en un pull request i depurar pas a pas: n'hi ha prou amb canviar el SELECT final per SELECT * FROM totals_client per veure el resultat intermedi sense tocar res més.

I hi ha un guany que no es veu: totals_comanda s'escriu una vegada i es fa servir dues. A la versió imbricada estava duplicat, amb tot el que això significa el dia que canviï la fórmula de l'import.

Aquest és el patró natural d'escriure anàlisis complexes: no es pensa en una consulta, sinó en una seqüència de transformacions.

Cada CTE redueix o transforma la granularitat i té un nom que diu què conté. Si el resultat final no quadra, el diagnòstic és mecànic: executa SELECT COUNT(*) FROM totals_comanda (n'han de sortir 20), després totals_client (12), i el nivell on el número no sigui l'esperat és on és l'error. És el consell de 07-04 —"compta les files de cada nivell"—, ara amb els nivells batejats. I anomena-les pel que contenen, no c1, c2, c3: una consulta amb WITH vendes_2025 AS ..., devolucions_2025 AS ..., net AS ... s'entén sense llegir el SQL.

La regla de decisió cap en tres frases: un sol nivell i d'usar i llençar → taula derivada; dos passos o més, o el mateix pas fet servir dues vegades → CTE; el que faran servir altres consultes, altres persones o l'equip de BI → vista. I no són excloents: una vista pot estar definida amb WITH a dins.

Aquest apartat corregeix el que diuen gairebé tots els tutorials antics. Fins a PostgreSQL 11, una CTE era una barrera d'optimització: el motor l'executava sencera, desava el seu resultat en memòria i només després continuava, de manera que un filtre de la consulta exterior no es podia empènyer cap a dins. Escriure WITH t AS (SELECT * FROM linies_comanda) SELECT * FROM t WHERE producte_id = 15 calculava les 47 files per descartar-ne gairebé totes.

Des de PostgreSQL 12 ja no és així. Una CTE que es fa servir una sola vegada i no té efectes secundaris s'integra (inlining) a la consulta principal, exactament igual que una vista o una taula derivada, i el planificador pot empènyer els filtres cap a dins. S'ha acabat la penalització per escriure consultes llegibles. Les dues paraules clau per forçar cada comportament:

Escrit com a WITH totals_comanda AS MATERIALIZED ( ... ), el pas es calcula una sola vegada per moltes vegades que el consulti la sentència principal — que és el que vols quan aquest pas recorre 47 línies i el fas servir en dues subconsultes diferents.

La manera de saber què està passant és mirar-ho, no suposar-ho: si a l'EXPLAIN apareix un node CTE Scan sobre un CTE nom, s'ha materialitzat; si no apareix enlloc i veus directament els escanejos de les taules, s'ha integrat. La tècnica és la de 08-05.

L'error heretat. Durant anys es va ensenyar "fes servir una CTE per forçar l'ordre d'execució" i "no facis servir CTE, que són lentes". Les dues afirmacions van caducar a PostgreSQL 12. Si necessites la barrera, demana-la explícitament amb MATERIALIZED; si no, escriu la CTE perquè es llegeix millor i no en paguis res. A MySQL 8 i SQL Server les CTE sempre s'integren i la paraula clau no existeix; a Oracle hi ha pistes equivalents (/*+ MATERIALIZE */).

Un WITH pot precedir qualsevol sentència, no només un SELECT. I encara hi ha més: una CTE pot ser ella mateixa un INSERT, UPDATE o DELETE amb RETURNING, i el seu resultat alimentar el pas següent. Aquest és el patró que resol una cosa que a 05-04 era impossible en una sola sentència: moure files d'una taula a una altra.

BotigaVerda vol arxivar les comandes cancel·lades —avui, només la 6— en una taula històrica:

Una sola sentència, una sola transacció, cap finestra en què la fila no existeixi enlloc. Les tres propietats que ho fan possible: totes les subsentències veuen la mateixa instantània de la base de dades, la de l'inici de la sentència: la CTE esborrades no veu l'efecte de l'INSERT, i l'INSERT no veu l'efecte del DELETE sobre comandes. L'ordre d'execució no està garantit, així que escriure a la mateixa taula des de dues branques de la mateixa sentència dona resultats imprevisibles. I una CTE que escriu s'executa sempre, encara que la consulta principal no la faci servir: és l'única excepció a la integració de l'apartat 5.

Un segon ús, més quotidià: calcular amb WITH i actualitzar amb el resultat. Alinear el preu de catàleg amb el preu mitjà realment venut:

Disset productes, els que s'han venut alguna vegada. I fixa't en l'avantatge sobre la versió de 07-04: allà calia un WHERE EXISTS perquè els tres productes mai venuts no rebessin NULL; aquí el JOIN implícit amb la CTE ja els deixa fora. (Recarrega l'script després de provar-ho.)

Aquí es tanca la promesa de 03-06. Una CTE recursiva és una CTE que es referencia a si mateixa, i té sempre la mateixa forma:

Com s'executa, que és l'única cosa que cal entendre de debò:

Iteració a iteració, amb l'organigrama de BotigaVerda: la base produeix Rosa; la primera iteració busca els fills de Rosa i produeix Andrés, Beatriz i Daniel; la segona busca els fills d'aquests tres i produeix Óscar, Laia, Marc i Irene; la tercera busca els fills d'aquests quatre, no en troba cap, la taula de treball queda buida i el procés acaba. Total: 1 + 3 + 4 = 8 files, els vuit empleats.

Tres detalls de sintaxi amb parany: RECURSIVE va immediatament després de WITH, una sola vegada, encara que hi hagi diverses CTE i només una sigui recursiva; UNION ALL no elimina duplicats i és el normal, mentre que UNION tot sol els elimina a cada pas —protecció rudimentària contra cicles, però més cara—; i el terme recursiu només pot referenciar la CTE una vegada, sense agregats, sense ORDER BY i sense LIMIT a dins.

Els vuit empleats, amb la seva profunditat i la seva cadena de comandament, i presentats com un arbre gràcies a ORDER BY ruta. Compara-ho amb 03-06: allà calien dos LEFT JOIN encadenats, el nombre de nivells estava escrit a la consulta i tot i així només s'arribava fins a l'"avi". Aquí la consulta no sap quants nivells hi ha, i funcionaria igual amb quinze.

Dues variants d'una línia que valen molt: el subarbre d'una persona —canvia el WHERE e.cap_id IS NULL del terme base per WHERE e.id = 2 i obtens l'Andrés i els seus tres subordinats, 4 files—; i un ordre estable, perquè ruta amb noms depèn de com ordeni els accents l'idioma: acumula una segona columna amb els ids emplenats, a.ruta_id || '.' || lpad(e.id::text, 5, '0'), i ordena-hi.

La recursivitat també funciona en sentit contrari: en lloc de baixar de pares a fills, pujar de fill a pare. La pregunta de màrqueting és "de qui ve, en última instància, la Núria Bosch Ferrer?".

La cadena de tres salts que 03-06 no podia recórrer: 1 → 2 → 5 → 13. La fila amb salt = 3 és l'origen de la branca, i es detecta perquè el seu referit_per_id és NULL. Girant el JOIN —ON h.referit_per_id = d.id— i partint del client 1, s'obté el contrari: tots els descendents de la Lucía, que són 5 (Carlos, Marta i Inés al nivell 1; Ana al 2; Núria al 3).

La recursivitat no és només per a arbres: serveix per produir files del no-res — els dotze mesos de l'informe, sense cap taula pel mig:

Retorna dotze files, de 2025-03 a 2026-02: l'esquelet exacte de l'informe mensual. A PostgreSQL això s'escriu molt millor amb generate_series (03-06), però generate_series no existeix a MySQL ni a SQLite, i aquesta és la manera portable d'aconseguir el mateix. Fixa't en on és la condició de parada: dins del terme recursiu, al seu propi WHERE. Si la treus, la consulta no acaba mai.

El perill de la recursivitat és el cicle a les dades: si per un error de captura el client 1 aparegués com a referit pel 13, la consulta 8.2 donaria voltes per sempre generant files fins a esgotar el disc temporal. Quatre defenses, de la més artesanal a la més neta:

1. La columna de ruta amb = ANY(...). S'acumula el camí recorregut en un array i es rebutja qui ja hi sigui:

2. CYCLE, des de PostgreSQL 14, que és el mateix escrit pel motor. S'afegeix després del parèntesi de tancament de la CTE:

Significa: vigila la columna id, marca amb TRUE a es_cicle la fila en què es detecti repetició i deixa d'expandir per aquí; a cami queda el recorregut. És més curt, més ràpid i més difícil d'equivocar.

3. Un comptador de profunditat, útil quan a més vols limitar el nivell: WHERE a.nivell < 10 al terme recursiu. 4. El LIMIT d'emergència: SELECT * FROM cadena LIMIT 1000 atura l'execució en arribar a mil files, perquè PostgreSQL avalua la recursió de manera mandrosa. És una xarxa de seguretat per experimentar, no una solució: fes-la servir mentre desenvolupes una recursiva sobre dades que no coneixes.

Nota de dialecte:

Motor	Paraula clau	Detall
PostgreSQL	WITH RECURSIVE obligatori	CYCLE i SEARCH des de la 14. Sense RECURSIVE, error de "la relació no existeix"
SQL Server	WITH tot sol	RECURSIVE no existeix; MAXRECURSION limita a 100 nivells per omissió
MySQL 8 / MariaDB	WITH RECURSIVE	Límit per cte_max_recursion_depth (1000 per omissió)
SQLite	WITH RECURSIVE (la paraula és opcional)	Suport complet, inclòs UNION
Oracle	WITH RECURSIVE, o el clàssic CONNECT BY	CONNECT BY PRIOR ... START WITH ... és anterior a l'estàndard i continua molt viu, amb LEVEL, SYS_CONNECT_BY_PATH i NOCYCLE

• Oblidar RECURSIVE. Sense ell, la CTE no pot referir-se a si mateixa: ERROR: relation "arbre" does not exist. I va darrere de WITH, no davant del nom de la CTE recursiva.
• Repetir WITH a cada CTE (s'escriu una sola vegada; les altres se separen per comes) o referir-se a una CTE definida més avall (només es veuen les anteriors, tret de l'autoreferència de RECURSIVE).
• Escriure un terme recursiu sense condició de parada. La consulta no acaba. En un arbre, la parada és implícita (s'acaben els fills); en una sèrie generada, l'has d'escriure tu al WHERE.
• Ignorar els cicles a les dades. Una jerarquia amb un bucle penja la consulta. CYCLE (PG 14+) o la columna de ruta amb = ANY(...).
• Creure que una CTE és sempre una barrera d'optimització. Ho era fins a PostgreSQL 11. Des de la 12 s'integra si es fa servir una vegada; si vols la barrera, demana-la amb MATERIALIZED.
• Fer servir UNION en lloc d'UNION ALL "per si de cas". Elimina duplicats a cada pas i costa bastant més. I escriure a la mateixa taula des de dues branques de la mateixa sentència: l'ordre no està garantit i el resultat és imprevisible.
• Consell: escriu la consulta per passos i executa-la per passos. Substitueix el SELECT final per SELECT * FROM pas_intermedi i comprova les files de cada nivell: 47 → 20 → 12 → 1.
• Consell: en una recursiva, comença sempre pel terme base sol, comprova que retorna exactament les arrels que esperes i només llavors afegeix l'UNION ALL. I acumula sempre nivell i ruta: no costen res i són la meitat del diagnòstic quan alguna cosa surt malament.

Reescriu amb CTE l'informe de "dos agregats de granularitat diferent" de 07-04: per client, nombre de comandes, facturació de producte, ports i total. Ha de quadrar en 727,95 € + 118,25 € = 846,20 €. (1) Escriu-lo amb dues CTE (per_comanda i per_client) en lloc de dues taules derivades. (2) Afegeix-ne una tercera que calculi els totals generals i mostra'ls al costat dels de cada client. (3) S'integraran les CTE o es materialitzaran? Com ho comprovaries?

RH vol, per a cada empleat, quantes persones té per sota en total (directes i indirectes). (1) Escriu una CTE recursiva que retorni tots els parells (cap, subordinat a qualsevol profunditat). (2) Agrega per obtenir el recompte per cap, incloent-hi amb 0 els qui no tenen ningú. (3) Comprova que la Rosa surt amb 7 i l'Andrés amb 3.

Un company ha escrit això per arxivar les ressenyes de productes descatalogats i no entén el resultat:

1. Què retorna el COUNT, 12 o un altre número? Per què?
2. S'ha esborrat alguna cosa realment? Quantes files?
3. Reescriu-ho perquè arxivi de debò en una taula ressenyes_arxiu i retorni quantes n'ha mogut.

Solució 1

1 i 2:

(2 primeres de 12 files.) Les columnes quadren: 727,95 + 118,25 = 846,20 €. El que resol el problema és el mateix que a 07-04 —agregar cada cosa a la seva granularitat abans d'unir—, però ara els passos tenen nom i per_comanda es defineix una vegada i es fa servir dues vegades (des de per_client i, a través d'ella, des de total_general).

3. per_comanda es fa servir una sola vegada (des de per_client), així que s'integrarà; per_client es fa servir dues vegades, així que PostgreSQL la materialitzarà — i aquí és el desitjable, perquè calcular-la dues vegades seria recórrer 47 línies dues vegades. Es comprova amb EXPLAIN (ANALYZE, COSTS OFF): si apareix un node CTE Scan on per_client, s'ha materialitzat.

Solució 2

(4 primeres de 8 files; els empleats 5, 6, 7 i 8 tanquen també amb 0.) La Rosa amb 7 i l'Andrés amb 3, com demanava l'enunciat. Dues idees fan que funcioni: el terme base arrenca des de tots els empleats alhora, no només des de l'arrel, de manera que cadascun construeix el seu propi subarbre; i el COUNT(*) - 1 descompta la fila (x, x) en què cada empleat es compta a si mateix — que és justament el que permet que els quatre sense subordinats apareguin amb 0 en lloc de desaparèixer.

Solució 3

1. Retorna 12, és a dir, el recompte d'abans de l'esborrat. És la propietat 1 de l'apartat 6: totes les parts de la sentència veuen la mateixa instantània, la de l'instant inicial. El SELECT principal no veu l'efecte del DELETE de la CTE.

2. Sí que s'ha esborrat, però zero files. L'únic producte amb actiu = FALSE és el 20 (Càpsules d'espirulina) i no té ressenyes, així que la CTE retorna el conjunt buit. Si el producte descatalogat fos l'1, se n'haurien esborrat les 2 ressenyes i el COUNT hauria continuat dient 12 — que és on és el parany real de l'exercici. 3. La versió correcta:

Ara sí: el DELETE alimenta l'INSERT, l'INSERT retorna el que ha inserit i el SELECT final compta el resultat de l'operació, no l'estat d'una taula. És el patró "moure files" complet, en una sentència i en una transacció.

WITH és l'eina que converteix SQL en una cosa que es pot llegir:

• Una CTE és una subconsulta amb nom posada al davant. Viu només durant la sentència, es pot reutilitzar dins d'ella —cosa que una taula derivada no permet— i no requereix permisos ni deixa rastre. Enfront de tres nivells de taules derivades imbricades, tres CTE encadenades diuen el mateix amb la meitat de parèntesis i es depuren pas a pas: 47 línies → 20 comandes → 12 clients → 1 mitjana, amb 5 clients per sobre dels 60,66 € de mitjana.
• CTE, vista o taula derivada: un pas d'usar i llençar, derivada; dos passos o més o reutilització, CTE; una cosa que faran servir altres consultes i altres persones, vista.
• Des de PostgreSQL 12 una CTE ja no és una barrera d'optimització: s'integra si es fa servir una vegada i es materialitza si es fa servir diverses. AS MATERIALIZED i AS NOT MATERIALIZED forcen cada comportament, i l'EXPLAIN diu quin està passant. El que diguin els tutorials anteriors al 2019 sobre això ja no val.
• Un WITH pot precedir un INSERT, UPDATE o DELETE, i una CTE pot ser ella mateixa una escriptura amb RETURNING: d'aquí el patró "moure files", que esborra d'una taula i insereix el que ha esborrat en una altra en una sola sentència atòmica. Totes les branques veuen la mateixa instantània.
• WITH RECURSIVE = terme base UNION ALL terme recursiu, iterant fins que no surten files noves. Amb ell: l'organigrama complet amb nivell i ruta (8 empleats, 3 nivells), la cadena de referits de tres salts 1 → 2 → 5 → 13, i una sèrie de 12 mesos generada sense generate_series. I els cicles a les dades, que la pengen, s'eviten amb una columna de ruta i = ANY(...), amb CYCLE ... SET ... USING ... des de PostgreSQL 14, amb un límit de profunditat o, mentre s'experimenta, amb un LIMIT d'emergència.

Amb les CTE ja saps descompondre una consulta i recórrer una estructura. Queda el buit que 04-05 va deixar obert amb totes les lletres: agregar sense col·lapsar les files. Quan vols el total de la comanda al costat de cadascuna de les seves línies, el percentatge que representa cada producte sobre la facturació global, la posició de cada client en un rànquing, quant ha variat un mes respecte de l'anterior o la mitjana mòbil d'un trimestre, un GROUP BY no serveix: col·lapsa exactament el que vols conservar. A la lliçó següent, funcions de finestra, veuràs la clàusula OVER que resol tot això d'una vegada, l'anatomia de PARTITION BY / ORDER BY / marc, per què no es pot filtrar per una funció de finestra al WHERE —l'error més freqüent de tots— i els rànquings, acumulats i mitjanes mòbils de BotigaVerda calculats sense perdre ni una de les 47 línies.