Clausola WITH di MySQL (CTE) spiegata: sintassi, esempi, query ricorsive & consigli sulle prestazioni

1. Introduzione

MySQL è un sistema di gestione di database ampiamente utilizzato da sviluppatori e amministratori di database, che offre funzionalità SQL potenti e flessibili. Tra le sue caratteristiche, la clausola WITH (Common Table Expression: CTE), introdotta in MySQL 8.0, è uno strumento potente che rende le query SQL più leggibili e ne migliora la manutenibilità.

Questo articolo spiega la clausola WITH dalle basi all’uso avanzato, rivolgendosi a utenti principianti e di livello intermedio. In particolare, trattiamo argomenti pratici come sostituire le sottoquery e implementare query ricorsive.

Per chi sta imparando SQL o ha difficoltà a ottimizzare le query nel lavoro quotidiano, questo articolo vuole fornire soluzioni concrete. Segui il contenuto qui sotto per comprendere i fondamenti della clausola WITH e applicarli in scenari reali.

2. Nozioni di base della clausola WITH (Common Table Expressions)

Che cos’è la clausola WITH?

La clausola WITH è una sintassi utilizzata per definire un set di risultati temporaneo (Common Table Expression, CTE) all’interno di una query SQL e fare riferimento ad esso nelle query successive. Supportata a partire da MySQL 8.0, consente di riscrivere sottoquery complesse in una forma più chiara e concisa.

Ad esempio, scrivendo le sottoquery direttamente, la leggibilità può diminuire e la query complessiva può diventare lunga. Utilizzando la clausola WITH, è possibile suddividere le query in blocchi logici, rendendole più facili da comprendere.

Sintassi di base della clausola WITH

Di seguito è riportata la sintassi di base della clausola WITH:

WITH table_name AS (
  SELECT column1, column2
  FROM original_table
  WHERE condition
)
SELECT column1, column2
FROM table_name;

In questa sintassi, una tabella virtuale (Common Table Expression) viene definita dopo WITH e poi utilizzata nella query principale. Questo rende le sottoquery usate ripetutamente più facili da esprimere in modo conciso.

Differenze rispetto a sottoquery e viste

La clausola WITH crea un set di risultati temporaneamente disponibile e differisce da sottoquery e viste sotto diversi aspetti.

La clausola WITH è più leggibile rispetto alle sottoquery ed è ideale quando non è necessario creare un oggetto permanente come una vista.

Vantaggi dell’utilizzo della clausola WITH

1. Migliorata leggibilità della query Anche quando esistono più sottoquery, organizzarle con la clausola WITH chiarisce la struttura.
2. Migliorata riusabilità Definendo un set di risultati temporaneo, è possibile fare riferimento ad esso più volte all’interno della query.
3. Migliorata manutenibilità Poiché le query possono essere suddivise logicamente, le modifiche e le estensioni diventano più facili.

3. Uso di base della clausola WITH di MySQL

Sostituire le sottoquery

La clausola WITH è uno strumento potente per semplificare sottoquery complesse. Incorporare le sottoquery direttamente può rendere l’intera query complicata e difficile da leggere, ma l’uso della clausola WITH ne migliora la leggibilità.

Di seguito è riportato un esempio di base di sostituzione di una sottoquery utilizzando la clausola WITH.

Utilizzando una sottoquery:

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

Utilizzando la clausola WITH:

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

In questo esempio, un set di risultati temporaneo chiamato sales è definito usando la clausola WITH e poi referenziato nella query principale. Questo rende l’intera query più facile da comprendere e meglio organizzata.

Definire più Common Table Expressions (CTE)

La clausola WITH consente di definire più CTE. Questo permette una ulteriore modularizzazione delle query complesse.

Esempio:

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

In questo esempio, sales_per_customer calcola le vendite totali per cliente e, in base a quel risultato, high_value_customers estrae i clienti con importi di acquisto elevati. Utilizzando più CTE in sequenza, le query possono essere costruite passo dopo passo.

Using Nested CTEs

Utilizzando CTE nidificate, è possibile eseguire operazioni sui dati più complesse.

Example:

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

In questa query, sales_data aggrega le vendite per prodotto e ranked_sales assegna le classifiche in base al volume di vendite. Infine, vengono estratti i primi cinque prodotti.

