MENU
  1. ホーム
  2. SQL
  3. Come scrivere query SQL combinando più sottoquery

Come scrivere query SQL combinando più sottoquery

SQL

Le sottoquery SQL sono una potente tecnica per eseguire una query all’interno di un’altra query. In particolare, combinando efficacemente più sottoquery, è possibile eseguire estrazioni e analisi di dati complesse. In questo articolo, partiremo dai fondamenti delle sottoquery, esploreremo le sottoquery correlate e la combinazione di più sottoquery, e forniremo esempi pratici per mostrare come scrivere query SQL utilizzando le sottoquery. Infine, offriremo suggerimenti per ottimizzare le prestazioni. Se desideri migliorare le tue competenze SQL, continua a leggere.

Indice

Fondamenti delle sottoquery

Una sottoquery è una query SQL inclusa all’interno di un’altra query SQL. Le sottoquery sono utilizzate per fornire dati alla query principale, e il loro risultato viene utilizzato all’interno della query principale. Di seguito è riportato un esempio di sottoquery di base.

Struttura di base

Una sottoquery di base è utilizzata all’interno di una dichiarazione SELECT ed è racchiusa tra parentesi. Ad esempio, una sottoquery per trovare il dipendente con lo stipendio più alto potrebbe essere scritta così:

SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Tipi di sottoquery

Esistono diversi tipi di sottoquery:

  • Sottoquery a singola riga: una sottoquery che restituisce un solo risultato.
  • Sottoquery a più righe: una sottoquery che restituisce più risultati.
  • Sottoquery correlate: una sottoquery che dipende da ciascuna riga della query principale.

Comprendere i fondamenti delle sottoquery è il primo passo per creare query più complesse. Proseguendo, esploreremo più dettagliatamente le sottoquery correlate.

Sottoquery correlate

Le sottoquery correlate vengono eseguite per ciascuna riga della query principale e fanno riferimento alle colonne della query principale. Questo consente estrazioni di dati più dinamiche e flessibili.

Concetto di sottoquery correlata

Le sottoquery correlate dipendono da ciascuna riga della query principale, creando una relazione reciproca tra la query principale e la sottoquery. Questo permette di eseguire confronti e aggregazioni di dati più complessi.

Struttura di base delle sottoquery correlate

La struttura di base di una sottoquery correlata è la seguente. Nell’esempio qui sotto, vengono estratti i dipendenti il cui stipendio è superiore alla media del dipartimento di appartenenza.

SELECT employee_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

In questa query, la query esterna (principale) e la query interna (sottoquery) sono collegate tramite il department_id, verificando se lo stipendio di ciascun dipendente supera la media del dipartimento.

Vantaggi delle sottoquery correlate

Le sottoquery correlate offrono i seguenti vantaggi:

  • Flessibilità: permettono di impostare condizioni complesse e di eseguire calcoli o confronti differenti per ciascuna riga della query principale.
  • Estrazione dinamica dei dati: generano risultati dinamici basati sui dati della query principale.

Successivamente, vedremo come combinare più sottoquery per eseguire estrazioni di dati complesse.

Come combinare più sottoquery

Combinando più sottoquery, è possibile eseguire estrazioni di dati estremamente dettagliate e complesse. Di seguito viene spiegato come utilizzare efficacemente più sottoquery.

Sottoquery nidificate

Le sottoquery possono essere ulteriormente nidificate all’interno di altre sottoquery per eseguire estrazioni di dati gerarchiche. Nell’esempio seguente, si ottiene il nome del dipartimento a cui appartiene il dipendente con lo stipendio più alto.

SELECT department_name
FROM departments
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE salary = (
        SELECT MAX(salary)
        FROM employees
    )
);

Questa query trova prima il dipendente con lo stipendio più alto e poi ottiene il nome del dipartimento a cui appartiene.

Combinazione di sottoquery correlate

È anche possibile combinare sottoquery correlate con altre sottoquery. Nell’esempio seguente, si verifica se lo stipendio di ciascun dipendente supera la media del dipartimento e si ottiene il nome del dipartimento corrispondente.

SELECT employee_name, department_name
FROM employees e1
JOIN departments d ON e1.department_id = d.department_id
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

Questa query utilizza una sottoquery correlata per verificare se lo stipendio di ciascun dipendente supera la media del dipartimento e, in base a questo risultato, ottiene il nome del dipartimento.

Sottoquery con più clausole WITH

