Il linguaggio SQL permette di estrarre moltissime informazioni dai database relazionali. Scrivere le query però non è sempre semplice. Come visto negli articoli precedenti, SELECT: struttura di una query semplice e SELECT: query con JOIN e GROUP BY, ci sono diverse clausole da definire affinché sia la sintassi che i dati estratti siano corretti. Ma come si fa quando la richiesta è molto complessa? Il segreto è dividere il problema in parti più piccole così da poterle risolvere in modo più semplice. Dopodiché, si mettono insieme le varie soluzioni per comporre la risposta finale. Per fare un’analogia è come con i mattoncini LEGO, si costruisce un mattoncino alla volta per arrivare a creare anche set molto complessi, oltre che belli da vedere.
Se i mattoncini, nel linguaggio SQL, sono query semplici che usano la sintassi vista fino ad ora, quali sono le istruzioni per costruire la query finale. Esistono quattro tipologie di strategie per unire diverse query:
- Query nidificate
- Operatori insiemistici
- Tabelle derivate
- Common Table Expression (CTE)
Anche in questo articolo useremo lo schema degli altri articoli 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.
Query nidificate
In questo articolo parleremo delle query nidificate ed in particolare dell’operatore IN. Ma prima di capire come funzione questo operatore è necessario definire cosa sono le query nidificate. Esse non sono nient’altro che un’istruzione SELECT contenuta all’interno di un’altra interrogazione. Ciò permette di suddividere un problema complesso in sottoproblemi più semplici e usare i risultati di una ricerca come input di un’altra. Le query nidificate possono essere inserite come predicato nelle clausole WHERE e HAVING, ma anche al posto di una tabella nella clausola FROM.
L’esempio più semplice è usare una query nidificata nella clausola WHERE. Supponiamo di voler trovare tutte le sale con la capienza massima all’interno del nostro database. Il problema si deve necessariamente dividere in 2 parti:
- Trovare la capienza massima all’interno della tabella SALA
- Filtrare le sale che hanno una capienza pari alla massima calcolata nel punto precedente
Il primo punto si risolve mediante l’uso della funzione di aggregazione MAX sull’intera tabella SALA, come riportato di seguito
SELECT MAX(Capienza)
FROM SALA;
Il secondo problema a questo punto si risolve imponendo nella clausola di WHERE che la capienza deve essere uguale alla query del punto precedente. L’istruzione sarà quindi:
SELECT *
FROM SALA
WHERE capienza=(SELECT MAX(Capienza)
FROM SALA);
Attenzione
È possibile utilizzare operatori come l’uguaglianza, disuguaglianza e confronto esclusivamente se è noto a priori che il risultato della SELECT nidificata è sempre un solo valore.
Così possiamo risolvere un po’ di problemi, ma abbiamo ancora delle limitazioni in alcuni scenari. Vediamo come possiamo ottenere ancora più capacità di manipolazione dei dati grazie al linguaggio SQL.
Operatore IN
Supponiamo di voler calcolare per ciascun film il numero di proiezioni che sono state fatte nei cinema di Torino. La query SQL, usando la sintassi vista negli altri articoli prevede di:
- usare le tabelle FILM, PROIEZIONE e CINEMA (clausola di FROM),
- imporre le opportune condizioni di join oltre al filtro sulla città (clausola di WHERE),
- raggruppare i risultati mediante il codice del film e il titolo (clausola di GROUP BY)
- infine, visualizzare il titolo e il numero di proiezione mediante la funzione di aggregazione COUNT (clausola di SELECT)
La query risultante sarà:
SELECT Titolo, COUNT(*) AS NumProiezioni
FROM FILM F, PROIEZIONE P, CINEMA C
WHERE F.CodF=P.CodF
AND P.CodC=C.CodC
AND Città = ‘Torino’
GROUP BY CodF, Titolo;
In questo caso la query non è complessa, ma potrebbe non essere subito così intuitivo scrivere la soluzione appena vista. Come possiamo procedere per semplificare il problema? Una possibilità è di dividere il problema in due parti:
- trovare i cinema di Torino
- calcolare il numero di proiezioni di ciascun film proiettati nei cinema del punto precedente
Prima query
La prima query è molto semplice! Richiede solo di filtrare, mediante una clausola di WHERE i record della tabella CINEMA. Ci basterà individuare i codici visto che non ci servono altre informazioni. Ciò risulta nel seguente codice SQL
SELECT CodC
FROM CINEMA
WHERE Città=’Torino’;
Seconda query
Il secondo punto è calcolare le proiezioni di questi cinema. Semplifichiamo ulteriormente! Calcoliamo solo le proiezioni di ciascun film. Dalla tabella proiezioni è sufficiente raggruppare per CodF e poi contare il numero di record con lo stesso valore di CodF. Poiché i codici, ahimè, non sono così esplicativi ci serve anche usare la tabella FILM per recuperare il titolo. La query risulterà come segue
SELECT Titolo, COUNT(*) AS NumProiezioni
FROM FILM F, PROIEZIONE P
WHERE F.CodF=P.CodF
GROUP BY CodF, Titolo;
Uniamo le query
Ora non ci resta che mettere insieme le due soluzioni. Come? In teoria ci basterebbe dire che tutti i record della tabella PROIEZIONE che assumono un valore di CodC uguale ad uno dei valori restituiti dalla prima query devono essere considerati nel calcolo finale. Ma in pratica?
Molti potrebbero pensare che si esegue la prima query, memorizzo il risultato in una variabile che poi uso nella seconda query. Potrebbe funzione, ma richiederebbe la sottomissione di due query separate al database con conseguente aumento del tempo di risposta anche solamente dovuto alla latenza di rete. Inoltre, questo approccio funzionerebbe solo all’interno di un linguaggio di programmazione e comunque implicherebbe sempre il problema di usare una lista di valori per filtrare i record di interesse.
Quindi? Nessun problema! Esiste un operatore che indica l’inclusione di un valore all’interno di un insieme: l’operatore IN. Questo operatore può essere usato nella clausola di WHERE e la sua sintassi è la seguente:
NomeAttributo IN (InterrogazioneNidificata)
dove il nome attributo deve essere compatibile con l’attributo restituito dalla query nidificata. La query nidificata può essere anche sostituita con una lista di valori, se necessario. Questa ultima opzione, però, è caldamente sconsigliata. Si usa, infatti, solo in casi in cui questi valori derivino da interazioni esterne con i dati del DB, come ad esempio quando un utente sceglie più opzioni in un form. Avere una query nidificata mediante l’operatore IN, permette al DBMS di ottimizzare l’esecuzione della query e rendere il tutto più veloce ed efficiente.
Tornando al nostro esempio, sarà sufficiente imporre che i CodC della tabella PROIEZIONE devono appartenere a quelli restituiti dalla prima query. La query sarà, pertanto, la seguente:
SELECT Titolo, COUNT(*) AS NumProiezioni
FROM FILM F, PROIEZIONE P
WHERE F.CodF=P.CodF
AND CodC IN (
SELECT CodC
FROM CINEMA
WHERE Città = ‘Torino’
)
GROUP BY CodF, Titolo;
Ovviamente si possono fare esempi molto più complessi, ma ciò che c’è da ricordare è che è sufficiente dividere il problema per trovare una soluzione molto più semplice da scrivere!
JOIN al posto dell’operatore IN
Come visto nell’esempio precedente, abbiamo risolto il problema con due approcci differenti. Il primo è stato mettere in join tutte le tabelle che ci servivano e poi effettuare gli opportuni filtraggi e raggruppamenti. Nel secondo approccio abbiamo diviso il problema in due pezzi, risolto entrambi e poi combinati insieme con l’operatore IN. A questo punto la domanda sorge spontanea: quale delle due soluzioni è migliore? In teoria nessuna delle due!!! Sono equivalenti.
Infatti, l’operatore IN può essere espresso in molti casi anche mediante un join. È sufficiente
- inserire nella clausola FROM della query principale le tabelle referenziate nella clausola FROM della query nidificata,
- nella WHERE le opportune condizioni di join (uguaglianza sull’attributo usato dall’operatore IN),
- aggiungere, infine, eventuali predicati di filtraggio aggiunti nella clausola WHERE della query nidificata
Quindi, l’operatore IN si usa solo quando si è principianti? No! Per prima cosa l’operatore IN riduce la richiesta di risorse da parte del DBMS. Infatti, tutte le risorse usate dalla query nidificata non potranno essere accedute direttamente dalla query principale, il che comporta un rilascio di memoria e, talvolta, una maggiore velocità di esecuzione. Ciò comporta che, se le informazioni contenute in una tabella non devono essere visualizzate, ma solo usate per filtrare, si può usare senza problemi l’operatore IN. Diversamente, è necessario usare il join per non istanziare inutilmente più volte la stessa tabella.
In alcuni casi non si può tradurre l’IN con un join. Pensate ad esempio di avere nella query nidificata sia una clausola di group by che di having. In questo caso non si può assolutamente riportare nella query esterna gli stessi vincoli senza alterare il risultato finale.
Un esempio di questo scenario potrebbe essere una variante della query precedente: per ciascun film calcolare il numero di proiezioni che sono state fatte nei cinema con una capienza media superiore a 100. Dividiamo nuovamente il problema:
- trovare i cinema con capienza media superiore a 100
- calcolare il numero di proiezioni di ciascun film proiettati nei cinema del punto precedente
La seconda query l’abbiamo già risolta in precedenza. La prima, invece, si tratta di calcolare per ogni cinema la capienza media delle sale e filtrare i cinema il cui valore è inferiore a 100. La query risulterà la seguente:
SELELCT CodC
FROM CINEMA C, SALA S
WHERE C.CodC=S.CodC
GROUP BY CodC
HAVING AVG(Capienza)>100;
La soluzione finale sarà pertanto:
SELECT Titolo, COUNT(*) AS NumProiezioni
FROM FILM F, PROIEZIONE P
WHERE F.CodF=P.CodF
AND CodC IN (SELELCT CodC
FROM CINEMA C, SALA S
WHERE C.CodC=S.CodC
GROUP BY CodC
HAVING AVG(Capienza)>100)
GROUP BY CodF, Titolo;
Come potete ben capire non è possibile usare un join al posto dell’operatore IN in quanto non potremmo gestire correttamente e contemporaneamente le clausole di GROUP BY e HAVING.
