È possibile utilizzare più dichiarazioni WITH in SQL?

Una guida completa alle istruzioni WITH multiple in SQL, perfetta per principianti ed esperti.

La clausola SQL WITH consente di definire una CTE (common table expression). Una CTE è come una tabella che viene popolata durante l'esecuzione della query. È possibile utilizzare più istruzioni WITH in una query SQL per definire più CTE. In questo articolo spiegheremo come definire più CTE in una singola query. Mostreremo inoltre come questo approccio porti la potenza espressiva di una query SQL a un livello superiore.

È possibile utilizzare più dichiarazioni WITH in SQL?

Sì! Di seguito è riportata una semplice query che utilizza due clausole WITH:

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

Nella query precedente sono presenti due CTE. La prima (in rosso) si chiama wine_exporting_country e la seconda (in blu) car_exporting_country. La query principale (in nero) fa riferimento a entrambe le istruzioni WITH come tabelle regolari utilizzando INTERSECT. L'intersezione delle due tabelle restituirà solo i Paesi presenti in entrambi i risultati CTE.

Si noti che la parola chiave WITH è presente solo una volta. Dopo la clausola WITH, è necessario inserire il nome della CTE, poi la parola chiave AS e infine tra parentesi la query SELECT per definire la CTE.

Se si desidera imparare a utilizzare più istruzioni WITH in una query, si consiglia il nostro corso interattivo Query ricorsive interattivo. Vi insegna i dettagli della sintassi della clausola WITH con oltre 100 esercizi pratici. Copre la sintassi di base, le istruzioni multiple WITH e le query ricorsive, l'uso più complesso della sintassi WITH.

La clausola WITH in SQL

Spieghiamo innanzitutto come funziona la clausola WITH. Essa crea al volo una sorta di tabella virtuale (la CTE), che viene creata e popolata durante l'esecuzione di una singola istruzione SQL; dopo l'esecuzione dell'SQL, la "tabella" CTE viene automaticamente rimossa. Per una migliore comprensione della clausola WITH, suggerisco l'articolo Cos'è una CTE?

