SQL - Common Table Expression (CTE)

Ciao a tutti, futuri maghi dell'SQL! Oggi ci imbarchiamo in un viaggio emozionante nel mondo delle Espressioni di Tabella Comune, o CTE per brevità. Non preoccupatevi se siete nuovi al programming - vi guiderò passo dopo passo, proprio come ho fatto per centinaia di studenti nel corso degli anni. Allora, prendetevi la vostra bevanda preferita, fatevi comodi e tuffiamoci dentro!

L'Espressione di Tabella Comune in SQL

Immaginate di organizzare una grande festa (chi non adora una buona analogia?). Avete una lista di compiti e volete spezzettarli in piccoli pezzi gestibili. Questo è esattamente ciò che fa una Common Table Expression in SQL - ci aiuta a spezzare le query complesse in parti più semplici e leggibili.

Una CTE è come un结果集 temporaneo con un nome che potete richiamare all'interno di una SELECT, INSERT, UPDATE, DELETE o MERGE statement. È definita all'interno della portata di esecuzione di una singola istruzione. Pensate a essa come una tabella temporanea che esiste solo per la vostra query.

Ecco un esempio semplice:

WITH cte_esempio AS (
SELECT 'Ciao, CTE!' AS saluto
)
SELECT saluto FROM cte_esempio;

In questo esempio:

1. Iniziamo con la parola chiave WITH, che segnala l'inizio della nostra CTE.
2. Diamo un nome alla nostra CTE: cte_esempio.
3. Definiamo cosa conterrà la nostra CTE: in questo caso, una semplice istruzione SELECT che crea una colonna chiamata 'saluto' con il valore 'Ciao, CTE!'.
4. Dopo la definizione della CTE, abbiamo la nostra query principale che utilizza la CTE.

Quando eseguite questo, vedrete:

| saluto    |
|-----------|
| Ciao, CTE! |

Non è fantastico? Abbiamo appena creato la nostra prima CTE!

La Clausola WITH in MySQL

Ora, parliamo della clausola WITH in MySQL. È la bacchetta magica che dà vita alle nostre CTE. La sintassi generale è questa:

WITH cte_nome [(elenco_colonne)] AS (query)
SELECT * FROM cte_nome;

Ecco un esempio più pratico. Immaginiamo di avere una tabella dei dipendenti:

CREATE TABLE dipendenti (
id INT PRIMARY KEY,
nome VARCHAR(50),
dipartimento VARCHAR(50),
stipendio DECIMAL(10, 2)
);

INSERT INTO dipendenti VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finanza', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

Ora, utilizziamo una CTE per trovare lo stipendio medio per dipartimento:

WITH dept_stipendio_medio AS (
SELECT dipartimento, AVG(stipendio) AS stipendio_medio
FROM dipendenti
GROUP BY dipartimento
)
SELECT * FROM dept_stipendio_medio
ORDER BY stipendio_medio DESC;

Questa query ci darà:

| dipartimento | stipendio_medio |
|--------------|-----------------|
| IT           | 62500.00        |
| Finanza      | 55000.00        |
| HR           | 51000.00        |

Qui, abbiamo utilizzato una CTE per calcolare lo stipendio medio per ogni dipartimento, e poi abbiamo selezionato da questa CTE per visualizzare i risultati. È come se avessimo creato una tabella temporanea con gli stipendi medi, che abbiamo poi utilizzato nella nostra query principale.

CTE da Tabelle Multiple

Le CTE non sono limitate a una sola tabella. Possiamo utilizzare più tabelle nelle nostre definizioni di CTE, proprio come nelle query regolari. Aggiungiamo una tabella dei dipartimenti al nostro esempio:

CREATE TABLE dipartimenti (
id INT PRIMARY KEY,
nome VARCHAR(50),
sede VARCHAR(50)
);

INSERT INTO dipartimenti VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finanza', 'Chicago');

Ora, utilizziamo una CTE per combinare informazioni da entrambe le tabelle:

WITH info_dipartimenti AS (
SELECT d.nome AS dipartimento,
AVG(e.stipendio) AS stipendio_medio,
d.sede
FROM dipendenti e
JOIN dipartimenti d ON e.dipartimento = d.nome
GROUP BY e.dipartimento, d.sede
)
SELECT * FROM info_dipartimenti
ORDER BY stipendio_medio DESC;

Questo ci darà:

| dipartimento | stipendio_medio | sede          |
|--------------|-----------------|---------------|
| IT           | 62500.00        | San Francisco |
| Finanza      | 55000.00        | Chicago       |
| HR           | 51000.00        | New York      |

In questo esempio, la nostra CTE unisce le tabelle dei dipendenti e dei dipartimenti, calcola lo stipendio medio e include le informazioni sulla sede.

CTE Ricorsiva

Ora, entriamo nel mondo delle CTE ricorsive! Queste sono come le bambole russe dell'SQL mondo. Una CTE ricorsiva si riferisce a se stessa, permettendo di lavorare con dati gerarchici o strutturati come alberi.

Creiamo un esempio semplice di gerarchia dei dipendenti:

CREATE TABLE gerarchia_dipendenti (
id INT PRIMARY KEY,
nome VARCHAR(50),
manager_id INT
);

INSERT INTO gerarchia_dipendenti VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

Ora, utilizziamo una CTE ricorsiva per visualizzare l'intera gerarchia:

WITH RECURSIVE gerarchia_dip AS (
SELECT id, nome, manager_id, 0 AS livello
FROM gerarchia_dipendenti
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.nome, e.manager_id, gd.livello + 1
FROM gerarchia_dipendenti e
JOIN gerarchia_dip gd ON e.manager_id = gd.id
)
SELECT * FROM gerarchia_dip
ORDER BY livello, id;

Questa query produrrà:

| id | nome       | manager_id | livello |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

Questa CTE ricorsiva parte dal dipendente di livello superiore (Big Boss) e poi ricorsivamente trova tutti i dipendenti che riportano a ciascun manager.

Vantaggi delle CTE

Le CTE offrono diversi vantaggi:

Vantaggio	Descrizione
Leggibilità	Le CTE rendono le query complesse più leggibili, spezzandole in sottoquery con nome.
Riutilizzo	Potete richiamare una CTE più volte all'interno di una query.
Ricorsione	Le CTE permettono di scrivere query ricorsive, ideali per dati gerarchici.
Semplicità	Possono semplificare le join e le sottoquery complesse.
Manutenzione	Le CTE rendono le query più facili da mantenere e modificare.

Svantaggi delle CTE

Sebbene le CTE siano potenti, hanno anche alcune limitazioni:

Svantaggio	Descrizione
Prestazioni	In alcuni casi, le CTE potrebbero non avere le stesse prestazioni delle tabelle derivate o delle viste.
Portata	Le CTE sono valide solo all'interno della portata della singola istruzione in cui sono definite.
Complessità	Per query molto semplici, l'uso di una CTE potrebbe aggiungere una complessità inutile.
Supporto del Database	Non tutti i sistemi di database supportano le CTE, anche se la maggior parte dei moderni li fa.

Ecco tutto, ragazzi! Abbiamo viaggiato attraverso il mondo delle Espressioni di Tabella Comune, dai concetti di base alle query ricorsive. Ricordate, come ogni nuova abilità, padroneggiare le CTE richiede pratica. Quindi non abbiate paura di sperimentare con esse nelle vostre query. Prima di sapere, scriverete CTE come un professionista, impressionando i vostri colleghi e rendendo le vostre query di database più pulite ed efficienti. Buon querying!

Credits: Image by storyset