Quando si utilizzano più sottoquery, l’uso della clausola WITH (Common Table Expression, CTE) può rendere la query più leggibile e facile da mantenere. Ecco un esempio che utilizza CTE.

WITH MaxSalary AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
),
HighEarners AS (
    SELECT e.employee_name, d.department_name, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN MaxSalary m ON e.department_id = m.department_id AND e.salary = m.max_salary
)
SELECT * FROM HighEarners;

Questa query ottiene inizialmente i dipendenti con lo stipendio più alto in ciascun dipartimento e quindi seleziona il nome del dipendente e del dipartimento.

Successivamente, vedremo un esempio pratico che mostra come utilizzare le sottoquery nidificate.

Esempio pratico 1: Sottoquery nidificate

Le sottoquery nidificate sono utilizzate per eseguire estrazioni di dati gerarchiche. In questa sezione, spiegheremo come utilizzare le sottoquery nidificate attraverso un esempio pratico.

Esempio: Ottenere il dipendente con lo stipendio più alto in un dipartimento specifico

In questo esempio, otterremo il dipendente con lo stipendio più alto in un dipartimento specifico (ad esempio, con department_id pari a 5).

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = 5
);

Questa query prima trova lo stipendio più alto tra i dipendenti del dipartimento con id pari a 5 e poi utilizza questo risultato per ottenere il nome e lo stipendio del dipendente corrispondente.

Esempio: Ottenere il dipendente con lo stipendio più alto in ciascun dipartimento

In un esempio più complesso, vediamo come ottenere il dipendente con lo stipendio più alto in ciascun dipartimento.

SELECT employee_name, department_id, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

Questa query utilizza una sottoquery correlata per trovare il dipendente con lo stipendio più alto in ciascun dipartimento. La sottoquery interna recupera lo stipendio massimo basato sul department_id passato dalla query esterna, che poi seleziona il dipendente corrispondente.

Esempio: Ottenere i dettagli del dipendente con lo stipendio più alto in ciascun dipartimento

Infine, vedremo come ottenere i dettagli del dipendente con lo stipendio più alto in ciascun dipartimento, includendo il nome del dipendente, lo stipendio e il nome del dipartimento.

SELECT e1.employee_name, e1.salary, d.department_name
FROM employees e1
JOIN departments d ON e1.department_id = d.department_id
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Questa query trova prima il dipendente con lo stipendio massimo in ciascun dipartimento, quindi ottiene i dettagli del dipendente corrispondente, inclusi il nome del dipendente, lo stipendio e il nome del dipartimento.

Successivamente, vedremo come organizzare sottoquery complesse utilizzando la clausola WITH.

Esempio pratico 2: Sottoquery con la clausola WITH

L’utilizzo della clausola WITH (Common Table Expression, CTE) consente di organizzare query complesse in modo più chiaro e manutenibile. In questa sezione, vedremo come semplificare le sottoquery utilizzando la clausola WITH con esempi pratici.

Esempio: Ottenere il dipendente con lo stipendio più alto in ciascun dipartimento

Vediamo prima come ottenere il dipendente con lo stipendio più alto in ciascun dipartimento utilizzando la clausola WITH.

WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_name, e.salary, d.department_name
FROM employees e
JOIN MaxSalaries m ON e.department_id = m.department_id AND e.salary = m.max_salary
JOIN departments d ON e.department_id = d.department_id;

In questa query, utilizziamo un CTE chiamato MaxSalaries per calcolare lo stipendio massimo in ciascun dipartimento, che viene poi utilizzato nella query principale. Questo rende l’intera query più leggibile.

Esempio: Ottenere il miglior venditore per vendite totali

Successivamente, vediamo un esempio di come ottenere i dettagli del venditore con le vendite totali più elevate.

WITH SalesData AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
),
TopSalesperson AS (
    SELECT salesperson_id, MAX(total_sales) AS max_sales
    FROM SalesData
)
SELECT s.salesperson_name, sd.total_sales
FROM SalesData sd
JOIN TopSalesperson ts ON sd.salesperson_id = ts.salesperson_id AND sd.total_sales = ts.max_sales
JOIN salespersons s ON sd.salesperson_id = s.salesperson_id;

In questa query, utilizziamo un CTE chiamato SalesData per calcolare le vendite totali di ciascun venditore, e un secondo CTE chiamato TopSalesperson per identificare il venditore con le vendite più elevate. Infine, recuperiamo i dettagli del venditore corrispondente.

Esempio: Ottenere la media delle vendite mensili per un anno specifico

