SQL - query nidificate, operatori insiemistici e viste

Prerequisito: SQL - interrogazioni, join e aggregazioniUn'interrogazione SQL ha almeno SELECT e FROM; le altre clausole (WHERE, GROUP BY, HAVING, ORDER BY) sono opzionali e HAVING richiede GROUP BY. Ordine logico di valutazione: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY. SELECT corrisponde a π (ma senza DISTINCT non elimina i duplicati), WHERE a σ, gli alias ad AS/ρ, i join a ⋈ (INNER, LEFT, RIGHT, FULL). WHERE seleziona le righe prima del raggruppamento, HAVING seleziona i gruppi dopo. Le funzioni COUNT, SUM, AVG, MIN, MAX ignorano i NULL (COUNT() conta le righe, DISTINCT toglie i duplicati dentro la funzione); con un aggregato o con GROUP BY nella SELECT possono comparire solo colonne di raggruppamento o aggregati. LIKE usa % (qualsiasi sequenza) e _ (un carattere). I confronti con NULL non sono veri: serve IS NULL. Un filtro sulla tabella di destra di un LEFT JOIN va nella clausola ON, non in WHERE, altrimenti il join diventa interno. Con interi COUNT(a)/COUNT(b) è una divisione intera: serve CAST.SQL - interrogazioni, join e aggregazioni →. Qui si compongono le query: una dentro l'altra (sottoquery), combinate con operatori insiemistici, o rese riutilizzabili con le viste. Sono gli strumenti che servono per le richieste del tipo "il massimo", "tutti", "nessuno", che in algebra si risolvono con differenza e divisione (Algebra relazionale estesa - divisione, aggregazione e outer joinL'algebra relazionale di base lavora una tupla alla volta e non sa fare calcoli sui valori né riassumere più tuple. L'estensione aggiunge: la proiezione generalizzata π_{F1,...,Fn}(R), che calcola espressioni sugli attributi (prezzo per quantità); l'aggregazione ℱ_{SUM, MAX, MIN, AVG, COUNT}(R), che produce una sola tupla; il raggruppamento (A1,...,Am)ℱ_{f(B)}(R), che produce una tupla per gruppo (grado n+m, cardinalità = numero di gruppi). Le funzioni di aggregazione ignorano i NULL, COUNT() conta le tuple, i NULL formano un gruppo a sé. Gli outer join (left, right, full) mantengono con NULL le tuple dangling del join; i NULL nella chiave di join non si accoppiano mai. La divisione R÷S dà gli x tali che (x,y) sta in R per ogni y di S, e si ottiene con π, × e −. Ricette: massimo = aggregazione + join con il massimo, "tutti" = divisione o complemento, "almeno due" = self-join.Algebra relazionale estesa - divisione, aggregazione e outer join →). Gli esempi sono nell'esercizio Esercizio - Interrogazioni SQL sul database dei riferimenti bibliografici (slide di SQL) e nei temi d'esame (per esempio Esercizio - Interrogazioni sulle formule scientifiche (tema d'esame 22 gennaio 2025)).

Query nidificate

Si può inserire una SELECT dentro un'altra, sempre tra parentesi. Di solito si annida nella FROM o nella WHERE.

Nella FROM: tabella derivata

La sottoquery produce una tabella temporanea a cui la query esterna si riferisce. In PostgreSQL deve avere un alias.

SELECT MAX(ReferencesNumber)
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber
      FROM Reference
      GROUP BY publisher) AS Counter;
-- 3: il massimo numero di riferimenti di un editore

Si usa quando serve aggregare un risultato già aggregato (il massimo dei conteggi): un'aggregazione non può contenere un'altra aggregazione direttamente (MAX(COUNT(...)) non è ammesso).

Nella WHERE

Tre usi:

1. Valore scalare (la sottoquery restituisce una riga e una colonna), usata con =, <, >, ...:

SELECT id, title, year FROM Reference
WHERE year = (SELECT MAX(year) FROM Reference);
-- i 3 riferimenti del 2002

Se la sottoquery restituisce più di una riga si ha un errore (o, in SQLite, si usa la prima).