In questo articolo, utilizzeremo una tabella di database chiamata prodotto che tiene traccia dei Paesi e dei prodotti più popolari che producono (ad esempio, olio d'oliva, vino, automobili). La tabella registra la quantità prodotta, importata ed esportata da ciascun Paese. La tabella contiene anche il prezzo del prodotto e la popolazione del Paese.

Vediamo un semplice esempio di query che utilizza la clausola WITH per definire una CTE. Supponiamo di voler ottenere un elenco dei Paesi che esportano più olio d'oliva di quanto ne importino. Vogliamo che i risultati siano ordinati in base alla quantità prodotta da ciascun paese. Ecco la query:

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

In questa query, abbiamo una CTE chiamata olive_oil_exporter, che è come una tabella con due colonne: country e qty_produced. Le righe della CTE contengono solo i Paesi che esportano più olio d'oliva di quelli che importano. Successivamente, utilizziamo un normale SELECT per interrogare la CTE olive_oil_exporter per ottenere i nomi dei paesi, che ordiniamo in modo decrescente in base alla quantità di olio d'oliva prodotto in quel paese.

Utilizzo di più dichiarazioni WITH in SQL

Supponiamo di voler classificare i paesi in paesi produttori di alimenti e paesi produttori di tecnologia. Per essere un produttore alimentare, un Paese deve esportare più di 100 milioni di dollari in prodotti alimentari. Per essere un produttore di alta tecnologia, un Paese deve produrre più di 1000 dollari in prodotti tecnologici per abitante.

Vogliamo un report con i nomi di tutti i Paesi e due colonne chiamate is_a_food_producer e is_a_hightech_producer. La query SQL per questo report è:

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

Nella query precedente, possiamo chiaramente identificare tre query separate. Le prime due query utilizzano la clausola WITH per definire due tabelle CTE: food_producer e hightech_producer. La terza query è la query principale, che consuma le due tabelle CTE create in precedenza.

Dopo la clausola WITH si trova il nome della tabella CTE (food_producer), poi la sottoclausola AS e infine (tra parentesi) la query per quella CTE. Per la seconda CTE, la clausola WITH non è necessaria; basta mettere una virgola e ripetere la stessa sintassi a partire dal nome della CTE.

Entrambe le CTE food_producer e hightech_producer hanno una sola colonna: country. La query principale prende i nomi di tutti i paesi dalla tabella product, quindi viene eseguita una LEFT JOIN contro ciascuna delle CTE. Quando LEFT JOIN non ha una riga corrispondente, significa che la risposta per questo Paese è 'No'; quando c'è una riga corrispondente, il valore per questo Paese è 'Sì'.

Prima di passare alla sezione successiva, vorrei suggerire l'articolo Come scrivere CTE multiple. In esso si possono trovare molti esempi e spiegazioni sulle CTE.

Utilizzo di dichiarazioni WITH multiple e annidate in SQL

In alcuni casi, abbiamo bisogno di una seconda CTE basata sulla prima CTE: una CTE annidata. In altre parole, la query che definisce la seconda CTE deve avere un riferimento alla prima CTE. Vediamo un esempio.

Vogliamo ottenere l'importo totale in dollari esportato dai Paesi in prodotti alimentari. Nello stesso report, vogliamo mostrare la percentuale che questo importo rappresenta sul totale delle esportazioni di quel Paese per tutti i tipi di prodotti. La query è:

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

In questa query, abbiamo creato una CTE chiamata country_export_by_product che ha le colonne country, product_family e total_exports (che rappresenta la quantità totale di questo prodotto esportato da questo paese (in dollari)). Si noti che la clausola GROUP BY utilizza le colonne country e product_family.

La CTE successiva si chiama country_export_total e si basa sulla CTE precedente country_export_by_product. L'idea di questa CTE è quella di ottenere l'importo totale esportato da ciascun Paese in base alla CTE precedente. Si noti che nel secondo CTE viene utilizzata la clausola GROUP BY. La necessità di livelli diversi di clausola GROUP BY è il motivo per cui abbiamo due CTE.

La query principale fa riferimento a entrambe le CTE, unendole per il valore del paese. Quindi l'espressione

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

... viene utilizzata per calcolare la percentuale che ogni prodotto rappresenta sul totale delle esportazioni di quel paese.

In termini di sintassi, è possibile che una CTE faccia riferimento a un'altra CTE nella stessa query. Questo è ciò che abbiamo fatto nella nostra query: quando abbiamo definito la CTE CTE country_export_total, abbiamo fatto riferimento alla CTE country_export_by_product definita in precedenza.

Si noti che si può fare riferimento all'istruzione WITH definita prima dell'attuale istruzione WITH, ma non a quelle successive. È possibile fare riferimento a ogni istruzione WITH più volte in un'altra istruzione WITH o in una query principale. Nel nostro esempio, abbiamo fatto riferimento alla prima istruzione WITH definita (la CTE country_export_by_product ) in due punti: Nel secondo WITH (la CTE country_export_total ) e nella query principale.

Altre limitazioni relative alla sintassi della clausola WITH sono:

• La parola chiave WITH deve essere usata una sola volta, prima della prima CTE.
• Tutte le CTE sono separate da virgole, ma non c'è nessuna virgola prima della query principale. Questo segue lo schema sintattico:

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

Suggerisco di consultare l'articolo Le CTE di SQL spiegate con esempi per altri esempi di query WITH; essi dimostrano come migliorare l'organizzazione e la leggibilità delle query SQL utilizzando le CTE.

Uso della clausola WITH per creare Query ricorsive

La clausola WITH viene utilizzata in SQL per definire query ricorsive. Le query ricorsive consentono di interrogare strutture gerarchiche (ad esempio, organigrammi, alberi o grafici). Per saperne di più sull' interrogazione di strutture gerarchiche, si veda qui.

Le query ricorsive si basano sulla clausola WITH. Per creare una query ricorsiva, è sufficiente una sola clausola WITH, ma la query all'interno di WITH è composta da due parti.

Le query ricorsive sono utili quando le tabelle o il modello di dati del database hanno una sorta di gerarchia implicita. Forse l'esempio più comune di tabella per spiegare questo argomento è la tipica tabella employee con le colonne employee_id e manager_employee_id.

Se vogliamo un report che mostri tutti i dipendenti con i nomi dei loro manager e il livello gerarchico del dipendente, possiamo usare la seguente query ricorsiva:

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

In questa query è presente la clausola WITH RECURSIVE, che viene utilizzata per creare una CTE ricorsiva chiamata company_hierarchy. La CTE conterrà tutti i nomi dei dipendenti e dei loro manager. Si noti che la CTE ha due istruzioni SELECT collegate da UNION ALL. La prima SELECT serve a ottenere il primo dipendente della query ricorsiva (CEO John Smith).

La seconda SELECT della UNION è una query che viene eseguita più volte. A ogni esecuzione, restituisce i dipendenti del livello successivo della gerarchia. Ad esempio, la prima esecuzione restituisce tutti i dipendenti che fanno capo direttamente a John Smith.

Infine, c'è una terza istruzione SELECT; è esterna alla CTE. Seleziona i nomi dei dipendenti, i nomi dei loro capi e il livello gerarchico. I dati vengono presi dalla CTE e uniti alla tabella employees. Utilizziamo un LEFT JOIN perché vogliamo tutti i dati della CTE (compreso John Smith, che ha un valore NULL nella colonna manager_id). I risultati sono mostrati in ordine crescente: prima per livello gerarchico, poi per employee_id del capo. Di seguito è riportato il risultato della query:

Per saperne di più sulle query ricorsive, consultate l'articolo Cos'è una CTE ricorsiva in SQL?

Continuare a conoscere le dichiarazioni multiple WITH in SQL

In questo articolo abbiamo trattato l'uso di più istruzioni WITH in una singola query SQL. Abbiamo anche spiegato come utilizzare la clausola WITH nelle query ricorsive. Se desiderate continuare a conoscere la clausola WITH, vi consiglio il nostro corso Query ricorsive che offre un'eccellente opportunità di esercitarsi con il tipo più impegnativo di query SQL.

Le istruzioni multiple WITH sono molto utili quando si scrivono rapporti SQL complessi. Se questo è il vostro caso, vi consiglio anche il nostro foglio informativo gratuito SQL per l'analisi dei dati, che abbiamo progettato appositamente per aiutarvi a scrivere query complesse per l'analisi dei dati.

Se volete esercitarvi con l'SQL a un livello avanzato, date un'occhiata alla nostra traccia pratica SQL avanzato . Contiene oltre 200 esercizi per esercitarsi su concetti avanzati di SQL. Ogni due mesi pubblichiamo un nuovo corso pratico di SQL avanzato nel nostro percorso mensile Pratica su SQL.

È inoltre possibile ottenere tutti questi corsi e altri ancora con il nostro Completo per sempre piano. Il piano vi dà accesso a vita a tutti i nostri corsi SQL a vari livelli di competenza e in quattro dialetti SQL. Iscrivetevi oggi stesso!