SQL - interrogazioni, join e aggregazioni

Prerequisiti: SQL - definizione dei dati e vincoliSQL (standard ISO 9075) è un linguaggio dichiarativo con una parte di definizione dei dati (DDL: CREATE/DROP/ALTER di database, schemi, tipi enumerati, domini, tabelle) e una di manipolazione (DML: INSERT, DELETE, UPDATE, SELECT). Una tabella SQL è un multi-insieme di righe (può avere duplicati), quindi non è una relazione in senso stretto. CREATE TABLE elenca colonne con tipo e vincoli: NOT NULL, DEFAULT, CHECK (di colonna o di tupla), PRIMARY KEY (una sola, anche composta), UNIQUE, FOREIGN KEY ... REFERENCES con ON DELETE/ON UPDATE (NO ACTION, CASCADE, SET NULL, SET DEFAULT). I tipi principali sono CHAR/VARCHAR, NUMERIC, INTEGER, SERIAL, BOOLEAN, DATE, TIME, TIMESTAMP, più UUID, intervalli e JSON in PostgreSQL. DROP ... CASCADE cancella anche gli oggetti dipendenti, RESTRICT (predefinito) rifiuta se ce ne sono. DELETE e UPDATE senza WHERE agiscono su tutta la tabella.SQL - definizione dei dati e vincoli → (le tabelle usate negli esempi) e Algebra relazionale - operatori di base e joinLe operazioni sul modello relazionale sono di aggiornamento (inserimento, modifica, cancellazione, che possono violare i vincoli di integrità: si rifiuta, si corregge, si propaga con cascade o si imposta NULL/default) e di interrogazione (funzioni che da un'istanza del database producono una relazione). L'algebra relazionale è il linguaggio procedurale delle interrogazioni: gli operatori primitivi sono unione, differenza, prodotto, rinomina ρ, selezione σ e proiezione π; derivati sono intersezione, theta-join, equi-join, join naturale. Unione, intersezione e differenza richiedono relazioni compatibili all'unione (stessi attributi e domini). La selezione è una decomposizione orizzontale, la proiezione verticale (commutano solo se gli attributi della selezione sono nella proiezione). Il theta-join è σ_φ(R1×R2), il join naturale equivale a un equi-join sugli attributi omonimi seguito da una proiezione; la sua cardinalità è tra 0 e |R1||R2|, ≤|R1| se gli attributi comuni sono chiave di R2, =|R1| se sono anche chiave esterna di R1 con vincolo di integrità referenziale. Le tuple senza corrispondente sono "dangling". I valori NULL richiedono condizioni esplicite IS NULL / IS NOT NULL, perché σ_{A>40} ∪ σ_{A≤40} non restituisce le tuple con A nullo.Algebra relazionale - operatori di base e join → con 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 →: SQL è la loro implementazione, con qualche differenza che si segnala. Si prosegue con SQL - query nidificate, operatori insiemistici e visteUna sottoquery è una SELECT tra parentesi dentro un'altra: nella FROM (tabella derivata, con alias obbligatorio in PostgreSQL) oppure nella WHERE, come valore scalare con =, <, ..., come insieme con IN / NOT IN, ANY, ALL, oppure con EXISTS / NOT EXISTS. Una sottoquery correlata usa colonne della query esterna e va rivalutata per ogni riga. Le funzioni di aggregazione non si possono usare in WHERE: il massimo si ottiene con una sottoquery scalare (WHERE n = (SELECT MAX(n) ...)), con una vista o una CTE. NOT IN con una sottoquery che contiene NULL non restituisce mai righe: meglio NOT EXISTS, EXCEPT o LEFT JOIN ... IS NULL. "Per tutti" (divisione) = doppio NOT EXISTS o HAVING COUNT(DISTINCT ...) = numero totale. Gli operatori insiemistici UNION, INTERSECT, EXCEPT richiedono query compatibili e di default eliminano i duplicati (ALL li mantiene). Una vista è una tabella virtuale definita da una query (CREATE VIEW, DROP VIEW): semplifica le query, riusa sottoquery, nasconde i dati; la vista materializzata invece memorizza il risultato.SQL - query nidificate, operatori insiemistici e viste →. Le interrogazioni degli esempi sono tutte nell'esercizio Esercizio - Interrogazioni SQL sul database dei riferimenti bibliografici (slide di SQL), con i risultati.

Gli esempi usano il database dei riferimenti bibliografici (tabelle Author(id, name, surname), Publisher(id, name), Reference(id, title, year, publisher), Information(reference, notes), Write(reference, author, authorNumber)), con 9 autori, 4 editori e 9 riferimenti.

Struttura di una query

SELECT   <elenco di colonne o espressioni>      -- obbligatoria
FROM     <tabelle e join>                       -- obbligatoria
WHERE    <condizione sulle righe>
GROUP BY <colonne di raggruppamento>
HAVING   <condizione sui gruppi>                -- solo con GROUP BY
ORDER BY <colonne> [ASC | DESC];

L'ordine di scrittura non è l'ordine di valutazione. Logicamente il DBMS fa:

1. FROM (e i join): costruisce l'insieme di righe di partenza;
2. WHERE: scarta le righe che non soddisfano la condizione;
3. GROUP BY: forma i gruppi;
4. HAVING: scarta i gruppi;
5. SELECT: calcola le colonne di uscita (qui nascono gli alias);
6. DISTINCT: toglie i duplicati;
7. ORDER BY (e LIMIT): ordina il risultato.

Conseguenza: in WHERE non si usano gli alias definiti nella SELECT (in PostgreSQL; SQLite lo tollera) né le funzioni di aggregazione, che richiedono i gruppi; in ORDER BY invece sì.

SELECT: proiezione, espressioni e DISTINCT

SELECT name, surname FROM Author;        -- π_{name,surname}(Author)
SELECT * FROM Publisher;                 -- tutte le colonne

Il risultato elenca le 9 righe di Author (John R. Buck, Donatella Castelli, ...).

DISTINCT. Una proiezione algebrica elimina i duplicati, SELECT no. $\pi_{\text{author},\text{authorNumber}}(\text{Write})$ si scrive

SELECT DISTINCT author, authorNumber FROM Write;

Senza DISTINCT si avrebbero le stesse righe qui (ogni coppia compare una volta, per via della chiave), ma in generale le righe duplicate restano. Regola pratica: per tradurre una $\pi$ in SQL si mette DISTINCT, a meno che la chiave non garantisca l'unicità.

Espressioni e alias. Nella SELECT (e in WHERE) si possono usare espressioni; AS dà un nome alla colonna di uscita:

SELECT surname || ' ' || name AS AuthorData FROM Author;

|| concatena le stringhe; il risultato ha una colonna AuthorData con valori come Buck John R.. Con AS si dà un nome anche a una tabella (FROM Author AS A): serve per distinguere colonne omonime e per i self-join (è la rinomina $\rho$).

WHERE: condizioni

SELECT name FROM Author WHERE surname = 'Gilardi';                       -- Gianni
SELECT id, title FROM Reference
WHERE year = 1999 OR year = 2000 ORDER BY title ASC;                     -- 2 righe

Operatori: =, <> (o !=), <, >, <=, >=, AND, OR, NOT, e inoltre BETWEEN a AND b (estremi inclusi), IN (v1, v2, ...), LIKE, IS NULL. Con ORDER BY ... ASC | DESC si ordina (ASC predefinito); più colonne si separano con la virgola. LIMIT n tiene le prime $n$ righe (PostgreSQL e SQLite).

LIKE è una "povera" espressione regolare: % sostituisce una sequenza qualsiasi di caratteri (anche vuota), _ un solo carattere.

SELECT name, surname FROM Author WHERE surname LIKE 'C%';    -- Castelli, Cornell
SELECT name FROM Author WHERE name LIKE '_a%';               -- Gary, Cay S., Pasquale

NULL e logica a tre valori

Un confronto con NULL non è vero né falso: è sconosciuto. Di conseguenza x = NULL e NULL = NULL non sono mai veri; per cercare i NULL si usa IS NULL / IS NOT NULL. WHERE tiene solo le righe per cui la condizione è vera. Con la logica a tre valori: NOT (1 = NULL) è sconosciuto, (1=1) OR (NULL=1) è vero, (1=2) AND (NULL=1) è falso (verificato). La tabella di verità è nella nota Algebra relazionale - operatori di base e joinLe operazioni sul modello relazionale sono di aggiornamento (inserimento, modifica, cancellazione, che possono violare i vincoli di integrità: si rifiuta, si corregge, si propaga con cascade o si imposta NULL/default) e di interrogazione (funzioni che da un'istanza del database producono una relazione). L'algebra relazionale è il linguaggio procedurale delle interrogazioni: gli operatori primitivi sono unione, differenza, prodotto, rinomina ρ, selezione σ e proiezione π; derivati sono intersezione, theta-join, equi-join, join naturale. Unione, intersezione e differenza richiedono relazioni compatibili all'unione (stessi attributi e domini). La selezione è una decomposizione orizzontale, la proiezione verticale (commutano solo se gli attributi della selezione sono nella proiezione). Il theta-join è σ_φ(R1×R2), il join naturale equivale a un equi-join sugli attributi omonimi seguito da una proiezione; la sua cardinalità è tra 0 e |R1||R2|, ≤|R1| se gli attributi comuni sono chiave di R2, =|R1| se sono anche chiave esterna di R1 con vincolo di integrità referenziale. Le tuple senza corrispondente sono "dangling". I valori NULL richiedono condizioni esplicite IS NULL / IS NOT NULL, perché σ_{A>40} ∪ σ_{A≤40} non restituisce le tuple con A nullo.Algebra relazionale - operatori di base e join →.

Funzioni di aggregazione

Regole:

• i NULL sono scartati prima di applicare l'aggregato (AVG divide per il numero di valori non nulli);
• DISTINCT dentro la funzione ignora i duplicati;
• quando la SELECT contiene una funzione di aggregazione senza GROUP BY, può contenere solo aggregati: SELECT reference, MAX(authorNumber) FROM Write è errato (PostgreSQL rifiuta la query; SQLite la accetta e restituisce una riga a caso, che è peggio).

SELECT MAX(authorNumber) AS MaxNumberAuthors FROM Write;          -- 3
SELECT COUNT(*) AS AuthorRowsNumber FROM Author;                  -- 9
SELECT COUNT(DISTINCT author) AS WriteAuthorsNumber FROM Write;   -- 9: ogni autore compare in almeno una riga

GROUP BY e HAVING

GROUP BY forma i gruppi di righe con gli stessi valori nelle colonne indicate; i NULL formano un gruppo a sé. Le funzioni di aggregazione si applicano a ciascun gruppo e producono una riga per gruppo. Nella SELECT possono comparire solo le colonne di raggruppamento e gli aggregati.

SELECT author, COUNT(*) FROM Write GROUP BY author;
-- una riga per autore: CastelliD 2, CornellG 2, ..., SchaferRW 1 (9 righe)

HAVING filtra i gruppi, WHERE filtra le righe prima di raggruppare:

SELECT author, COUNT(*) FROM Write GROUP BY author HAVING COUNT(*) > 1;
-- 5 autori con almeno due riferimenti: CastelliD, CornellG, HorstmannCS, PaganoP, TanenbaumAS

SELECT author, COUNT(*) FROM Write WHERE authorNumber > 1 GROUP BY author;
-- WHERE agisce prima: si contano solo le righe con numero autore > 1 -> BuckJR 1, CornellG 2, PaganoP 2, SchaferRW 1

SELECT author, COUNT(*) FROM Write WHERE authorNumber > 1
GROUP BY author HAVING COUNT(*) > 1;
-- prima WHERE poi HAVING -> CornellG 2, PaganoP 2

Regola: una condizione che riguarda una riga va in WHERE (è più efficiente: si scarta prima di raggruppare); una che riguarda un aggregato va in HAVING.

Join

Il join si scrive in FROM con la sintassi esplicita (preferibile alla vecchia con la virgola e le condizioni in WHERE):

SELECT name, title
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
WHERE name = 'McGraw-Hill';
-- π_{name,title}(Reference ⋈_{publisher=id} σ_{name='McGraw-Hill'}(Publisher))
-- 3 righe: Analisi Uno, Java 2 tecniche avanzate, Java 2 i fondamenti

Un join a più tabelle si scrive concatenando i JOIN; ogni ON collega l'ultima tabella aggiunta:

SELECT surname, title
FROM Author AS A INNER JOIN Write AS W ON A.id = W.author
                 INNER JOIN Reference AS R ON W.reference = R.id;
-- 14 righe: una per coppia (autore, riferimento)

Join con raggruppamento. "Quanti riferimenti ha ogni editore":

SELECT name, COUNT(*)
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
GROUP BY name;
-- McGraw-Hill 3, Prentice Hall International Editions 3, ECDL 2, Springer 1

Il raggruppamento avviene dopo il join: COUNT(*) conta le righe del join, quindi attenzione se il join moltiplica le righe (un riferimento con tre autori compare tre volte).

Left join: tutte le righe di sinistra.

SELECT title, notes
FROM Reference AS R LEFT JOIN Information AS I ON R.id = I.reference;
-- 9 righe: i 4 riferimenti con note hanno le note, gli altri 5 hanno notes = NULL

SELECT COUNT(*)
FROM Reference AS R LEFT JOIN Information AS I ON R.id = I.reference
WHERE notes IS NULL;
-- 5: i riferimenti senza informazioni

Dove va la condizione: ON o WHERE? Nei join esterni sono diverse. Con ... LEFT JOIN Information I ON R.id = I.reference WHERE notes LIKE '%edition' si hanno solo 2 righe: il WHERE scarta le righe con notes = NULL (NULL LIKE qualcosa non è vero) e il join esterno si comporta come uno interno. Con ... ON R.id = I.reference AND notes LIKE '%edition' si hanno 9 righe: il filtro rimuove solo l'abbinamento (e lascia NULL a destra). Un filtro sulla tabella di destra di un left join va in ON; un filtro sulla tabella di sinistra va in WHERE. Lo stesso vale per contare "zero" senza perdere righe: LEFT JOIN ... ON a.id = w.author AND w.authorNumber > 1 GROUP BY ... COUNT(w.reference) dà 0 per gli autori senza righe corrispondenti, con COUNT(w.reference) (non COUNT(*), che darebbe 1 sulla riga con NULL).

Self-join. Per accoppiare righe della stessa tabella servono due alias: "ogni commento con la sua risposta", nel database del social network del laboratorio, è FROM Commento AS c INNER JOIN Commento AS r ON c.id = r.Commento_Origine.

Ordinare e unire più condizioni

SELECT P.name, A.surname, COUNT(R.id) AS ReferencesNumber
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
     INNER JOIN Write AS W ON R.id = W.reference
     INNER JOIN Author AS A ON W.author = A.id
GROUP BY P.name, A.surname
ORDER BY P.name ASC, ReferencesNumber DESC, A.surname ASC;

Per ogni coppia (editore, cognome) conta i riferimenti. Si ordina per editore, poi per numero decrescente, poi per cognome (si può ordinare per un alias della SELECT). Nota: si raggruppa per cognome, non per A.id: due autori diversi con lo stesso cognome verrebbero fusi. Per distinguerli si raggruppa anche per A.id.

Dall'algebra a SQL

Differenze da tenere presenti: SQL non elimina i duplicati da solo (DISTINCT, UNION senza ALL); i NULL; l'ordine delle righe è definito solo con ORDER BY.

Divisione intera e cast

Con interi, / è una divisione intera: SELECT COUNT(id) / COUNT(DISTINCT publisher) FROM Reference con 9 riferimenti e 4 editori dà 2, non 2,25. Per avere il decimale si converte un operando: CAST(COUNT(id) AS DOUBLE PRECISION) / COUNT(DISTINCT publisher) dà 2,25 (verificato; in SQLite il tipo si chiama REAL).

Errori tipici

• Colonne non aggregate e non di raggruppamento nella SELECT con GROUP BY o con un aggregato (SELECT reference, MAX(authorNumber)).
• Aggregati in WHERE (WHERE n = MAX(n)): servono HAVING o una sottoquery (SQL - query nidificate, operatori insiemistici e visteUna sottoquery è una SELECT tra parentesi dentro un'altra: nella FROM (tabella derivata, con alias obbligatorio in PostgreSQL) oppure nella WHERE, come valore scalare con =, <, ..., come insieme con IN / NOT IN, ANY, ALL, oppure con EXISTS / NOT EXISTS. Una sottoquery correlata usa colonne della query esterna e va rivalutata per ogni riga. Le funzioni di aggregazione non si possono usare in WHERE: il massimo si ottiene con una sottoquery scalare (WHERE n = (SELECT MAX(n) ...)), con una vista o una CTE. NOT IN con una sottoquery che contiene NULL non restituisce mai righe: meglio NOT EXISTS, EXCEPT o LEFT JOIN ... IS NULL. "Per tutti" (divisione) = doppio NOT EXISTS o HAVING COUNT(DISTINCT ...) = numero totale. Gli operatori insiemistici UNION, INTERSECT, EXCEPT richiedono query compatibili e di default eliminano i duplicati (ALL li mantiene). Una vista è una tabella virtuale definita da una query (CREATE VIEW, DROP VIEW): semplifica le query, riusa sottoquery, nasconde i dati; la vista materializzata invece memorizza il risultato.SQL - query nidificate, operatori insiemistici e viste →).
• Filtro sulla tabella di destra di un LEFT JOIN messo in WHERE.
• COUNT(*) su un left join al posto di COUNT(colonna_di_destra): i "senza corrispondenza" contano 1.
• Dimenticare DISTINCT dove la traduzione di una $\pi$ può dare duplicati.
• = NULL al posto di IS NULL.
• Divisione intera tra COUNT.
• Raggruppare per una colonna non unica (cognome) quando serviva l'identificatore.
• Contare dopo un join che moltiplica le righe senza COUNT(DISTINCT ...).

Domande d'esame

1. Differenza tra WHERE e HAVING. Traccia. WHERE filtra le righe prima del raggruppamento e non può contenere aggregati; HAVING filtra i gruppi dopo il raggruppamento e di solito contiene aggregati. Si usa WHERE quando possibile (scarta prima, costa meno).

2. Come si conta il numero di commenti di ogni post, mostrando 0 per i post senza commenti? Traccia. FROM Post p LEFT JOIN Commento c ON p.ID = c.Post GROUP BY p.ID con COUNT(c.ID). Con COUNT(*) i post senza commenti darebbero 1; con un INNER JOIN non comparirebbero.

3. Perché SELECT reference, MAX(authorNumber) FROM Write è errata? Traccia. Con un aggregato e senza GROUP BY la SELECT può contenere solo aggregati: la colonna reference non ha un valore unico per il gruppo (l'intera tabella). Si ottiene la riga che ha il massimo con una sottoquery.