Coding Interview Prep · Lezione

Sottoquery nella clausola FROM (tabelle derivate)

Racchiudere una query in una tabella virtuale e capire perché gli alias sono obbligatori

Sottoquery nella clausola FROM (tabelle derivate) è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 2 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 Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Che cos'è una tabella derivata

Una sottoquery nella clausola FROM è chiamata tabella derivata (o vista inline). Invece di restituire un singolo valore, restituisce un intero set di risultati che la query esterna tratta come se fosse una tabella reale.

Può contenere molte righe e molte colonne.
È possibile interrogarla, usarla in un join e applicarvi filtri come con qualsiasi tabella.

Gli intervistatori usano le tabelle derivate per verificare se sa suddividere un problema in più fasi.

Gli alias sono obbligatori

La principale insidia: una tabella derivata deve avere un alias. Senza alias, la maggior parte dei motori rifiuta la query.

MySQL: Every derived table must have its own alias.
Postgres: subquery in FROM must have an alias.

Le assegni un nome (qui dept_avg) e potrà fare riferimento alle sue colonne usando quel nome.

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

Perché pre-aggregare in una tabella derivata

Un problema frequente nei colloqui è: mostrare ogni dipendente accanto allo stipendio medio del suo reparto. Non è possibile combinare direttamente la riga di dettaglio con un'aggregazione senza incorrere in problemi di raggruppamento.

L'approccio più chiaro consiste nel calcolare la media per reparto in una tabella derivata e poi ricollegarla alle righe di dettaglio. La tabella derivata si riduce prima a una riga per reparto.

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

Filtrare in base al risultato di un'aggregazione

Le tabelle derivate consentono di filtrare in base a un'aggregazione calcolata senza ricorrere a complesse manipolazioni con HAVING nella query esterna. Supponiamo di volere solo i reparti la cui media degli stipendi supera 60000.

Eseguiamo l'aggregazione all'interno, poi applichiamo all'esterno un semplice WHERE sulla colonna derivata. Per la query esterna, avg_salary è una colonna ordinaria.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

Due livelli di aggregazione

Le tabelle derivate sono particolarmente utili quando serve un'aggregazione di un'aggregazione — una richiesta classica nei colloqui: qual è la media delle medie degli stipendi per reparto?

Non è possibile annidare direttamente AVG(AVG(...)). La query interna produce una media per ogni reparto; quella esterna calcola la media di tali valori.

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

Assegnare un nome alle colonne calcolate

Qualsiasi espressione in una tabella derivata richiede un alias se si desidera farvi riferimento all'esterno. Altrimenti, l'espressione interna salary * 12 avrebbe un nome assegnato dal database, sul quale non si può fare affidamento.

Assegni sempre un alias alle colonne calcolate — gli intervistatori notano quando si fa riferimento a un'espressione senza alias presumendo un nome di colonna che potrebbe non esistere.

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

Unire due tabelle derivate

È possibile unire più tabelle derivate. Qui confrontiamo il numero di dipendenti di ogni reparto con il costo totale degli stipendi, unendo due sottoquery pre-aggregate.

Ogni tabella derivata risponde a una sotto-domanda; il join le combina nel report finale. Questo modo di ragionare per fasi è esattamente ciò che viene apprezzato nei colloqui di livello intermedio.

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

Ambito: la query esterna non può vedere all'interno

Una regola importante: la query esterna può fare riferimento solo alle colonne che la tabella derivata espone nel proprio elenco SELECT. Le colonne usate solo all'interno della sottoquery non sono visibili all'esterno.

Se la query interna seleziona dept_id e avg_salary, allora salary o name non sono disponibili all'esterno — sono state utilizzate dall'aggregazione. Gli intervistatori verificano proprio questo limite di visibilità.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

Tabella derivata rispetto a CTE

Una tabella derivata e una Common Table Expression (CTE) spesso producono lo stesso piano di esecuzione. Gli intervistatori potrebbero chiedere perché scegliere l'una o l'altra:

Tabella derivata: inline, adatta a un utilizzo occasionale.
CTE (WITH): definita all'inizio, leggibile e riutilizzabile se viene referenziata più volte.

Per una logica profondamente annidata, una pipeline di CTE si legge dall'alto verso il basso; una tabella derivata si legge dall'interno verso l'esterno.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

La sottoquery LATERAL / correlata in FROM

Normalmente una sottoquery in FROM non può fare riferimento alle righe della query esterna. LATERAL (Postgres) o CROSS APPLY (SQL Server) rimuovono questa restrizione, consentendo alla tabella derivata di essere eseguita per ogni riga esterna.

Questo permette di eseguire ricerche top-N per ogni riga. Conoscere l'esistenza di questa parola chiave dimostra una consapevolezza da sviluppatore senior anche in un colloquio di livello intermedio.

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

Frase da colloquio

Se Le fanno una domanda sulle sottoquery nella clausola FROM, risponda: "Una tabella derivata è una sottoquery nella clausola FROM che restituisce un set di risultati che la query esterna usa come una tabella. Deve avere un alias, la query esterna può vedere solo le colonne che seleziona ed è ideale per pre-aggregare prima di un join o per aggregare un risultato aggregato."

Aggiunga che LATERAL consente di fare riferimento alle righe esterne e avrà coperto ogni aspetto.

Verifica rapida

Scelga l'affermazione che è sempre necessaria per una sottoquery nella clausola FROM.

Riepilogo

Tabelle derivate, concetti acquisiti:

Una sottoquery in FROM restituisce una tabella virtuale — molte righe e molte colonne.
Deve necessariamente avere un alias; la query esterna vede solo le colonne selezionate.
La si usa per pre-aggregare prima di un join, filtrare in base a valori aggregati o aggregare un risultato aggregato.
Una CTE è l'alternativa denominata e più leggibile; LATERAL/CROSS APPLY consentono di fare riferimento alle righe esterne.

Prossimo argomento: le sottoquery per l'appartenenza a un insieme con IN, ANY e ALL.

Domande Frequenti

La lezione «Sottoquery nella clausola FROM (tabelle derivate)» è gratuita?

Sì — il testo completo di «Sottoquery nella clausola FROM (tabelle derivate)» è 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 Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Sottoquery nella clausola FROM (tabelle derivate)»?

Racchiudere una query in una tabella virtuale e capire perché gli alias sono obbligatori Eserciti Coding 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 Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding 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 2 di 4.

Quanto tempo richiede la lezione «Sottoquery nella clausola FROM (tabelle derivate)»?

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 Coding Interview Prep?

Sì. Ogni lezione Coding 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

Sottoquery scalari in SELECT e WHERE
Sottoquery nella clausola FROM (tabelle derivate)
Sottoquery con IN, ANY e ALL
Prestazioni di EXISTS rispetto a IN

← Torna a Coding Interview Prep