Uso dei subquery in SELECT

Vorrei tornare ancora una volta sull’argomento dei subquery in SELECT. Soprattutto sul fatto che la query interna può riferirsi ai dati della query esterna. Sembra tutto semplice, ma non proprio. Dai, approfondiamo ancora un po’ questa cosa...

I subquery in SELECT ti permettono di aggiungere colonne extra con valori calcolati o dati che dipendono da altri record o tabelle. Per esempio, puoi mostrare una lista di studenti con la loro media voti, il numero di corsi a cui sono iscritti o il voto massimo attuale nel gruppo. È utile quando vuoi analizzare i dati “al volo”, creando colonne riassuntive senza pre-elaborare i dati.

Basi dei subquery in SELECT

Prima di passare agli esempi, vediamo la sintassi generale. I subquery in SELECT si scrivono così:

SELECT column1,
       column2,
       (SELECT aggregazione_o_condizione FROM altra_tabella WHERE condizione) AS nuovo_nome_colonna
FROM tabella_principale;

Nota che il subquery restituisce un solo valore, che appare nel risultato come una nuova colonna. E condizione può riferirsi alle colonne della tabella_principale.

Esempio 1: Aggiungere la media voti dello studente

Partiamo da una query semplice e utile: abbiamo una tabella students e una tabella grades dove sono salvati i voti degli studenti.

Tabella students:

Tabella grades:

Ora vogliamo ottenere la lista degli studenti con i loro nomi e la media dei voti. Per farlo usiamo un subquery in SELECT:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

Risultato:

Qui il subquery (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) calcola la media dei voti per ogni studente. Restituisce un valore per ogni riga della tabella students, ed è comodo quando non vuoi fare un JOIN o creare viste intermedie.

Esempio 2: Contare il numero di corsi per ogni studente

Adesso aggiungiamo info sugli studenti: quanti corsi stanno seguendo. Per questo abbiamo altre tabelle:

Tabella enrollments:

Mostriamo la lista degli studenti con il numero di corsi a cui sono iscritti:

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- riferimento alla tabella students della query esterna
FROM students s;

Risultato:

Il subquery (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) conta il numero di record nella tabella enrollments per ogni studente.

Aggregazione dei dati nei subquery

Spesso i subquery in SELECT vengono usati per calcolare dati aggregati. Funzioni come AVG, SUM, COUNT, MAX, MIN ti permettono di lavorare sui dati direttamente dentro altre query.

Esempio 3: Somma totale dei voti dello studente

Aggiungiamo il totale dei voti per ogni studente. Usiamo un subquery che somma tutti i voti dalla tabella grades:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Risultato:

Questo subquery (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) somma i voti di ogni studente. Se uno studente non ha voti, il risultato sarà NULL, perché SUM restituisce NULL se non ci sono valori.

Limitazioni e consigli

1. Performance. I subquery in SELECT vengono eseguiti separatamente per ogni riga della tabella principale. Questo può rallentare molto su grandi quantità di dati. Se puoi, sostituiscili con JOIN o usa dati aggregati già pronti. Per esempio:

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

Questo approccio con JOIN è più ottimizzato, perché il raggruppamento e il conteggio vengono fatti una sola volta.

2. Problemi con NULL.

Se nel subquery non ci sono dati, il risultato sarà NULL. Può essere inaspettato. Esempio:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Se per uno studente non ci sono record in grades, il risultato total_grade sarà NULL. Per sostituire NULL con 0, usa la funzione COALESCE:

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

Sì, qui come primo parametro della funzione COALESCE passiamo

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

Ottimizzazione dei subquery in SELECT

Per evitare calcoli inutili e migliorare la performance:

1. Usa indici sulle colonne coinvolte nei subquery. Per esempio, indicizzare student_id nella tabella grades velocizza il filtraggio.
2. Sostituisci i subquery con dati aggregati già pronti usando JOIN, se puoi.
3. Limita la quantità di dati elaborati dai subquery usando filtri (WHERE).

Esempio finale: combinare i subquery

Mettiamo insieme tutto quello che abbiamo visto e creiamo una query che mostra il nome dello studente, la media voti, il numero di corsi e la somma totale dei voti:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Questa query restituisce il profilo completo dello studente, costruito grazie alla potenza dei subquery. Vediamo la media e la somma dei voti, e anche il numero di corsi a cui ogni studente è iscritto. Una struttura così è un ottimo modo per ottenere info aggregate al volo senza creare VIEW separate o fare JOIN complicati.