CTE vs sottoquery: quando scegliere cosa?

Ormai lo sappiamo: le CTE rendono il codice più leggibile. Ma vale sempre la pena usarle? A volte una semplice sottoquery fa il lavoro meglio e più in fretta. Vediamo quando ogni strumento gioca a tuo favore e impariamo a scegliere con criterio.

Sottoquery: veloci e semplici

Ti ricordi già che una sottoquery è SQL dentro SQL. Si infila direttamente nella query principale e viene eseguita “al volo”. Perfetta per operazioni semplici e una tantum:

-- Trova i prodotti più cari della media
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Qui la sottoquery calcola la media una volta sola, e fine. Niente strutture inutili.

Performance: chi è più veloce?

Le sottoquery spesso vincono in velocità per operazioni semplici. PostgreSQL può ottimizzarle “al volo”, soprattutto quando la sottoquery viene eseguita una sola volta:

-- Veloce: la sottoquery viene eseguita una volta sola
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Le CTE di default vengono materializzate — PostgreSQL prima calcola il risultato della CTE, lo salva come tabella temporanea e poi lo usa. Questo può rallentare le query semplici:

-- Più lento: la CTE viene materializzata in una tabella temporanea
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Ma! Da PostgreSQL 12 puoi controllare la materializzazione:

-- Forza a NON materializzare
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Uso ripetuto: qui le CTE spaccano

Quando ti serve lo stesso risultato intermedio più volte, le CTE sono imbattibili:

-- Con la sottoquery: ripeti la stessa logica due volte
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completato') AS ordini_completati,
    (SELECT COUNT(*) FROM orders WHERE status = 'completato') * 100.0 / COUNT(*) AS tasso_completamento
FROM orders;

-- Con la CTE: calcoli una volta, usi due volte
WITH ordini_completati AS (
    SELECT COUNT(*) AS conteggio FROM orders WHERE status = 'completato'
)
SELECT
    oc.conteggio AS ordini_completati,
    oc.conteggio * 100.0 / (SELECT COUNT(*) FROM orders) AS tasso_completamento
FROM ordini_completati oc;

Analisi complesse: CTE vince a mani basse

Per analisi a più step, la CTE trasforma il caos in ordine. Guarda il report sulle vendite:

Con le sottoquery (confusione totale):

SELECT 
    categoria,
    ricavo,
    ricavo * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS quota_ricavo
FROM (
    SELECT 
        p.categoria,
        SUM(p.price * oi.quantity) AS ricavo
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.categoria
) ricavo_categoria;

Con le CTE (tutto ordinato):

WITH vendite_annuali AS (
    SELECT 
        p.categoria,
        p.price * oi.quantity AS importo_vendita
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
ricavo_categoria AS (
    SELECT 
        categoria,
        SUM(importo_vendita) AS ricavo
    FROM vendite_annuali
    GROUP BY categoria
),
ricavo_totale AS (
    SELECT SUM(importo_vendita) AS totale FROM vendite_annuali
)
SELECT 
    rc.categoria,
    rc.ricavo,
    rc.ricavo * 100.0 / rt.totale AS quota_ricavo
FROM ricavo_categoria rc, ricavo_totale rt;

Ricorsione: monopolio delle CTE

Per le strutture gerarchiche le sottoquery non bastano.

Solo le CTE ricorsive risolvono problemi tipo “trovare tutti i subordinati di un manager”:

WITH RECURSIVE gerarchia_dipendenti AS (
    -- Partiamo dal CEO
    SELECT employee_id, manager_id, name, 1 AS livello
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Aggiungiamo i subordinati di ogni livello
    SELECT e.employee_id, e.manager_id, e.name, gd.livello + 1
    FROM employees e
    JOIN gerarchia_dipendenti gd ON e.manager_id = gd.employee_id
)
SELECT * FROM gerarchia_dipendenti ORDER BY livello, name;

Debug e manutenzione del codice

Le CTE si possono facilmente testare a pezzi:

-- Controlliamo il primo step
WITH clienti_attivi AS (
    SELECT customer_id FROM customers WHERE status = 'attivo'
)
SELECT COUNT(*) FROM clienti_attivi; -- Verifica che la logica sia giusta

-- Aggiungiamo il secondo step
WITH clienti_attivi AS (...),
ordini_recenti AS (
    SELECT customer_id, COUNT(*) as numero_ordini
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM ordini_recenti; -- Controlla anche questo step

Le sottoquery sono più difficili da testare — devi estrarle dal contesto.

Consigli pratici

Usa le sottoquery quando:

• La logica è semplice e sta in una riga
• Vuoi la massima performance per operazioni semplici
• Il risultato intermedio serve solo una volta
• Lavori con pochi dati

Usa le CTE quando:

• La query è complessa e si può dividere in step logici
• Devi riutilizzare più volte i risultati intermedi
• Conta la leggibilità e la manutenibilità del codice
• Lavori con gerarchie (CTE ricorsive)
• Fai debug di logiche complesse a pezzi

La regola d’oro

Parti con la sottoquery. Se diventa difficile da leggere o la logica si ripete — passa alle CTE. Il tuo collega del futuro (o tu stesso tra sei mesi) ti ringrazierà!