🔍 Le Subquery in SQL

1. Cos'è una Subquery?

Una subquery (o "query annidata") è semplicemente una query dentro un'altra query. È racchiusa tra parentesi tonde ( ) e viene eseguita per prima.

La struttura base

SELECT colonne
FROM tabella
WHERE colonna operatore (SELECT ... FROM ... WHERE ...);
                          ↑ questa è la SUBQUERY

Analogia per capire meglio

Pensa alla subquery come a una domanda preparatoria:

-- La domanda "Chi guadagna più della media?" diventa:
SELECT nome, stipendio
FROM dipendenti
WHERE stipendio > (SELECT AVG(stipendio) FROM dipendenti);
                   ↑ calcola prima la media (es: 35000)
                     poi cerca chi ha stipendio > 35000

2. Quando servono le Subquery?

Le subquery risolvono problemi che non puoi risolvere in un solo passaggio. Ecco i casi tipici:

3. Subquery nella clausola WHERE

È il caso più comune. La subquery calcola un valore (o un insieme di valori) che poi viene usato per filtrare.

Caso A: Subquery che restituisce UN SOLO valore (scalare)

-- Chi guadagna più della media aziendale?
SELECT nome, stipendio
FROM dipendenti
WHERE stipendio > (SELECT AVG(stipendio) FROM dipendenti);

-- Come funziona:
-- 1. La subquery calcola: AVG(stipendio) = 35000
-- 2. La query diventa: SELECT nome, stipendio FROM dipendenti WHERE stipendio > 35000

-- Chi ha lo stipendio più alto?
SELECT nome, stipendio
FROM dipendenti
WHERE stipendio = (SELECT MAX(stipendio) FROM dipendenti);

-- ATTENZIONE: questa trova TUTTI quelli con lo stipendio massimo
-- (potrebbero essere più di uno se ci sono più persone con lo stipendio massimo)

-- Dipendenti del reparto "Vendite" (senza usare JOIN ma se si chiedono di visualizzare dati di più tabelle il JOIN è necessario)
SELECT nome, stipendio
FROM dipendenti
WHERE reparto_id = (
    SELECT id 
    FROM reparti 
    WHERE nome = 'Vendite'
);

Caso B: Subquery che restituisce PIÙ valori (lista)

-- Dipendenti che lavorano in reparti con budget > 100000
SELECT nome, stipendio
FROM dipendenti
WHERE reparto_id IN (
    SELECT id 
    FROM reparti 
    WHERE budget > 100000
);

-- Come funziona:
-- 1. La subquery trova gli id dei reparti ricchi: (1, 3, 7)
-- 2. La query diventa: WHERE reparto_id IN (1, 3, 7)

-- Clienti che NON hanno mai fatto ordini
SELECT nome
FROM clienti
WHERE id NOT IN (
    SELECT DISTINCT cliente_id 
    FROM ordini
);

-- Trova tutti i cliente_id presenti in ordini
-- Poi restituisce i clienti che NON sono in quella lista

4. Operatori speciali: IN, ANY, ALL, EXISTS

4.1 - Operatore IN

Già visto sopra. Verifica se un valore è presente in una lista.

WHERE colonna IN (subquery)     -- valore presente nella lista
WHERE colonna NOT IN (subquery) -- valore NON presente nella lista

4.2 - Operatori ANY e ALL

Permettono confronti con tutti o almeno uno dei valori restituiti.

-- Dipendenti con stipendio maggiore
-- di ALMENO UN manager
SELECT nome, stipendio
FROM dipendenti
WHERE stipendio > ANY (
  SELECT stipendio 
  FROM dipendenti 
  WHERE ruolo = 'manager'
)

-- Dipendenti con stipendio superiore
-- a TUTTI i manager
SELECT nome, ruolo
FROM dipendenti
WHERE stipendio > ALL (
  SELECT stipendio 
  FROM dipendenti 
  WHERE ruolo = 'manager'
)

4.3 - Operatore EXISTS

EXISTS verifica semplicemente se la subquery restituisce almeno una riga. Non importa cosa restituisce, solo SE restituisce qualcosa.

-- Trova i clienti che hanno fatto almeno un ordine
SELECT nome
FROM clienti c
WHERE EXISTS (
    SELECT 1 
    FROM ordini o 
    WHERE o.cliente_id = c.id
);

-- SELECT 1 è una convenzione: significa "non mi interessa
-- cosa restituisci, solo SE restituisci qualcosa"

-- Clienti che NON hanno mai ordinato nulla
SELECT nome
FROM clienti c
WHERE NOT EXISTS (
    SELECT 1 
    FROM ordini o 
    WHERE o.cliente_id = c.id
);

