Risposta.

Storia della questione:

Le sottoquery annidate sono state inizialmente concepite in SQL per ampliare le capacità espressive del linguaggio e risolvere compiti aziendali complessi che non possono essere gestiti con semplici operatori SELECT. Tuttavia, con l'aumento delle dimensioni dei dati e la complessità dei modelli relazionali, è emerso che le sottoquery annidate non sempre funzionano in modo efficiente: molto dipende dall'implementazione dell'ottimizzatore del particolare DBMS.

Problema:

La principale sfida è trovare un compromesso tra leggibilità, correttezza logica e prestazioni. Non sempre la sottoquery annidata viene ottimizzata in operazioni di join e spesso si trasforma in costosi cicli di scansione (Nested Loops).

Soluzione:

Utilizzare sottoquery annidate in SELECT per calcolare aggregati o espressioni complesse, se non si può fare diversamente.
Per grandi volumi di dati, passare a JOIN: questo consente all'ottimizzatore di rendere la query basata su set e applicare indici.
Estrarre la sottoquery nella parte esterna (tramite WITH/CTE), se è necessario aumentare la leggibilità o le prestazioni.

Esempio di codice:

-- Sottoquery annidata in SELECT (con cautela!)
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.client_id = clients.id) AS order_count
FROM clients;

-- Equivalente tramite JOIN (di solito più veloce):
SELECT clients.name, COUNT(orders.id) AS order_count
FROM clients
LEFT JOIN orders ON orders.client_id = clients.id
GROUP BY clients.name;

Caratteristiche chiave:

Le sottoquery correlate annidate portano spesso a prestazioni O(N*M)
Le sottoquery non correlate sono più sicure e più veloci
Esternalizzare la sottoquery in WITH/CTE o JOIN aumenta la prevedibilità del piano e accelera l'esecuzione

Domande insidiose.

La sottoquery annidata in SELECT funziona più velocemente dell'equivalente LEFT JOIN?

Spesso — no. La sottoquery correlata in SELECT viene eseguita per ogni riga della query esterna, mentre il JOIN viene costruito una sola volta con gli indici per l'intera tabella.

È possibile utilizzare una sottoquery in FROM invece di CTE (WITH) e ci sarà differenza?

Sì, una sottoquery in FROM:

SELECT t1.id, sub.agg
FROM table1 t1
JOIN (SELECT id, MAX(val) AS agg FROM table2 GROUP BY id) sub ON t1.id = sub.id;

Ma CTE a volte offre una maggiore leggibilità e può portare a un'altra ottimizzazione nei piani di esecuzione.

Tutte le sottoquery annidate vengono ottimizzate in JOIN analoghi?

No. Non tutti i DBMS sono in grado di farlo in modo uniforme, a volte una sottoquery annidata porta alla scansione di ogni riga, soprattutto se c'è correlazione tra la query esterna e quella interna.

Errori comuni e anti-pattern

Utilizzo di sottoquery correlate annidate in SELECT con grandi volumi.
Duplicazione delle condizioni di filtro nella query interna ed esterna.
Tentativo di sostituire tutte le sottoquery annidate con JOIN senza analisi dei requisiti.

Esempio dalla vita reale

Caso negativo

Un manager delle vendite ha creato un report sui clienti, contando nelle sottoquery interne il numero di ordini. Tempi di esecuzione — minuti, il carico sul server cresceva esponenzialmente.

Vantaggi:

Logica chiara Svantaggi:
Report molto lenti con un gran numero di clienti
Carico inefficiente sul server

Caso positivo

La query è stata riscritta con LEFT JOIN e aggregazione.