2. Insieme di valori: x IN (sottoquery), x NOT IN (...), e confronti x op ANY (...), x op ALL (...):

SELECT surname FROM Author
WHERE id IN (SELECT author FROM Write WHERE authorNumber = 3);   -- Buck

IN equivale a = ANY. x >= ALL (sottoquery) è vero se $x$ è maggiore o uguale a tutti i valori (SQLite non supporta ANY/ALL: si ottengono con MAX/MIN).

3. Esistenza: EXISTS (sottoquery) è vero se la sottoquery restituisce almeno una riga; NOT EXISTS se non ne restituisce. Le colonne selezionate non contano (SELECT *).

SELECT p.name FROM Publisher p
WHERE EXISTS (SELECT * FROM Reference r WHERE r.publisher = p.id AND r.year < 1995);
-- McGraw-Hill: ha un riferimento del 1991

Sottoquery correlate

Una sottoquery è correlata se usa colonne della query esterna: concettualmente va rivalutata per ogni riga esterna.

SELECT r.id, r.year, r.publisher
FROM Reference r
WHERE r.year > (SELECT AVG(r2.year) FROM Reference r2 WHERE r2.publisher = r.publisher);
-- i riferimenti più recenti della media del proprio editore (4 righe)

Qui r.publisher nella sottoquery rimanda alla riga corrente r della query esterna; gli alias (r, r2) sono indispensabili per distinguere le due copie di Reference. Una sottoquery non correlata si valuta una sola volta.

Il massimo e il minimo

In WHERE non si possono usare funzioni di aggregazione. L'errore tipico (anche nelle slide, come passaggio intermedio) è

SELECT publisher, ReferencesNumber
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher) AS Counter
WHERE ReferencesNumber = MAX(ReferencesNumber);     -- ERRORE: aggregato in WHERE

Il valore del massimo va calcolato a parte e confrontato. Quattro modi equivalenti, per "l'editore con più riferimenti" (risposta: McGraw-Hill e Prentice Hall con 3, a pari merito):

-- (1) sottoquery scalare, ripetendo la tabella derivata
SELECT publisher, ReferencesNumber
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher) AS Counter
WHERE ReferencesNumber = (SELECT MAX(ReferencesNumber)
                          FROM (SELECT publisher, COUNT(id) AS ReferencesNumber
                                FROM Reference GROUP BY publisher) AS Counter2);

-- (2) con una vista: si scrive la sottoquery una volta sola
CREATE VIEW PublisherReferences AS
SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher;

SELECT publisher, ReferencesNumber FROM PublisherReferences
WHERE ReferencesNumber = (SELECT MAX(ReferencesNumber) FROM PublisherReferences);

-- (3) con una CTE (clausola WITH), che è una vista "temporanea" valida per una sola query
WITH Counter AS (SELECT publisher, COUNT(id) AS n FROM Reference GROUP BY publisher)
SELECT * FROM Counter WHERE n = (SELECT MAX(n) FROM Counter);

-- (4) con HAVING e ALL (PostgreSQL; non in SQLite)
SELECT publisher FROM Reference GROUP BY publisher
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM Reference GROUP BY publisher);

I primi tre sono stati provati (stesso risultato). ORDER BY n DESC LIMIT 1 è una scorciatoia che restituisce una sola riga anche in caso di pareggio (qui una delle due, a caso): è corretta solo se si accetta uno qualunque dei massimi. La sintassi WITH non è nelle slide ma è standard e leggibile.

"Per tutti" e "nessuno"

Nessuno / che non: si traduce con una differenza. Per "gli editori senza riferimenti" esistono quattro forme equivalenti, tre giuste e una a rischio:

SELECT id FROM Publisher WHERE id NOT IN (SELECT publisher FROM Reference);          -- (a) RISCHIO NULL
SELECT id FROM Publisher p
WHERE NOT EXISTS (SELECT * FROM Reference r WHERE r.publisher = p.id);               -- (b)
SELECT p.id FROM Publisher p LEFT JOIN Reference r ON r.publisher = p.id
WHERE r.id IS NULL;                                                                    -- (c)
SELECT id FROM Publisher EXCEPT SELECT publisher FROM Reference;                      -- (d)