5. Subquery nella clausola FROM (Derived Tables)

Puoi usare una subquery come se fosse una tabella temporanea. La subquery va nella clausola FROM e DEVE avere un alias.

-- Qual è lo stipendio medio per reparto, e poi la media di queste medie?

-- ERRORE: non puoi fare AVG(AVG(stipendio)) direttamente!
-- SOLUZIONE: subquery nel FROM

SELECT AVG(media_reparto) AS media_delle_medie
FROM (
    SELECT reparto_id, AVG(stipendio) AS media_reparto
    FROM dipendenti
    GROUP BY reparto_id
) AS medie_per_reparto;
   ↑ OBBLIGATORIO dare un alias alla subquery!

-- Reparti dove la media stipendi è sopra la media aziendale
SELECT reparto_id, media_stipendio
FROM (
    SELECT reparto_id, AVG(stipendio) AS media_stipendio
    FROM dipendenti
    GROUP BY reparto_id
) AS stats_reparto
WHERE media_stipendio > (SELECT AVG(stipendio) FROM dipendenti);

-- SBAGLIATO (manca alias):
SELECT * FROM (SELECT ... FROM ...);

-- CORRETTO:
SELECT * FROM (SELECT ... FROM ...) AS nome_alias;

6. Subquery nella clausola SELECT (Scalar Subquery)

Puoi inserire una subquery come "colonna calcolata". DEVE restituire esattamente un valore per ogni riga.

-- Mostra ogni dipendente e quanto guadagna rispetto alla media
SELECT 
    nome,
    stipendio,
    (SELECT AVG(stipendio) FROM dipendenti) AS media_aziendale,
    stipendio - (SELECT AVG(stipendio) FROM dipendenti) AS differenza
FROM dipendenti;

-- Ogni ordine con la sua percentuale sul totale vendite
SELECT 
    id,
    importo,
    ROUND(
        importo * 100.0 / (SELECT SUM(importo) FROM ordini), 
        2
    ) AS percentuale_totale
FROM ordini;

7. 🔗 Subquery Correlate

Questa sezione è fondamentale perché le subquery correlate sono uno degli strumenti più potenti (e meno compresi) di SQL.

7.1 - La differenza fondamentale

-- Subquery eseguita UNA sola volta
SELECT nome
FROM dipendenti
WHERE stipendio > (
    SELECT AVG(stipendio) 
    FROM dipendenti
);

-- Subquery eseguita PER OGNI dipendente
SELECT nome
FROM dipendenti d1
WHERE stipendio > (
    SELECT AVG(d2.stipendio) 
    FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id
);

7.2 - Data una subquery, come riconoscere se è una subquery correlata?

SELECT * 
FROM tabella1 t1        ← t1 definito QUI (nella query esterna)
WHERE colonna > (
    SELECT ... 
    FROM tabella2 t2
    WHERE t2.x = t1.y   ← t1 USATO QUI (dentro la subquery)
);                               ↑ QUESTA È UNA CORRELAZIONE!

7.3 - QUANDO usare le subquery correlate

Le subquery correlate sono necessarie (non opzionali) in queste situazioni:

7.4 - ESEMPI CONCRETI E DETTAGLIATI

📌 Esempio 1: Dipendenti sopra la media DEL PROPRIO reparto

Problema: "Trova i dipendenti che guadagnano più della media del loro reparto."

Perché serve correlata? Perché la media da confrontare è diversa per ogni dipendente - dipende dal suo reparto.

-- La query completa:
SELECT d1.nome, d1.stipendio, d1.reparto_id
FROM dipendenti d1
WHERE d1.stipendio > (
    SELECT AVG(d2.stipendio)
    FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id  -- CORRELAZIONE!
);

-- NOTA: d1 e d2 sono alias della STESSA tabella!
-- d1 = la riga che stiamo valutando
-- d2 = tutte le righe per calcolare la media del gruppo

📌 Esempio 2: Il prodotto più costoso DI OGNI categoria

Problema: "Per ogni categoria, mostra il prodotto con il prezzo più alto."

SELECT p1.nome, p1.categoria, p1.prezzo
FROM prodotti p1
WHERE p1.prezzo = (
    SELECT MAX(p2.prezzo)
    FROM prodotti p2
    WHERE p2.categoria = p1.categoria  -- CORRELAZIONE!
);

-- Per ogni prodotto p1:
-- - Calcola il MAX prezzo tra i prodotti della STESSA categoria
-- - Se p1.prezzo = quel MAX, includi p1 nel risultato

