Modulo 6 - Sottquery e Query Avanzate

Modulo 6 - Sottquery e Query Avanzate Modulo 6 - Sottquery e Query Avanzate

Spiegazione del Modulo 6: Sottquery e Query Avanzate

Benvenuto al Modulo 6! Qui eleviamo le query SQL a un livello superiore, affrontando problemi complessi che richiedono query "nidificate" o combinate. Le sottquery ti permettono di usare i risultati di una query come input per un'altra, mentre operatori come UNION uniscono set di risultati. Questo è utile per analisi avanzate, come confronti o filtri dinamici. Useremo le tabelle prodotti, clienti e ordini dai moduli precedenti – assicurati di averle popolate con i dati di esempio.

Approfondiamo i punti con sintassi, esempi e consigli. I comandi sono standard; testali nel tuo RDBMS (nota: alcuni feature come CTE sono in SQL:1999+).

1. Sottquery Correlate e Non Correlate

Panoramica: Una sottquery è una query dentro un'altra (tra parentesi). Può essere nel SELECT, FROM, WHERE o HAVING.
Non Correlate: Indipendente – eseguita prima, restituisce un valore fisso.
- Esempio: Trova prodotti più cari della media prezzi.
```
SELECT nome, prezzo
FROM prodotti
WHERE prezzo > (SELECT AVG(prezzo) FROM prodotti);
```
```
SELECT nome, prezzo
FROM prodotti
WHERE prezzo > (SELECT AVG(prezzo) FROM prodotti);
```
  (La sottquery calcola la media una volta; risultato: Laptop, Smartphone, etc.)

Correlate: Dipende dalla query esterna – eseguita per ogni riga esterna (può essere lenta su grandi set).

Esempio: Per ogni ordine, trova se il cliente ha speso più della media per quel prodotto.

SELECT o.id, c.nome, o.quantita * p.prezzo AS spesa
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
WHERE o.quantita * p.prezzo > (SELECT AVG(quantita * prezzo) FROM ordini WHERE prodotto_id = o.prodotto_id);

SELECT o.id, c.nome, o.quantita * p.prezzo AS spesa
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
WHERE o.quantita * p.prezzo > (SELECT AVG(quantita * prezzo) FROM ordini WHERE prodotto_id = o.prodotto_id);

(Correlata su prodotto_id; filtra ordini sopra media per prodotto.)

Consigli: Usa non correlate per efficienza. Evita correlate profonde (usa join se possibile). Testa con EXPLAIN per performance.

2. Operatori: EXISTS, ANY, ALL

EXISTS: Verifica se la sottquery restituisce righe (vero se ≥1 riga).

Esempio: Clienti con almeno un ordine.

SELECT nome
FROM clienti c
WHERE EXISTS (SELECT 1 FROM ordini o WHERE o.cliente_id = c.id);

SELECT nome
FROM clienti c
WHERE EXISTS (SELECT 1 FROM ordini o WHERE o.cliente_id = c.id);

(Correlata; efficiente, smette al primo match.)

ANY: Confronta con qualsiasi valore della sottquery (simile a IN per >, <, etc.).

Esempio: Prodotti con prezzo ≥ qualsiasi in categoria 'Libri'.

SELECT nome, prezzo
FROM prodotti
WHERE prezzo >= ANY (SELECT prezzo FROM prodotti WHERE categoria = 'Libri');

SELECT nome, prezzo
FROM prodotti
WHERE prezzo >= ANY (SELECT prezzo FROM prodotti WHERE categoria = 'Libri');

(Restituisce prodotti non più economici del più economico libro.)

ALL: Confronta con tutti i valori (vero se condizione vale per ogni).

Esempio: Prodotti più cari di tutti in 'Casa'.

SELECT nome, prezzo
FROM prodotti
WHERE prezzo > ALL (SELECT prezzo FROM prodotti WHERE categoria = 'Casa');

SELECT nome, prezzo
FROM prodotti
WHERE prezzo > ALL (SELECT prezzo FROM prodotti WHERE categoria = 'Casa');

(Più caro di ogni prodotto 'Casa'.)

Consigli: Usa NOT EXISTS per assenza (es. clienti senza ordini). ANY/ALL per confronti; equivalenti a MIN/MAX in alcuni casi.

3. Union, Intersect, Except

Panoramica: Operatori set – combinano risultati di query compatibili (stesse colonne/tipi).

UNION: Unisce set, rimuove duplicati (UNION ALL li tiene).

Esempio: Nomi da clienti e prodotti (unici).

SELECT nome FROM clienti
UNION
SELECT nome FROM prodotti;

SELECT nome FROM clienti
UNION
SELECT nome FROM prodotti;