La trappola di NOT IN. Con un editore Wiley senza riferimenti, le quattro forme restituiscono Wiley. Se però una sola riga di Reference ha publisher = NULL, la forma (a) restituisce zero righe. Il motivo: x NOT IN (a, b, NULL) equivale a x <> a AND x <> b AND x <> NULL, e l'ultimo termine è sconosciuto, quindi la congiunzione non è mai vera. Le forme (b), (c), (d) restano corrette (provato). Regola: con NOT IN si aggiunge WHERE publisher IS NOT NULL nella sottoquery, oppure si preferisce NOT EXISTS.

Tutti: la divisione si scrive con un doppio NOT EXISTS, che legge "non esiste un corso per cui non esiste un esame superato". Con Passed(Student, Course) e Course(Course) dell'esempio di divisione:

SELECT DISTINCT p.Student
FROM Passed p
WHERE NOT EXISTS (SELECT * FROM Course c
                  WHERE NOT EXISTS (SELECT * FROM Passed q
                                    WHERE q.Student = p.Student AND q.Course = c.Course));
-- Anna, Carla

Oppure con il conteggio: uno studente ha superato tutti i corsi se i corsi distinti che ha superato (tra quelli di Course) sono tanti quanti i corsi totali:

SELECT p.Student
FROM Passed p JOIN Course c ON p.Course = c.Course
GROUP BY p.Student
HAVING COUNT(DISTINCT p.Course) = (SELECT COUNT(*) FROM Course);
-- Anna, Carla (Carla ha passato anche Algebra: il join con Course la scarta)

Entrambe le forme sono state provate e danno lo stesso risultato della formula $\pi_X(R)-\pi_X((\pi_X(R)\times S)-R)$. Il conteggio è più breve ma presuppone che le righe $(x,y)$ non siano ripetute.

Operatori insiemistici

<query-1> UNION     [ALL] <query-2>
<query-1> INTERSECT [ALL] <query-2>
<query-1> EXCEPT    [ALL] <query-2>

Restituiscono unione, intersezione e differenza dei risultati delle due query. Le query devono essere compatibili all'unione: stesso numero di colonne con tipi compatibili (i nomi delle colonne sono quelli della prima query). Senza ALL i duplicati sono eliminati; con ALL sono mantenuti (e sono più veloci, perché non serve ordinare).

SELECT surname FROM Author WHERE surname LIKE 'C%'     -- Castelli, Cornell
UNION
SELECT surname FROM Author WHERE surname LIKE '%ll';   -- Cornell

La prima query restituisce Castelli e Cornell, la seconda solo Cornell. Con UNION il cognome Cornell (presente in entrambe) compare una volta (2 righe in tutto), con UNION ALL due volte (3 righe); INTERSECT dà solo Cornell. Per "i riferimenti senza informazioni aggiuntive", cioè $\pi_{\text{id}}(\text{Reference})-\pi_{\text{reference}}(\text{Information})$:

SELECT id FROM Reference
EXCEPT
SELECT reference FROM Information;
-- 5 righe: ECDL2002, Gilardi1991, HorstmannCornell2000, HorstmannCornell2001, OppenheimSchafer1999

Sono l'equivalente di $\cup$, $\cap$, $-$ dell'algebra; tutti i DBMS moderni li supportano (MySQL solo dalla versione 8.0.31 per INTERSECT ed EXCEPT). EXCEPT si chiama MINUS in Oracle.

Viste

Definizione. Una vista è una tabella derivata da altre tabelle (di base o altre viste) per mezzo di una query. È una tabella virtuale: a differenza delle tabelle di base, le sue righe non sono memorizzate (nella forma classica); la query che la definisce viene eseguita ogni volta che la si usa.

CREATE VIEW TitleYears AS
SELECT title, year FROM Reference;

CREATE VIEW AuthorTitles AS
SELECT name, surname, title
FROM Reference AS R
     INNER JOIN Write AS W ON R.id = W.reference
     INNER JOIN Author AS A ON W.author = A.id;

DROP VIEW TitleYears CASCADE;       -- o RESTRICT (predefinito)