📌 Esempio 3: Clienti che hanno fatto ordini (con EXISTS)

Problema: "Trova i clienti che hanno effettuato almeno un ordine."

SELECT c.nome, c.citta
FROM clienti c
WHERE EXISTS (
    SELECT 1
    FROM ordini o
    WHERE o.cliente_id = c.id  
);

-- Per ogni cliente c:
-- - Cerca se esiste almeno un ordine con cliente_id = c.id
-- - Se esiste → includi il cliente nel risultato

📌 Esempio 4: Prodotti MAI venduti (NOT EXISTS)

Problema: "Trova i prodotti che non hanno mai avuto vendite."

SELECT p.nome, p.categoria
FROM prodotti p
WHERE NOT EXISTS (
    SELECT 1
    FROM vendite v
    WHERE v.prodotto_id = p.id  
);

-- Per ogni prodotto p:
-- - Cerca se NON esiste nessuna vendita con prodotto_id = p.id
-- - Se non esiste → includi il prodotto nel risultato

📌 Esempio 5: Percentuale stipendio rispetto al proprio reparto

Problema: "Per ogni dipendente, mostra quanto rappresenta il suo stipendio in percentuale rispetto al totale del suo reparto."

SELECT 
    d1.nome,
    d1.reparto_id,
    d1.stipendio,
    ROUND(
        d1.stipendio * 100.0 / (
            SELECT SUM(d2.stipendio)
            FROM dipendenti d2
            WHERE d2.reparto_id = d1.reparto_id
        ), 2
    ) AS percentuale_reparto
FROM dipendenti d1;

-- Risultato esempio:
-- nome  | reparto_id | stipendio | percentuale_reparto
-- ------|------------|-----------|--------------------
-- Mario | 1          | 40000     | 57.14%
-- Lucia | 1          | 30000     | 42.86%
-- Paolo | 2          | 50000     | 52.63%
-- Anna  | 2          | 45000     | 47.37%

-- Clienti con almeno un ordine
SELECT nome
FROM clienti c
WHERE EXISTS (
    SELECT 1 FROM ordini o
    WHERE o.cliente_id = c.id
);

-- Stesso risultato con JOIN
SELECT DISTINCT c.nome
FROM clienti c
JOIN ordini o 
ON c.id = o.cliente_id;

-- Clienti che non hanno mai effettuato ordini
SELECT nome
FROM clienti c
WHERE NOT EXISTS (
    SELECT 1 FROM ordini o
    WHERE o.cliente_id = c.id
);

-- Stesso risultato con JOIN
SELECT DISTINCT c.nome
FROM clienti c
LEFT JOIN ordini o 
WHERE o.id=NULL 
ON c.id = o.cliente_id;

7.6 - Performance delle subquery correlate

-- INVECE DI (subquery correlata - più lenta):
SELECT d1.nome, d1.stipendio
FROM dipendenti d1
WHERE d1.stipendio > (
    SELECT AVG(d2.stipendio) FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id
);

-- PROVA QUESTA (JOIN con subquery nel FROM - spesso più veloce):
SELECT d.nome, d.stipendio
FROM dipendenti d
join (
    SELECT reparto_id, AVG(stipendio) AS media_rep
    FROM dipendenti
    GROUP BY reparto_id
) AS medie ON d.reparto_id = medie.reparto_id
WHERE d.stipendio > medie.media_rep;

7.7 - Riepilogo: quando usare le correlate

8. 💡 I Trucchi per Riconoscere quando servono le Subquery

Ecco le parole chiave e le situazioni tipiche che indicano la necessità di una subquery:

-- Per subquery NON correlata:
-- PASSO 1: Testa la subquery da sola
SELECT AVG(stipendio) FROM dipendenti;
-- Risultato: 35000 ✓

-- PASSO 2: Inseriscila nella query principale
SELECT nome FROM dipendenti 
WHERE stipendio > (SELECT AVG(stipendio) FROM dipendenti);

-- Per subquery CORRELATA:
-- PASSO 1: Scrivi la struttura esterna
SELECT d1.nome FROM dipendenti d1 WHERE d1.stipendio > (...);

-- PASSO 2: Aggiungi la subquery CON il riferimento a d1
SELECT d1.nome 
FROM dipendenti d1 
WHERE d1.stipendio > (
    SELECT AVG(d2.stipendio) FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id  -- riferimento a d1!
);

9. ⚠️ Errori comuni da evitare

-- NON FUNZIONA!
SELECT nome
FROM dipendenti
WHERE stipendio > AVG(stipendio);