INTERSECT: Intersezione – solo righe comuni.
- Esempio: Nomi condivisi tra clienti e prodotti (se ce ne sono).
```
SELECT nome FROM clienti
INTERSECT
SELECT nome FROM prodotti;
```
```
SELECT nome FROM clienti
INTERSECT
SELECT nome FROM prodotti;
```
  (Nota: Non tutti RDBMS supportano INTERSECT; usa join alternativi.)

EXCEPT: Sottrazione – righe nella prima non nella seconda.

Esempio: Clienti senza ordini.

SELECT nome FROM clienti
EXCEPT
SELECT c.nome FROM clienti c INNER JOIN ordini o ON c.id = o.cliente_id;

SELECT nome FROM clienti
EXCEPT
SELECT c.nome FROM clienti c INNER JOIN ordini o ON c.id = o.cliente_id;

(O usa NOT EXISTS.)

Consigli: Ordina con ORDER BY alla fine. UNION richiede colonne matching. Utile per report multi-sorgente.

4. Query Nidificate e CTE (Common Table Expressions)

Query Nidificate: Sottquery multiple livelli.

Esempio: Media prezzi dei prodotti ordinati da clienti di 'Roma'.

SELECT AVG(prezzo) FROM prodotti
WHERE id IN (SELECT prodotto_id FROM ordini
             WHERE cliente_id IN (SELECT id FROM clienti WHERE citta = 'Roma'));

SELECT AVG(prezzo) FROM prodotti
WHERE id IN (SELECT prodotto_id FROM ordini
             WHERE cliente_id IN (SELECT id FROM clienti WHERE citta = 'Roma'));

CTE: "Tabelle temporanee" nominate – migliorano leggibilità per query complesse.

Sintassi: WITH nome_cte AS (query) SELECT ... FROM nome_cte;

Esempio: Calcola spesa totale per cliente.

WITH spesa_clienti AS (
    SELECT c.nome, SUM(o.quantita * p.prezzo) AS totale
    FROM ordini o
    INNER JOIN clienti c ON o.cliente_id = c.id
    INNER JOIN prodotti p ON o.prodotto_id = p.id
    GROUP BY c.nome
)
SELECT nome, totale
FROM spesa_clienti
WHERE totale > 1000;

WITH spesa_clienti AS (
    SELECT c.nome, SUM(o.quantita * p.prezzo) AS totale
    FROM ordini o
    INNER JOIN clienti c ON o.cliente_id = c.id
    INNER JOIN prodotti p ON o.prodotto_id = p.id
    GROUP BY c.nome
)
SELECT nome, totale
FROM spesa_clienti
WHERE totale > 1000;

(CTE riutilizzabile; supporta recursive per gerarchie.)

Consigli: Usa CTE per query lunghe – più leggibili di nidificate. Recursive CTE per alberi (es. gerarchie dipendenti).

Obiettivi del Modulo

Risolvere problemi complessi con query multilivello.
Scegliere tra sottquery, operatori set e CTE per efficienza/leggibilità.
Gestire scenari reali come filtri dinamici o unioni.

Esercizi Suggeriti

Esercizio 1: Usa una sottquery non correlata per trovare clienti con più ordini della media.
Esercizio 2: Con EXISTS, elenca prodotti mai ordinati (NOT EXISTS).
Esercizio 3: Combina UNION per unire liste di categorie da prodotti e un'altra tabella (creane una).
Esercizio 4: Crea una CTE per calcolare ranking clienti per spesa, poi filtra top 2.
Bonus: Nidifica query per trovare il prodotto più venduto per città.

English version

Explanation of Module 6: Subqueries and Advanced Queries

Welcome to Module 6! Here we take SQL queries to the next level, tackling complex problems that require "nested" or combined queries. Subqueries let you use the results of one query as input to another, while operators like UNION join result sets. This is useful for advanced analysis, such as comparisons or dynamic filters. We will use the products, customers and orders tables from the previous modules – make sure you have them populated with sample data.

We delve deeper into the points with syntax, examples and advice. The controls are standard; test them in your RDBMS (note: some features like CTE are in SQL:1999+).

Overview: A subquery is a query inside another (in parentheses). It can be in the SELECT, FROM, WHERE or HAVING.
Unrelated: Independent – executed first, returns a fixed value.
Example: Find products that are more expensive than average prices.

SELECT name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

SELECT name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

(The subquery calculates the average once; result: Laptop, Smartphone, etc.)

Related: Depends on outer query – run for each outer row (can be slow on large sets).
Example: For each order, find if the customer spent more than average for that product.

SELECT o.id, c.name, o.quantity * p.price AS expense 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
WHERE o.quantity * p.price > (SELECT AVG(quantity * price) FROM orders WHERE product_id = o.product_id);

