0Pricing
SQL Academy · Lezione

Sottoquery correlate

Una sottoquery che dipende dalla riga esterna

Sottoquery correlate è una lezione SQL Academy 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 Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è una sottoquery correlata?

Una sottoquery correlata è una sottoquery che fa riferimento a una colonna della query esterna, cioè quella che la contiene. A differenza di una sottoquery normale, che viene eseguita una volta e restituisce un risultato fisso, una sottoquery correlata viene valutata una volta per ogni riga elaborata dalla query esterna.

Questo la rende efficace per i confronti riga per riga, ma anche più costosa rispetto alle sottoquery semplici.

Sottoquery semplici e correlate

La differenza principale è la seguente: una sottoquery normale non fa riferimento alla query esterna e può essere eseguita autonomamente. Una sottoquery correlata dipende dalla riga esterna: è possibile vedere l'alias della tabella esterna all'interno della sottoquery.

Nell'esempio seguente, la SELECT interna fa riferimento a e1.department_id della query esterna, creando la correlazione.

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Configurazione delle tabelle di esempio

Vengono create due tabelle che verranno utilizzate in tutta la lezione: employees e departments. Queste tabelle forniscono dati realistici per illustrare le sottoquery correlate in diversi scenari.

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

Dipendenti con uno stipendio superiore alla media del proprio reparto

Un caso d'uso classico delle sottoquery correlate consiste nell'individuare ogni dipendente il cui stipendio supera lo stipendio medio del proprio reparto. La query interna ricalcola la media del reparto per ogni riga relativa a un dipendente nella query esterna.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

Usare sottoquery correlate in SELECT

Le sottoquery correlate non sono limitate alla clausola WHERE: possono comparire anche nella lista SELECT per calcolare un valore per ogni riga. Qui si recupera lo stipendio di ogni dipendente insieme allo stipendio medio del relativo reparto, tutto in un'unica query.

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

Trovare il dipendente con lo stipendio più alto per reparto

È possibile usare una sottoquery correlata per trovare il dipendente con lo stipendio massimo in ogni reparto. La query interna individua lo stipendio massimo per il reparto della riga corrente, mentre la query esterna mantiene solo la riga corrispondente.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

EXISTS con una sottoquery correlata

L'operatore EXISTS viene spesso usato insieme alle sottoquery correlate. Restituisce TRUE se la query interna produce almeno una riga. Qui vengono elencati tutti i reparti con almeno un dipendente assunto prima del 2021.

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

NOT EXISTS con una sottoquery correlata

NOT EXISTS è l'opposto: restituisce TRUE quando la sottoquery correlata non trova righe corrispondenti. È utile per trovare record principali privi di record secondari, ad esempio reparti senza dipendenti.

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

Sottoquery correlata in UPDATE

Le sottoquery correlate funzionano anche all'interno delle istruzioni UPDATE. L'esempio seguente aggiunge una colonna dept_avg e usa quindi una sottoquery correlata per riempirla con lo stipendio medio del reparto di ciascun dipendente.

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

Sottoquery correlata in DELETE

È possibile usare una sottoquery correlata anche in un'istruzione DELETE per rimuovere righe in base ai dati di una tabella correlata. La query seguente elimina i dipendenti il cui stipendio è inferiore al 60% della media del proprio reparto: un modello di pulizia dei dati.

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

Suggerimento sulle prestazioni: sottoquery correlate e JOIN

Le sottoquery correlate vengono eseguite una volta per ogni riga esterna, quindi possono essere lente su tabelle di grandi dimensioni. Molte sottoquery correlate possono essere riscritte come una JOIN con una tabella derivata o una CTE per ottenere prestazioni migliori. È importante conoscere entrambi gli schemi e scegliere in base alla leggibilità e al piano di esecuzione.

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

Verifica rapida

Metta alla prova la propria comprensione delle sottoquery correlate.

Riepilogo: sottoquery correlate

In questa lezione ha appreso che una sottoquery correlata fa riferimento a una colonna della query esterna e viene rivalutata per ogni riga esterna. Punti chiave:

  • Può comparire in SELECT, WHERE, UPDATE e DELETE.
  • EXISTS / NOT EXISTS si combinano naturalmente con le sottoquery correlate per verificare la presenza di righe correlate.
  • Sono espressive, ma possono essere lente: quando le prestazioni sono importanti, valuti di riscriverle come una JOIN.
  • È l'alias della tabella esterna all'interno della sottoquery a creare la correlazione.

Domande Frequenti

La lezione «Sottoquery correlate» è gratuita?

Sì — il testo completo di «Sottoquery correlate» è 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 Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Sottoquery correlate»?

Una sottoquery che dipende dalla riga esterna Eserciti SQL Academy 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 Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy 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 «Sottoquery correlate»?

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 Academy?

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

  1. Sottoquery correlate
  2. EXISTS e NOT EXISTS
  3. IN a confronto con ANY e ALL
  4. Prestazioni di EXISTS e JOIN
← Torna a SQL Academy