-- FUNZIONA!
SELECT nome
FROM dipendenti
WHERE stipendio > 
  (SELECT AVG(stipendio) FROM dipendenti);

-- ERRORE se ci sono più reparti!
SELECT nome
FROM dipendenti
WHERE reparto_id = (
  SELECT id FROM reparti 
  WHERE budget > 50000
);

-- FUNZIONA con qualsiasi numero!
SELECT nome
FROM dipendenti
WHERE reparto_id IN (
  SELECT id FROM reparti 
  WHERE budget > 50000
);

-- ERRORE di sintassi!
SELECT *
FROM (
  SELECT reparto_id, AVG(stipendio)
  FROM dipendenti
  GROUP BY reparto_id
);

-- FUNZIONA!
SELECT *
FROM (
  SELECT reparto_id, AVG(stipendio) as media
  FROM dipendenti
  GROUP BY reparto_id
) AS t;

-- AMBIGUO: quale tabella?
SELECT nome
FROM dipendenti
WHERE stipendio > (
    SELECT AVG(stipendio) 
    FROM dipendenti
    WHERE reparto_id = reparto_id
);

-- CHIARO: d1 è esterno, d2 interno
SELECT nome
FROM dipendenti d1
WHERE d1.stipendio > (
    SELECT AVG(d2.stipendio) 
    FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id
);

-- Se ordini.cliente_id contiene NULL, questo potrebbe dare 0 risultati!
SELECT * FROM clienti 
WHERE id NOT IN (SELECT cliente_id FROM ordini);

-- SOLUZIONE 1: escludi i NULL
SELECT * FROM clienti 
WHERE id NOT IN (
    SELECT cliente_id FROM ordini 
    WHERE cliente_id IS NOT NULL
);

-- SOLUZIONE 2: usa NOT EXISTS (gestisce meglio i NULL e generalmente è più veloce)
SELECT * FROM clienti c
WHERE NOT EXISTS (
    SELECT 1 FROM ordini o 
    WHERE o.cliente_id = c.id
);

10. 🎯 Esercizi guidati

Prova a risolvere questi esercizi. Per ogni esercizio, prima identifica se serve una subquery e di che tipo.

Esercizio 1: Livello Base

SELECT nome, stipendio 
FROM dipendenti 
WHERE stipendio > 40000;

Esercizio 2: Livello Base - Subquery semplice

SELECT nome, stipendio 
FROM dipendenti 
WHERE stipendio = (SELECT MAX(stipendio) FROM dipendenti);

Esercizio 3: Livello Intermedio - IN

SELECT nome 
FROM clienti 
WHERE id IN (
    SELECT DISTINCT cliente_id 
    FROM ordini 
    WHERE importo > 1000
);

Esercizio 4: Livello Intermedio - NOT EXISTS

-- Con NOT EXISTS :
SELECT nome 
FROM clienti c 
WHERE NOT EXISTS (
    SELECT 1 FROM ordini o 
    WHERE o.cliente_id = c.id
);

Esercizio 5: Livello Avanzato - CORRELATA

SELECT d1.nome, d1.stipendio, d1.reparto_id
FROM dipendenti d1
WHERE d1.stipendio > (
    SELECT AVG(d2.stipendio)
    FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id  -- CORRELAZIONE!
);

Esercizio 6: Livello Avanzato - CORRELATA per MAX per gruppo

SELECT p1.nome, p1.categoria, p1.prezzo
FROM prodotti p1
WHERE p1.prezzo = (
    SELECT MAX(p2.prezzo)
    FROM prodotti p2
    WHERE p2.categoria = p1.categoria  -- CORRELAZIONE!
);

Esercizio 7: Livello Avanzato - CORRELATA nel SELECT

SELECT 
    d1.nome,
    d1.reparto_id,
    (
        SELECT COUNT(*) - 1  -- -1 per escludere se stesso
        FROM dipendenti d2
        WHERE d2.reparto_id = d1.reparto_id
    ) AS numero_colleghi
FROM dipendenti d1;

Esercizio 8: Livello Esperto - Doppia condizione correlata

SELECT d1.nome, d1.reparto_id, d1.stipendio, d1.data_assunzione
FROM dipendenti d1
WHERE d1.stipendio = (
    SELECT MAX(d2.stipendio)
    FROM dipendenti d2
    WHERE d2.reparto_id = d1.reparto_id
)
AND d1.data_assunzione < (
    SELECT AVG(d3.data_assunzione)
    FROM dipendenti d3
    WHERE d3.reparto_id = d1.reparto_id
);

📋 Riepilogo Finale