Key Points for Practical Usage

1. Think in Logical Steps Build CTEs step by step to improve readability and make debugging easier.
2. Store Intermediate Calculation Results Group calculation results or filtering conditions used multiple times into CTEs to reduce code duplication.
3. Be Careful with Large Datasets Since CTEs generate temporary result sets, consider performance impact when working with large amounts of data.

4. Practical Examples of Recursive WITH Clauses

What Is a Recursive WITH Clause?

A recursive WITH clause (recursive CTE) is a method that uses a Common Table Expression to repeatedly execute a self-referencing query, enabling you to process hierarchical data and iterative calculations. Recursive CTEs are supported in MySQL 8.0 and later and are especially useful when working with parent-child relationships and hierarchical structures.

Basic Syntax of a Recursive CTE

To define a recursive CTE, use the WITH RECURSIVE keyword. The basic syntax is as follows:

WITH RECURSIVE recursive_table_name AS (
  initial_query -- starting point of the recursion
  UNION ALL
  recursive_query -- query called recursively
)
SELECT * FROM recursive_table_name;

• Initial query : Retrieves the first dataset for the recursive process.
• Recursive query : Generates new rows based on the initial query or the previous iteration’s results.
• UNION ALL : Combines the results of the initial query and the recursive query.

Example: Processing Hierarchical Data

Recursive CTEs are often used to expand data with a hierarchical structure (e.g., organization trees or category trees).

Example: Expanding an employee management hierarchy

Consider the following employees table:

Using this data, we can create a query that retrieves the full hierarchy starting from a given employee.

