← Torna alla Base di conoscenza← Domanda precedenteDomanda successiva →
ProgrammazioneSviluppatore Backend

Cosa fa l'istruzione WITH (Common Table Expressions, CTE)? Quali sono i vantaggi dell'uso delle CTE rispetto alle sottoquery? Fornisci un esempio.

Supera i colloqui con l'assistente IA Hintsage

Risposta

L'istruzione WITH (o CTE — Common Table Expression) crea temporaneamente un set di dati risultante, che può essere utilizzato nella SELECT principale o anche in modo ricorsivo. Le CTE rendono le query più facili da comprendere, migliorano la leggibilità e consentono di riutilizzare risultati intermedi.

Vantaggi rispetto alle normali sottoquery:

  • Migliorano la leggibilità di query complesse (più facili da debug e mantenere).
  • Consentono l'uso di query ricorsive.
  • È possibile fare riferimento a CTE più volte per nome, evitando la duplicazione della stessa sottoquery.

Esempio:

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

Domanda insidiosa

"È importante specificare MATERIALIZED/NOT MATERIALIZED durante la dichiarazione di una CTE? Come può influenzare le prestazioni?"

Risposta: In PostgreSQL è possibile specificare esplicitamente MATERIALIZED (calcolare sempre la CTE una volta e memorizzarla, anche se utilizzata più volte) o NOT MATERIALIZED (la CTE verrà inlinata nella query principale). Di solito, l'ottimizzatore decide se materializzare la CTE, ma la specifica forzata può talvolta influenzare significativamente la velocità.

Esempio:

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

Storia

In un grande progetto, un analista ha sostituito tutte le sottoquery con CTE, senza sapere che in PostgreSQL fino alla versione 12 le CTE venivano sempre materializzate. Risultato: le query si sono rallentate di 2-3 volte. Dopo il ritorno alle sottoquery o il passaggio a nuove versioni, il problema è scomparso.

Storia

Un report con più livelli di sottoquery è risultato illeggibile per un collega. È stato riscritto in CTE con nomi significativi: la discussione e il supporto delle query si sono semplificati notevolmente.

Storia

Due CTE con nomi identici hanno causato confusione in un grande script: la CTE è stata utilizzata prima della sua dichiarazione. Risultato: errore di compilazione e grande perdita di tempo nella ricerca della causa.