Anatomia di una sottoquery correlata
Capire come la query interna fa riferimento alla riga esterna e il modello di esecuzione per riga
Anatomia di una sottoquery correlata è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.
Cosa rende correlata una sottoquery
Gli intervistatori dividono le sottoquery in due categorie. Una sottoquery semplice (non correlata) può essere eseguita autonomamente. Una sottoquery correlata fa riferimento a una colonna della query esterna, quindi non può essere eseguita da sola.
- Non correlata: viene valutata una volta e il risultato viene riutilizzato per ogni riga esterna.
- Correlata: viene rivalutata una volta per ogni riga esterna, perché dipende da quella riga.
Il segnale rivelatore è la presenza di una colonna della tabella esterna all'interno della query interna. Se lo individua, può riconoscere immediatamente il modello.
Il modello di esecuzione per riga
Immagini il motore che scorre le righe esterne. Per ogni riga esterna inserisce i valori di quella riga nella query interna, la esegue e usa il risultato per decidere qualcosa o calcolare un valore.
Questo è il modello mentale che gli intervistatori vogliono sentirle esprimere: «la query interna viene eseguita una volta per ogni riga esterna».
Questa formulazione suggerisce anche la classica domanda successiva: le sottoquery correlate possono essere lente perché la query interna potrebbe essere eseguita migliaia di volte. Risolveremo il problema nella lezione 4.
Individuare il riferimento esterno
Qui la tabella employees e l'alias esterno e1 per il dipendente da valutare guidano una query interna che legge e1.dept_id. Quel riferimento alla riga esterna è la correlazione.
Se rimuove il prefisso dell'alias, la query interna non viene più compilata autonomamente. È proprio questa dipendenza a renderla correlata.
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Leggere ad alta voce quella query
Traduca la query precedente in inglese semplice, come farebbe durante un colloquio:
«Per ogni dipendente e1, trovi la media degli stipendi del suo reparto e mantenga il dipendente solo se guadagna più della media del reparto.»
La clausola WHERE e2.dept_id = e1.dept_id della query interna collega la media al reparto di questo dipendente. Senza quella riga, confronterebbe tutti con la media dell'intera azienda.
Gli alias sono obbligatori
Quando la query interna e quella esterna accedono alla stessa tabella, deve assegnare un alias a entrambe, così il motore sa a quale riga appartiene una colonna.
e1= la riga esterna che viene verificata.e2= la scansione interna della tabella.
Se rimuove gli alias, dept_id diventa ambiguo; molti motori lo associano quindi silenziosamente alla tabella interna, interrompendo la correlazione. Gli intervistatori inseriscono proprio questo errore.
Sottoquery correlata in SELECT
Le sottoquery correlate non sono limitate a WHERE. In una lista SELECT producono una colonna calcolata, valutata nuovamente per ogni riga esterna.
Nell'esempio seguente, ogni ordine mostra quanti altri ordini ha effettuato lo stesso cliente. Il conteggio interno è correlato tramite o.customer_id.
SELECT o.order_id,
o.customer_id,
(SELECT COUNT(*)
FROM orders o2
WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;Scalare significa esattamente un valore
Una sottoquery correlata usata in SELECT o confrontata con =, >, < deve restituire un singolo valore scalare per ogni riga esterna.
Se restituisce più di una riga, il database genera un errore come «la sottoquery restituisce più di una riga».
Gli aggregati come COUNT, MAX o AVG garantiscono un solo valore, per questo sono comuni nelle sottoquery scalari correlate. Conoscere questa regola previene una frequente sorpresa in fase di esecuzione.
Quando la sottoquery restituisce NULL
Una sottoquery scalare correlata può trovare zero righe interne. In tal caso, un aggregato restituisce NULL (oppure, nel caso di COUNT, restituisce 0).
Quel NULL confluisce nell'espressione esterna. I confronti con NULL restituiscono UNKNOWN, quindi la riga esterna potrebbe essere esclusa senza che Lei se ne accorga.
Se ha bisogno di un valore alternativo, racchiuda la sottoquery in COALESCE. Gli intervistatori amano chiedere cosa succede quando nessuna riga interna corrisponde e si aspettano che Lei menzioni il comportamento di NULL.
SELECT c.customer_id,
COALESCE((SELECT MAX(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM customers c;Esempio svolto: data dell'ordine più recente
Un'attività comune: mostrare ogni cliente con la data del suo ordine più recente. Una sottoquery correlata in SELECT lo fa direttamente.
Per ogni riga del cliente, la query interna trova la data massima dell'ordine per quel cliente tramite o.customer_id = c.customer_id.
SELECT c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;Perché può essere lenta
Poiché la query interna viene eseguita una volta per ogni riga esterna, una sottoquery correlata su una tabella esterna di grandi dimensioni può avviare milioni di esecuzioni interne.
- Un indice sulla colonna correlata, qui
orders.customer_id, permette a ogni esecuzione interna di terminare rapidamente. - Senza un indice, ogni esecuzione potrebbe scansionare l'intera tabella, producendo un lavoro approssimativamente pari a O(n*m).
Nei colloqui, citi sempre l'indice e la riscrittura con JOIN come strumenti per migliorare le prestazioni.
Correlata e non correlata a confronto
La differenza consiste in una sola riga. La versione non correlata confronta tutti con la media aziendale; quella correlata confronta ogni persona con il proprio reparto.
Legga entrambe e noti come la singola riga WHERE e2.dept_id = e1.dept_id cambi completamente il significato.
-- Uncorrelated: one global average, computed once
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Correlated: per-department average, recomputed per row
SELECT e1.name FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Verifica rapida
Verifichi di aver compreso che cosa definisce una sottoquery correlata.
Riepilogo: anatomia di una sottoquery correlata
Punti chiave:
- Una sottoquery correlata fa riferimento alla riga esterna e viene eseguita una volta per ogni riga esterna.
- Assegni un alias a entrambe le tabelle quando si tratta della stessa tabella, così la correlazione resta non ambigua.
- L'uso scalare deve restituire esattamente un valore; zero corrispondenze producono NULL, quindi protegga il risultato con
COALESCE. - Può trovarsi in SELECT o WHERE e le prestazioni dipendono dall'indicizzazione della colonna correlata.
Dica «viene eseguita una volta per ogni riga esterna» durante il colloquio e avrà centrato il concetto fondamentale.
Domande Frequenti
La lezione «Anatomia di una sottoquery correlata» è gratuita?
Sì — il testo completo di «Anatomia di una sottoquery correlata» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.
Cosa imparerò in «Anatomia di una sottoquery correlata»?
Capire come la query interna fa riferimento alla riga esterna e il modello di esecuzione per riga Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Interview Prep?
Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.
Quanto tempo richiede la lezione «Anatomia di una sottoquery correlata»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?
Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Anatomia di una sottoquery correlata
- Aggregati per gruppo senza GROUP BY
- EXISTS e NOT EXISTS correlati
- Riscrivere le sottoquery correlate come join