Negli articoli precedenti abbiamo visto molti costrutti del linguaggio SQL. In particolare, con l’utilizzo delle tabelle derivate e la loro evoluzione, ossia le Common Table Expression (CTE) siamo riusciti a scrivere query molto complesse. Questi costrutti sono ormai presenti da anni nei database relazionali, sia commerciali che open source. Tuttavia, prima del loro avvento le query venivano scritte in maniera differente quando si poteva rispondere ad una richiesta mediante una sola interrogazione. Infatti, In alcuni casi può essere necessario legare l’esecuzione di un’interrogazione nidificata al valore di uno o più attributi in un’interrogazione più esterna. Questo legame, chiamato correlazione, viene espresso da una o più condizioni di correlazione che sono indicate nella clausola WHERE dell’interrogazione nidificata che la richiede. Il predicato legherà, pertanto. alcuni attributi delle tabelle inserite nella clausola FROM dell’interrogazione nidificata con alcuni attributi delle tabelle definite nella clausola FROM delle interrogazioni più esterne. Detto così sembra molto complesso, ma in realtà è più semplice a farsi che a dirsi.
In questo articolo vedremo alcuni esempi di correlazione sia richiesti da alcuni operatori del linguaggio SQL sia per altri scenari. Come negli articoli precedenti, useremo lo schema relativo alle proiezioni dei film nei cinema. Lo riportiamo qui sotto per chi non avesse letto gli altri articoli.
FILM (CodF, Titolo, Data_uscita, Genere, DurataMinuti)
CINEMA (CodC, Nome, Indirizzo, Città, SitoWeb*)
SALA (CodC, NumeroSala, Capienza)
PROIEZIONE (CodC, NumeroSala, Data, OraInizio, OraFine, CodF)
Vi ricordo che la notazione usata per descrivere lo schema usa la sottolineatura per indicare i campi che compongono la chiave primaria e l’asterisco per segnalare la possibilità di assegnare valori NULL al campo. Infine, per semplificare, le chiavi esterne hanno lo stesso nome delle chiavi primarie a cui si riferiscono. Procediamo quindi a vedere i vari operatori.
EXISTS e NOT EXISTS
Gli operatori che usano la correlazione per il loro funzionamento sono:
- EXISTS
- NOT EXISTS
Questi due operatori sono “fratellastri” rispettivamente degli operatori IN e NOT IN visti negli articoli SELECT: operatore IN e SELECT: operatore NOT IN e costruttore di tupla. Tuttavia, sia la loro sintassi che il loro comportamento differiscono.
Infatti, gli operatori EXISTS e NOT EXISTS ammettono come parametro un’interrogazione nidificata la cui esecuzione verrà legata alla query più esterna mediante una o più condizioni di correlazione. L’operatore EXISTS restituisce il valore vero solo se l’interrogazione nidificata fornisce un insieme non vuoto (ossia restituisce almeno un record). Diversamente, restituisce il valore falso se l’interrogazione interna restituisce l’insieme vuoto (ossia non restituisce nessun record). L’operatore NOT EXISTS si comporta in modo duale. Infine, la clausola SELECT della query nidificata può includere qualsiasi attributo ma è irrilevante ai fini del risultato. Vediamo un esempio per capire il legame che lega i vari operatori.
Supponiamo di voler trovare i titoli dei film che non sono mai stati proiettati nei cinema di Torino. Come al solito dividiamo il problema in due passi
- Trovare i codici dei film proiettati almeno una volta in un cinema di Torino
- Visualizzare i titoli dei film che non appartengono all’insieme trovato precedentemente
La prima parte si può risolvere con la seguente query
SELECT CodF
FROM PROIEZIONE P, CINEMA C
WHERE P.CodC=C.CodC
AND Città=’Torino’;
A questo punto dovremmo escludere questi codici dal risultato finale. La cosa più naturale è usare l’operatore NOT IN sull’attributo CodF. Otterremmo quindi la seguente query:
SELECT Titolo
FROM FILM
WHERE CodF NOT IN (
SELECT CodF
FROM PROIEZIONE P, CINEMA C
WHERE P.CodC=C.CodC
AND Città=’Torino’);
Si potrebbe ragionare, però, in maniera differente. Per ciascun film cerchiamo se esiste una proiezione nei cinema della città di Torino. Se esiste, quel film deve essere escluso dal risultato, altrimenti deve essere visualizzato. Questa è esattamente la logica della NOT EXISTS. Riscriviamo quindi la query come segue
SELECT Titolo
FROM FILM F
WHERE NOT EXISTS (
SELECT *
FROM PROIEZIONE P, CINEMA C
WHERE P.CodC=C.CodC
AND Città=’Torino’
AND P.CodF=F.CodF);
L’ultima condizione della query nidificate determina la correlazione tra l’esecuzione della stessa e quella esterna. Ciò significa che, man mano si scansiona la tabella FILM, viene generata una query le cui condizioni nella clausola WHERE vengono aggiornate in base al valore letto nel campo CodF. Ovviamente il database non effettua questa procedura in quanto risulterebbe troppo onerosa in termini di risorse e tempo di esecuzione, ma la logica di fondo è quella.
Come si evince da questo esempio è possibile esprimere la stessa interrogazione con la NOT In che con la NOT EXISTS. Ma allora perché i risultati potrebbero differire? Per prima cosa dobbiamo specificare che nella maggior parte dei casi la scrittura con un operatore o il suo “fratellastro” è ininfluente ai fini del risultato. Tuttavia, se l’attributo su cui si effettua la correlazione (ossia quello di confronto per l’inclusione/esclusione) può presentare valori NULL allora l’insieme dei record filtrati non sarà equivalente. Gli operatori EXISTS e NOT EXISTS gestiscono anche i valori NULL, mentre i loro “fratellastri” no.
Correlazione tra query
La correlazione può essere usata anche senza l’impiego degli operatori EXISTS e NOT EXISTS. Infatti, potremmo avere la necessità di legare il risultato di query nidificata a ciò che stiamo analizzando nella query esterna. Prima di vedere un esempio concreto, è necessario fare alcune precisazioni:
- Non si possono esprimere condizioni di correlazione in interrogazioni allo stesso livello di nidificazione.
- La correlazione si può esprimere solo tra una query di un determinato livello di nidificazione e gli attributi delle tabelle della query del livello di nidificazione subito superiore. Non si può pertanto usare la correlazione in una query esterna né “saltare” livelli di nidificazione.
Supponiamo di voler trovare le sale che hanno una capienza maggiore della capienza media di tutte le sale dei cinema della stessa città. In sintesi, se i cinema di Torino hanno una capienza media di 50 posti, vogliamo trovare le sale che hanno un valore per l’attributo capienza maggiore di 50. Ovviamente, cambiando città il valore medio cambierà di conseguenza.
Iniziamo a scrivere la query che calcola la capienza media delle sale.
SELECT AVG(Capienza)
FROM SALA S, CINEMA C
WHERE S.CodC=C.CodC
Questa query, però, ci restituisce un valore calcolato su tutte le sale. Come facciamo a limitare il suo raggio d’azione alla città che a cui appartiene una sala? Dobbiamo usare la correlazione! Nella query esterna andremo a scansionare la tabella SALA imponendo che la capienza della singola sala sia maggiore della media calcolata dalla query precedente a cui però aggiungeremo la condizione che la città sia uguale a quella che stiamo analizzando esternamente. Il risultato finale sarà:
SELECT CodC, NumeroSala
FROM SALA S1, CINEMA C1
WHERE S1.CodC=C1.CodC
AND Capienza > (SELECT AVG(Capienza)
FROM SALA S2, CINEMA C2
WHERE S2.CodC=C2.CodC
AND C1.Città=C2.Città);
L’uso degli alias ci aiuta a distinguere le varie istanze della stessa tabella e ad esprimere in modo corretto la correlazione.
Questo esempio ci evidenzia la difficoltà di scrivere in modo diretto query di questo genere. Esistono delle alternative più semplici? Ovviamente sì: le tabelle derivate e le CTE. Possiamo, infatti, scrivere la stessa query usando ciò che abbiamo visto negli articoli XXX e YYY. Dobbiamo solo dividere il problema in maniera differente:
- Per ogni città calcolare la capienza media
- Aggiungere il valore della capienza media ad ogni città
- Filtrare le sale in base alla capienza
Usiamo le CTE per semplicità anche di lettura della soluzione. La prima query sarà quindi:
WITH
MEDIA_CITTA AS
(SELECT Città, AVG(Capienza) AS CapienzaMedia
FROM SALA S, CINEMA C
WHERE S.CodC=C.CodC
GROUP BY Città)
A questo punto per rispondere agli altri due passi del problema è sufficiente mettere in join la CTE appena definita con la tabella CINEMA e poi, mediante un’altra join, recuperare le sale di ciascun cinema e confrontare la capienza con la capienza media. La query finale sarà:
WITH
MEDIA_CITTA AS
(SELECT Città, AVG(Capienza) AS CapienzaMedia
FROM SALA S, CINEMA C
WHERE S.CodC=C.CodC
GROUP BY Città)
SELECT CodC, NumeroSala
FROM SALA S, CINEMA C, MEDIA_CITTA M
WHERE S.CodC=C.CodC
AND M.Città=C.Città
AND Capienza > CapienzaMedia;
Come si evince l’uso delle CTE e delle tabelle derivate risulta molto più intuitivo della correlazione e non ci pone nessun vincolo sui livelli di annidamento che possiamo utilizzare.