WITH RECURSIVE employee_hierarchy AS (
  -- Initial query: get top-level employees
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive query: get direct reports
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Result:

In this query, subordinates are searched recursively based on manager_id, expanding the full hierarchy.

Limitations and Cautions for Recursive CTEs

1. An End Condition Is Required If the recursive query does not meet an end condition, an infinite loop may occur. Always include appropriate conditions to prevent infinite recursion.
2. Performance Impact Recursive CTEs can involve many calculations over large datasets, which may increase execution time. Use LIMIT clauses and filtering conditions to improve efficiency.
3. Recursion Depth Limits MySQL has a limit on recursion depth, so take care when running very deep recursive processing. This limit can be configured with the max_recursive_iterations parameter.

Scenari in cui le CTE ricorsive sono utili

• Scorrere strutture di cartelle : Ricerca ricorsiva di cartelle e sottocartelle.
• Creare organigrammi : Visualizzare gerarchie da manager a subordinati.
• Visualizzare alberi di categorie : Recuperare categorie di prodotto gerarchiche o strutture di tag.

Le CTE ricorsive sono un modo potente per scrivere query SQL concise per questi scenari, migliorando al contempo la leggibilità.

5. Note e Considerazioni Quando Si Usa la Clausola WITH

Impatto sulle Prestazioni e Ottimizzazione

1. Ricalcolo della CTE In generale, una CTE definita con la clausola WITH viene ricalcolata ogni volta che viene referenziata. Pertanto, utilizzare la stessa CTE più volte può aumentare il tempo di esecuzione della query. Esempio:

   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;
   

Nel caso sopra, sales è referenziata due volte, quindi viene calcolata due volte. Per evitare ciò, quando è necessario referenziare i risultati più volte, memorizzare il risultato in una tabella temporanea può essere efficace.

Soluzione:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;

SELECT * FROM temp_sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM temp_sales;

2. Dividere CTE complesse Se la clausola WITH diventa troppo annidata, la query complessiva può diventare complessa e più difficile da debug. È importante suddividere la logica in modo appropriato affinché l’elaborazione all’interno di una singola CTE non diventi eccessivamente complicata.

Utilizzare le Clausole WITH su Grandi Set di Dati

La clausola WITH genera set di dati temporanei durante l’esecuzione. Quando si gestiscono grandi quantità di dati, ciò può gravare sulla memoria o sullo storage.

Contromisure:

• Filtrare i Dati con Clausole WHERE Ridurre il calcolo filtrando i dati non necessari all’interno della CTE.

  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;
  

• Usare Clausole LIMIT Se il set di dati è grande, usa LIMIT per estrarre solo i dati di cui hai bisogno.

Compatibilità Versione MySQL

La clausola WITH in MySQL è supportata in MySQL 8.0 e versioni successive. Poiché le versioni precedenti non supportano la clausola WITH, è necessario considerare alternative.

Alternative:

• Usare Subquery Utilizza le subquery direttamente invece della clausola WITH.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• Creare View Se hai bisogno di query riutilizzabili, l’uso di una view può essere efficace.

  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM sales_view;
  

Come Usare Correttamente la Clausola WITH

1. Prioritizzare la Leggibilità Lo scopo della clausola WITH è organizzare le query e migliorare la leggibilità. Un uso eccessivo può rendere le query più complesse, quindi usala solo quando necessario.
2. Verificare le Prestazioni Controlla il piano di esecuzione (il comando EXPLAIN) e considera come ottimizzare le prestazioni.

   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   

6. Casi d’Uso Pratici in Scenari Reali

Aggregazione Dati di Vendita

Ecco un esempio di aggregazione dei dati di vendita per mese e poi l’uso di quel risultato per calcolare la media mensile delle vendite.

Esempio: Aggregazione delle Vendite Mensili e Calcolo della Media

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

In questa query, monthly_sales calcola le vendite per mese e, in base a quel risultato, viene calcolata la media complessiva delle vendite. Questo consente di organizzare i dati in modo chiaro e semplifica l’analisi.

Filtrare i Dati in Base a Condizioni Specifiche

Separando la logica di filtraggio complessa in una clausola WITH, è possibile migliorare la leggibilità.

Esempio: Creare un Elenco di Clienti ad Alto Spesa

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

In questa query, customer_totals calcola l’importo totale degli acquisti per cliente e vengono estratti i clienti che soddisfano la condizione specificata.

Analizzare Dati Gerarchici

Quando si analizzano dati gerarchici come strutture organizzative o categorie, le clausole WITH ricorsive sono estremamente utili.

Esempio: Recuperare un Elenco di Subordinati Diretti e Indiretti

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

Questa query costruisce dati gerarchici in employee_hierarchy e recupera i dipendenti raggruppati per livello. Consente la generazione dinamica di informazioni simili a un organigramma.

Analisi Avanzata Utilizzando più CTE

Sfruttando più clausole WITH, i dati possono essere elaborati passo dopo passo, rendendo l’analisi complessa più semplice.

Esempio: Estrarre i Prodotti più Venduti per Categoria

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

In questa query, le vendite per categoria vengono calcolate e vengono estratti i primi tre prodotti all’interno di ciascuna categoria. Questo approccio è efficace quando si restringe il set di dati in base a condizioni specifiche.

Punti Chiave per l’Applicazione Pratica

1. Progettare le Query in Fasi Logiche Utilizzare la clausola WITH per suddividere le query e processare i dati passo dopo passo mantenendo la leggibilità.
2. Estrarre Solo i Dati Necessari Utilizzare le clausole WHERE e LIMIT per evitare di processare dati superflui e progettare query efficienti.
3. Applicazioni Aziendali Flessibili La clausola WITH può essere applicata in modo flessibile all’analisi delle vendite, segmentazione dei clienti, gestione dell’inventario e altro.

7. FAQ (Domande Frequenti)

Q1: Quando dovrei usare la clausola WITH?

A1:
La clausola WITH è particolarmente efficace nei seguenti scenari:

• Quando si desidera semplificare subquery complesse.
• Quando è necessario riutilizzare lo stesso set di dati più volte all’interno di una query.
• Quando si vuole suddividere logicamente una query per migliorare la leggibilità.

Ad esempio, nelle query che utilizzano gli stessi risultati di aggregazione più volte, la clausola WITH consente un’organizzazione più efficiente.

Q2: Quando è utile una CTE ricorsiva?

A2:
Le CTE ricorsive sono utili quando si gestiscono strutture gerarchiche o calcoli iterativi. In particolare:

• Elaborare dati gerarchici (ad es., alberi organizzativi, strutture di categorie).
• Visualizzare gerarchie di cartelle o file .
• Calcoli sequenziali di numeri o periodi di tempo (ad es., calcolare la sequenza di Fibonacci).

L’uso delle CTE ricorsive semplifica l’espansione e l’elaborazione di dati auto-referenziali.

Q3: Le query che utilizzano la clausola WITH sono più efficienti delle viste?

A3:
Dipende dal caso d’uso.

• Clausola WITH : Crea un set di risultati temporaneo usato solo all’interno della query. Ideale per dati che non necessitano di riutilizzo frequente.
• Vista : Memorizzata permanentemente nel database e riutilizzabile da altre query. Ideale per query che vengono eseguite più volte.

Scegliere il metodo appropriato in base allo scenario è importante.

Q4: Cosa causa il degrado delle prestazioni quando si usa la clausola WITH?

A4:
Le principali cause di degrado delle prestazioni quando si utilizza la clausola WITH includono:

• Ricalcolo del CTE : I risultati vengono ricalcolati ogni volta che vengono referenziati, aumentando il tempo di elaborazione.
• Gestione di grandi dataset : Generare grandi dataset all’interno del CTE aumenta l’uso di memoria e riduce le prestazioni.
• Mancanza di indicizzazione adeguata : Se le query all’interno del CTE non utilizzano indici appropriati, le prestazioni possono rallentare.

Contromisure:

• Considerare tabelle temporanee o viste se la frequenza di riutilizzo è elevata.
• Utilizzare le clausole WHERE e LIMIT per restringere correttamente i dati.

Q5: Quali alternative esistono per le versioni di MySQL che non supportano la clausola WITH?

A5:
Nelle versioni precedenti a MySQL 8.0 la clausola WITH non è supportata, quindi si possono usare le seguenti alternative:

• Usare sottoquery Utilizzare direttamente sottoquery al posto della clausola WITH.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• Usare tabelle temporanee Memorizzare i dataset riutilizzabili in una tabella temporanea.

  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM temp_sales;
  

Q6: Quali sono le migliori pratiche quando si usa la clausola WITH?

A6:
Tenere presente le seguenti best practice:

• Prioritizzare la semplicità : Non forzare logiche complesse in un’unica clausola WITH. Suddividerla in modo appropriato.
• Verificare le prestazioni : Controllare il piano di esecuzione usando il comando EXPLAIN e ottimizzare se necessario.
• Considerare la riusabilità : Se la frequenza di riutilizzo è alta, sfruttare viste o tabelle temporanee.

8. Conclusione

Questo articolo ha trattato la clausola WITH (Common Table Expression, CTE) introdotta in MySQL 8.0, dai fondamenti alle applicazioni avanzate. La clausola WITH è una funzionalità estremamente utile per rendere le query complesse più concise e leggibili. Di seguito i punti chiave.

Principali vantaggi della clausola WITH

1. Migliore leggibilità delle query Organizza sottoquery complesse, migliorando la leggibilità e la manutenibilità del codice SQL.
2. Riusabilità delle query Elabora i dati in modo efficiente quando lo stesso dataset viene referenziato più volte.
3. Abilita operazioni ricorsive sui dati Le CTE ricorsive semplificano l’elaborazione di dati gerarchici e calcoli iterativi.

Punti pratici d’uso

• Utile per analisi di vendite e dati dei clienti, consentendo aggregazioni passo‑passo.
• Le CTE ricorsive sono efficaci per l’elaborazione di dati gerarchici (ad esempio organigrammi o strutture di categorie).
• Combinare clausole WITH con viste o tabelle temporanee permette operazioni di database flessibili ed efficienti.

Considerazioni importanti

• La clausola WITH è potente, ma un uso improprio può ridurre le prestazioni.
• Valutare caso per caso riusabilità e performance, scegliendo tra viste o tabelle temporanee quando opportuno.
• Verificare sempre l’efficienza della query usando il piano di esecuzione (comando EXPLAIN).

Prossimi passi

Utilizzando la clausola WITH, è possibile creare query SQL più efficienti e manutenibili. Prova ad applicarla nei tuoi progetti reali seguendo questi passaggi:

1. Inizia con query semplici e pratica la loro strutturazione usando la clausola WITH.
2. Metti alla prova le CTE ricorsive per dati gerarchici e scenari complessi.
3. Concentrati sull’ottimizzazione delle prestazioni per migliorare ulteriormente le tue competenze SQL.

Questo conclude l’articolo. Usa la tua conoscenza della clausola WITH di MySQL nel tuo lavoro quotidiano e nei tuoi studi.