SELECT o.id, c.name, o.quantity * p.price AS expense 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
WHERE o.quantity * p.price > (SELECT AVG(quantity * price) FROM orders WHERE product_id = o.product_id);

(Related on product_id; filter orders above average by product.)

Tips: Use uncorrelated for efficiency. Avoid deep correlates (use joins if possible). Test with EXPLAIN for performance.

2. Operators: EXISTS, ANY, ALL

EXISTS: Tests whether the subquery returns rows (true if ≥1 row).
Example: Customers with at least one order.

SELECT name 
FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders or WHERE o.customer_id = c.id);

SELECT name 
FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders or WHERE o.customer_id = c.id);

(Related; efficient, quits in first match.)

ANY: Compare to any subquery value (similar to IN for >, <, etc.).
Example: Products with price ≥ any in the 'Books' category.

SELECT name, price 
FROM products 
WHERE price >= ANY (SELECT price FROM products WHERE category = 'Books');

SELECT name, price 
FROM products 
WHERE price >= ANY (SELECT price FROM products WHERE category = 'Books');

(Returns products no cheaper than the cheapest book.)

ALL: Compare with all values (true if condition holds for all).
Example: Most expensive products of all in 'Home'.

SELECT name, price 
FROM products 
WHERE price > ALL (SELECT price FROM products WHERE category = 'Home');

SELECT name, price 
FROM products 
WHERE price > ALL (SELECT price FROM products WHERE category = 'Home');

(More expensive than any 'Home' product.)

Advice: Use NOT EXISTS for absence (e.g. customers without orders). ANY/ALL for comparisons; equivalent to MIN/MAX in some cases.

3. Union, Intersect, Except

Overview: Set operators – combine compatible query results (same columns/types).
UNION: Merges sets, removes duplicates (UNION ALL keeps them).
Example: Names from customers and products (unique).

SELECT name FROM customers 
UNION 
SELECT name FROM products;

SELECT name FROM customers 
UNION 
SELECT name FROM products;

INTERSECT: Intersection – common lines only.
Example: Names shared between customers and products (if any).

SELECT name FROM customers 
INTERSECT 
SELECT name FROM products;

SELECT name FROM customers 
INTERSECT 
SELECT name FROM products;

(Note: Not all RDBMS support INTERSECT; use alternative joins.)

EXCEPT: Subtraction – rows in the first not the second.
Example: Customers without orders.

SELECT name FROM customers 
EXCEPT 
SELECT c.name FROM customers c INNER JOIN orders o ON c.id = o.customer_id;

SELECT name FROM customers 
EXCEPT 
SELECT c.name FROM customers c INNER JOIN orders o ON c.id = o.customer_id;

(Or use NOT EXISTS.)

Tips: Sort with ORDER BY at the end. UNION requires matching columns. Useful for multi-source reports.

4. Nested Queries and CTE (Common Table Expressions)

Nested Queries: Multiple level subqueries.
Example: Average prices of products ordered by customers in 'Rome'.

SELECT AVG(price) FROM products 
WHERE id IN (SELECT product_id FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Rome'));

SELECT AVG(price) FROM products 
WHERE id IN (SELECT product_id FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Rome'));

CTE: Named "Temporary Tables" – improve readability for complex queries.
Syntax: WITH cte_name AS (query) SELECT ... FROM cte_name;
Example: Calculate total spend per customer.

WITH customer_spend AS ( 
SELECT c.name, SUM(o.quantity * price) AS total 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name
 ) 
SELECT name, total 
FROM customer_spend 
WHERE total > 1000;

WITH customer_spend AS ( 
SELECT c.name, SUM(o.quantity * price) AS total 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name
 ) 
SELECT name, total 
FROM customer_spend 
WHERE total > 1000;

(reusable CTE; up recursive port for hierarchies.)

Tips: Use CTE for long queries – more readable than nested ones. Recursive CTE for trees (e.g. dependent hierarchies).

Module Objectives

Solve complex problems with multilevel queries.
Choose between subqueries, set operators and CTEs for efficiency/readability.
Manage real scenarios like dynamic filters or merges.

Suggested Exercises

Exercise 1: Use an unrelated subquery to find customers with more orders than average.
Exercise 2: With EXISTS, list products never ordered (NOT EXISTS).
Exercise 3: Combine UNION to join category lists from products and another table (create one).
Exercise 4: Create a CTE to calculate customer ranking by spend, then filter top 2.
Bonus: Nest queries to find the best-selling product by city.

Puoi seguire anche il mio canale YouTube https://www.youtube.com/channel/UCoOgys_fRjBrHmx2psNALow/ con tanti video interessanti

I consigli che offriamo sono di natura generale. Non sono consigli legali o professionali. Quello che può funzionare per una persona potrebbe non essere adatto a un’altra, e dipende da molte variabili.

Magnetismo

Cerca nel blog