Una vista si interroga come una tabella: SELECT * FROM AuthorTitles WHERE surname = 'Tanenbaum'.

A che cosa servono:

• semplificare query complesse e riusare una sottoquery (come PublisherReferences sopra, scritta una volta e usata due volte);
• nascondere colonne o righe riservate (una vista senza la colonna password dà accesso agli utenti senza mostrare le password): è l'indipendenza logica dei dati (Basi di dati e DBMS - architettura ANSI-SPARC e indipendenza dei datiUna base di dati (database, DB) è una collezione integrata e persistente di dati correlati che rappresenta un frammento di mondo reale (mini-mondo); un DBMS è il software generico che permette di definirla (schema, nel catalogo come metadati), costruirla e manipolarla (interrogazioni e aggiornamenti). Rispetto a una raccolta di file il DBMS riduce ridondanza e incoerenza, condivide i dati tra applicazioni con controllo della concorrenza (isolamento, atomicità), garantisce durabilità, sicurezza (autenticazione e autorizzazione) ed efficienza. L'architettura ANSI/SPARC ha tre livelli: schema esterno (una vista per applicazione), logico (rappresentazione integrata, indipendente dalla memorizzazione), interno (strutture fisiche); da qui l'indipendenza fisica (cambia l'interno senza toccare logico ed esterno) e logica (cambia il logico senza toccare le applicazioni). Attori: sviluppatori del DBMS, DBA, progettisti di basi di dati e di applicazioni, utenti parametrici e occasionali.Basi di dati e DBMS - architettura ANSI-SPARC e indipendenza dei dati →), cioè la vista come schema esterno;
• mantenere stabile lo schema esterno quando cambia quello logico.

Aggiornamento delle viste. Una vista va tenuta aggiornata: se cambiano le tabelle da cui dipende, anche la vista cambia. Con le viste online questo accade eseguendo la query ogni volta; con le viste materializzate il risultato è memorizzato e va aggiornato (REFRESH MATERIALIZED VIEW in PostgreSQL): le letture sono veloci, gli aggiornamenti costano. Modificare i dati tramite una vista è possibile solo per viste semplici (una sola tabella, senza aggregati né DISTINCT): per le altre non si sa quali modifiche delle tabelle producano una data modifica della vista (problema di ricerca noto: update propagation). In SQLite le viste non sono modificabili (cannot modify ... because it is a view).

Errori tipici

• Aggregato in WHERE (= MAX(...)): si calcola in una sottoquery o in una vista.
• Dimenticare l'alias della tabella derivata in FROM (PostgreSQL lo rifiuta).
• NOT IN con una sottoquery che può restituire NULL.
• Sottoquery scalare che restituisce più righe.
• Non distinguere le due copie della stessa tabella in una sottoquery correlata (alias uguali o mancanti).
• UNION quando servivano i duplicati (conteggi!) o UNION ALL quando non li volevano.
• ORDER BY ... LIMIT 1 per "il massimo" quando ci possono essere pareggi.
• Credere che una vista memorizzi dati (se non è materializzata) o che sia sempre modificabile.

Domande d'esame

1. Trovare, con una sottoquery, il post con il numero massimo di commenti e spiegare perché HAVING COUNT(*) = MAX(COUNT(*)) non funziona. Traccia. Gli aggregati non si annidano: MAX(COUNT(*)) non è ammesso. Si calcola il conteggio per post in una vista o tabella derivata e si confronta con (SELECT MAX(n) FROM ...).

2. Perché NOT IN può dare un risultato vuoto anche se esistono righe che soddisfano la richiesta? Come lo si evita? Traccia. Se la sottoquery contiene NULL, x NOT IN (..., NULL) è sconosciuto per ogni $x$. Si usa NOT EXISTS, EXCEPT, oppure si filtra IS NOT NULL nella sottoquery.

3. Differenza tra una vista e una tabella; quando conviene una vista materializzata? Traccia. La vista è una query con un nome, calcolata a ogni uso; non occupa spazio per i dati. La materializzata ne memorizza il risultato: conviene per query costose, molto lette e con tabelle di base che cambiano di rado, accettando di doverla aggiornare.