Infine, vediamo come ottenere la media delle vendite mensili per un anno specifico utilizzando la clausola WITH.

WITH MonthlySales AS (
    SELECT DATE_TRUNC('month', sale_date) AS month, AVG(sales_amount) AS avg_sales
    FROM sales
    WHERE EXTRACT(year FROM sale_date) = 2023
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT month, avg_sales
FROM MonthlySales
ORDER BY month;

In questa query, utilizziamo un CTE chiamato MonthlySales per calcolare la media delle vendite mensili per l’anno 2023, e poi usiamo questo risultato nella query principale. Questo rende facile ottenere la media delle vendite per ogni mese.

Successivamente, introdurremo suggerimenti e tecniche per ottimizzare le prestazioni delle query SQL che utilizzano sottoquery.

Ottimizzazione delle prestazioni

Le query SQL che utilizzano sottoquery sono potenti, ma possono anche riscontrare problemi di prestazioni. In questa sezione, introdurremo suggerimenti e tecniche per ottimizzare le prestazioni delle query SQL che utilizzano sottoquery.

Utilizzo degli indici

Creare indici sulle colonne utilizzate nelle sottoquery può migliorare significativamente la velocità di esecuzione delle query. È particolarmente importante creare indici sulle colonne utilizzate frequentemente nelle sottoquery.

CREATE INDEX idx_department_id ON employees(department_id);

In questo esempio, viene creato un indice sulla colonna department_id per velocizzare la ricerca.

Evitare sottoquery inutili

Alcune sottoquery possono essere ridondanti e possono essere semplificate utilizzando JOIN. Eliminare sottoquery non necessarie può migliorare le prestazioni delle query.

-- Esempio con sottoquery
SELECT e.employee_name, d.department_name
FROM employees e
WHERE e.department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

-- Esempio ottimizzato con JOIN
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

In questo esempio, la sottoquery è stata sostituita con un JOIN, rendendo la query più semplice e veloce.

Ottimizzazione con EXISTS

Quando il risultato di una sottoquery viene utilizzato per un controllo di esistenza, l’operatore EXISTS può migliorare le prestazioni. EXISTS termina l’elaborazione non appena viene trovata una riga corrispondente, rendendolo efficiente.

-- Esempio con sottoquery
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- Esempio ottimizzato con EXISTS
SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE e.department_id = d.department_id AND d.location = 'New York'
);

In questo esempio, l’operatore IN è stato sostituito con EXISTS, migliorando l’efficienza della query.

Utilizzo delle viste

Quando si eseguono frequentemente query che includono sottoquery complesse, la trasformazione di tali sottoquery in viste può migliorare le prestazioni. Le viste salvano i risultati delle query come tabelle virtuali, rendendone facile il riutilizzo.

-- Creazione di una vista
CREATE VIEW HighSalaryEmployees AS
SELECT employee_name, salary, department_id
FROM employees
WHERE salary > 100000;

-- Query utilizzando la vista
SELECT e.employee_name, d.department_name
FROM HighSalaryEmployees e
JOIN departments d ON e.department_id = d.department_id;

In questo esempio, viene creata una vista chiamata HighSalaryEmployees, che viene poi utilizzata per migliorare la leggibilità e le prestazioni della query.

Aggiornamento delle statistiche

Aggiornare regolarmente le statistiche del database consente al query optimizer di generare piani di esecuzione ottimali. Le statistiche includono informazioni sugli indici e sulla cardinalità delle tabelle.

-- Aggiornamento delle statistiche (esempio: PostgreSQL)
ANALYZE employees;

In questo esempio, vengono aggiornate le statistiche della tabella employees per ottimizzare le prestazioni della query.

Conclusione

In questo articolo abbiamo esaminato come scrivere query SQL combinando più sottoquery. Abbiamo trattato i fondamenti delle sottoquery, le sottoquery correlate, come combinare più sottoquery, esempi pratici di sottoquery nidificate e con la clausola WITH, e tecniche per ottimizzare le prestazioni. Utilizzando queste tecniche, sarai in grado di creare query SQL più efficienti e potenti. Migliora le tue competenze SQL e applica queste tecniche per eseguire estrazioni e analisi di dati complesse.

SQL
Clausola WITH Competenze SQL Database Estrazione dei Dati indice ottimizzazione delle prestazioni Ottimizzazione delle Query Sottoquery Sottoquery Correlata Sottoquery Nidificata SQL Visualizzazione
Indice