6. SQL avanzato
6.1. Introduzione
In questo capitolo presentiamo
- ulteriori sintassi dell’istruzione SELECT che la rendono un comando di interrogazione molto potente, in particolare per interrogare più tabelle contemporaneamente.
- sintassi estese dei comandi già trattati
Per illustrare i vari ordini, lavoreremo con le seguenti tabelle utilizzate per la gestione degli ordini in una piccola-media azienda di distribuzione di libri:
6.1.1. la tabella CLIENTI
Memorizza le informazioni relative ai clienti dell'azienda:
 |
Un identificatore univoco per il cliente - chiave primaria | |
nome del cliente | |
I=Privato, E=Azienda, A=Ente pubblico | |
nome per un privato | |
Nome della persona di contatto presso la sede del cliente (nel caso di un'azienda o di un ente pubblico) | |
Indirizzo del cliente - via | |
Città | |
CAP | |
Telefono | |
Da quando è cliente? | |
S (Sì) se il cliente ha un debito nei confronti dell'azienda e N (No) in caso contrario. |
6.1.2. La tabella ARTICOLI
Memorizza le informazioni relative ai prodotti venduti, in questo caso libri. La sua struttura è la seguente:
Un identificatore univoco per un libro (ISBN = International Standard Book Number) - chiave primaria | |
Titolo del libro | |
Codice che identifica in modo univoco un editore | |
Nome dell'autore | |
Sommario del libro | |
Quantità venduta quest'anno | |
Quantità venduta l'anno precedente | |
Data dell'ultima vendita | |
Quantità dell'ultima consegna | |
Data dell'ultima consegna | |
Prezzo di vendita | |
Costo di acquisto | |
Quantità minima d'ordine | |
Livello minimo delle scorte | |
Quantità in magazzino |
Il contenuto potrebbe essere il seguente:
6.1.3. la tabella ORDERS
Memorizza le informazioni relative agli ordini effettuati dai clienti. La sua struttura è la seguente:
Un identificatore univoco per un ordine - chiave primaria | |
ID cliente per questo ordine - chiave esterna - riferimento a CUSTOMERS(ID) | |
Data di inserimento dell'ordine | |
O (Sì) se l'ordine è stato annullato e N (No) in caso contrario. |
6.1.4. La tabella DETAILS
Contiene i dettagli di un ordine, ovvero i titoli e le quantità dei libri ordinati. La sua struttura è la seguente:
Numero d'ordine - chiave esterna che fa riferimento alla colonna NOCMD della tabella COMMANDES | |
Numero del libro ordinato - chiave esterna che fa riferimento alla colonna ISBN nella tabella BOOKS | |
Quantità ordinata |
Il contenuto potrebbe essere il seguente:
Sopra, vediamo che l'ordine n. 3 (NOCMD) riguarda tre libri. Ciò significa che il cliente ha ordinato tre libri contemporaneamente. I dati relativi a questo cliente si trovano nella tabella [ORDERS], dove vediamo che l'ordine n. 3 è stato effettuato dal cliente n. 5. La tabella [CUSTOMERS] ci dice che il cliente n. 5 è la società NetLogos di Segré.
6.2. L'istruzione SELECT
In questa sezione, intendiamo approfondire la nostra comprensione dell'istruzione SELECT introducendo nuove sintassi.
6.2.1. Sintassi di una query su più tabelle
SELECT colonna1, colonna2, ... FROM tabella1, tabella2, ..., tabellap WHERE condizione ORDER BY ... | |
La novità in questo caso è che le colonne column1, column2, ... provengono da più tabelle table1, table2, ... Se due tabelle hanno colonne con lo stesso nome, l'ambiguità viene risolta utilizzando la notazione tablei.columnj. La condizione può essere applicata a colonne provenienti da tabelle diverse. |
Come funziona
Viene costruito il prodotto cartesiano delle tabelle table1, table2, ..., tablep. Se n_i è il numero di righe nella tabella table_i, la tabella risultante avrà n₁*n₂*...*n_p righe contenenti tutte le colonne delle diverse tabelle. | |
A questa tabella viene applicata la condizione WHERE. Viene così generata una nuova tabella | |
Questa tabella viene ordinata secondo il metodo specificato in ORDER. | |
Vengono visualizzate le colonne specificate dopo SELECT. |
Esempi
Utilizziamo le tabelle presentate in precedenza. Vogliamo vedere i dettagli degli ordini effettuati dopo il 25 settembre:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Si noti che dopo FROM, elenchiamo i nomi di tutte le tabelle alle cui colonne facciamo riferimento. Nell'esempio precedente, le colonne selezionate appartengono tutte alla tabella DETAILS. Tuttavia, la condizione fa riferimento alla tabella ORDERS. Da qui la necessità di elencare quest'ultima dopo FROM. L'operazione che verifica l'uguaglianza tra colonne in due tabelle diverse è spesso chiamata equijoin.
La query SELECT avrebbe potuto essere scritta anche come segue:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Continuiamo con i nostri esempi. Vogliamo ottenere lo stesso risultato di prima, ma con il titolo del libro ordinato, anziché il suo codice ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
Lo stesso risultato si ottiene con la seguente query SQL, che è meno leggibile:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
Sopra, vengono eseguiti due inner join con la tabella [DETAILS]:
- uno con la tabella [ORDERS] per accedere alla data dell'ordine di un libro
- uno con la tabella [ARTICLES] per accedere al titolo del libro ordinato
Vogliamo anche il nome del cliente che ha effettuato l'ordine:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Vogliamo anche le date degli ordini e che queste date vengano visualizzate in ordine decrescente:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
Ecco alcune regole da seguire quando si creano i join:
- Dopo SELECT, elencare le colonne che si desidera visualizzare. Se la colonna esiste in più tabelle, precederla con il nome della tabella.
- Dopo FROM, elencare tutte le tabelle che saranno interrogate dall'istruzione SELECT, ovvero le tabelle contenenti le colonne elencate dopo SELECT e WHERE.
6.2.2. Auto-join
Vogliamo trovare i libri che hanno un prezzo al dettaglio superiore a quello del libro "Using SQL":
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
Le due tabelle coinvolte nel join sono identiche in questo caso: entrambe sono la tabella «articles». Per distinguerle, assegniamo loro degli alias: «articles a» e «articles b». L’alias della prima tabella è «a», mentre quello della seconda è «b». Questa sintassi può essere utilizzata anche se le tabelle sono diverse. Quando si utilizza un alias, esso deve essere impiegato in tutta l’istruzione SELECT al posto della tabella a cui si riferisce.
6.2.3. Join esterno
Vogliamo identificare i clienti che hanno effettuato un acquisto a settembre, insieme alla data dell'ordine. Gli altri clienti vengono visualizzati senza questa data:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
È sorprendente che in questo caso non otteniamo il risultato corretto. Dovremmo avere tutti i clienti presenti nella tabella [CLIENTS], ma non è così. Se pensiamo a come funziona un outer join, ci rendiamo conto che i clienti che non hanno effettuato alcun acquisto sono stati abbinati a una riga vuota nella tabella ORDERS e quindi a una data vuota (un valore NULL nella terminologia SQL). Questa data non soddisfa la condizione impostata per la data, quindi il cliente corrispondente non viene visualizzato. Proviamo qualcos'altro:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Questa volta otteniamo la risposta corretta alla nostra domanda.
6.2.4. Query annidate
SELECT colonna[e] FROM tabella[e] WHERE espressione operatore di query ORDER BY ... | |
Una query è un'istruzione SELECT che restituisce un insieme di 0, 1 o più valori. Abbiamo quindi una condizione WHERE del tipo espressione operatore (val1, val2, ..., vali) L'espressione e vali devono essere dello stesso tipo. Se la query restituisce un singolo valore, ci riduciamo a una condizione del tipo espressione operatore valore che ci è familiare. Se la query restituisce un elenco di valori, possiamo utilizzare i seguenti operatori:
espressione IN (val1, val2, ..., vali): vero se l'espressione restituisce uno degli elementi dell'elenco vali.
opposto di IN
deve essere preceduto da =, !=, >, >=, <, <= espressione >= ANY (val1, val2, .., valn): vero se l'espressione è >= uno dei valori vali nell'elenco
deve essere preceduta da =, !=, >, >=, <, <= espressione >= ALL (val1, val2, .., valn): vero se l'espressione è >= tutti i valori validi nell'elenco
query: vero se la query restituisce almeno una riga. |
Esempi
Riprendiamo la domanda già risolta con un equijoin: visualizza i titoli con un prezzo di vendita superiore a quello del libro "Using SQL".
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
Questa soluzione sembra più intuitiva rispetto all'equijoin. Eseguiamo un filtro iniziale con un SELECT, poi un secondo filtro sull'insieme risultante. In questo modo possiamo eseguire diversi filtri in sequenza.
Vogliamo trovare i titoli con un prezzo di vendita superiore al prezzo medio di vendita:
Quali clienti hanno ordinato i titoli restituiti dalla query precedente?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Spiegazione
- Selezioniamo dalla tabella DETAILS i codici ISBN presenti tra i libri con un prezzo superiore al prezzo medio dei libri.
- Nelle righe selezionate nel passaggio precedente, l'ID cliente (IDCLI) non è presente. Si trova nella tabella ORDERS. Il collegamento tra le due tabelle è stabilito tramite il numero d'ordine (NOCMD), da cui la condizione di join ORDERS.nocmd=DETAILS.nocmd.
- Un singolo cliente potrebbe aver acquistato uno dei libri in questione più volte, nel qual caso il suo codice IDCLI apparirebbe più volte. Per evitare ciò, inseriamo la parola chiave DISTINCT dopo SELECT. DISTINCT elimina generalmente i duplicati dalle righe restituite da una query SELECT.
- Per recuperare il nome del cliente, dovremmo eseguire un join aggiuntivo tra le tabelle ORDERS e CUSTOMERS, come mostrato nella seguente query.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Trova i clienti che non hanno effettuato ordini dal 24 settembre:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
Abbiamo visto che è possibile filtrare le righe in modi diversi dall'utilizzo della clausola WHERE: utilizzando la clausola HAVING in combinazione con la clausola GROUP BY. La clausola HAVING filtra gruppi di righe.
Proprio come con la clausola WHERE, la sintassi
HAVING expression opérateur requête
è possibile, con il vincolo già menzionato che l'espressione deve essere una delle espressioni expri presenti nella clausola
GROUP BY expr1, expr2, ...
Esempi
Quali sono i dati di vendita dei libri con un prezzo superiore a 200F?
Per prima cosa, visualizziamo le quantità vendute per titolo:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Ora filtriamo i titoli:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
Forse in modo più ovvio, avremmo potuto scrivere:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Query annidate
Nel caso delle query annidate, esiste una query padre (la query più esterna) e una query figlio (la query più interna). La query padre viene valutata solo dopo che la query figlio è stata completamente valutata.
Le query correlate hanno la stessa sintassi, con la seguente piccola differenza: la query figlia esegue un join sulla tabella della query padre. In questo caso, la coppia di query padre-figlia viene valutata ripetutamente per ogni riga della tabella padre.
Esempio
Riprendiamo l'esempio in cui vogliamo i nomi dei clienti che non hanno effettuato ordini dal 24 settembre:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
La query principale opera sulla tabella dei clienti. La query secondaria esegue un join tra le tabelle dei clienti e degli ordini. Si tratta quindi di una query correlata. Per ogni riga della tabella dei clienti, la query secondaria viene eseguita: cerca l'ID del cliente negli ordini effettuati dopo il 24 settembre. Se non ne trova nessuno (not exists), viene visualizzato il nome del cliente. Quindi, passa alla riga successiva della tabella dei clienti.
6.2.6. Criteri per la scrittura dell'istruzione SELECT
Abbiamo visto, in diverse occasioni, che è possibile ottenere lo stesso risultato utilizzando diverse istruzioni SELECT. Facciamo un esempio: visualizzare i clienti che hanno effettuato un ordine:
Join
Query annidate
restituisce lo stesso risultato.
Query correlate
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
restituisce lo stesso risultato.
Gli autori Christian MAREE e Guy LEDANT, nel loro libro "SQL: Introduzione, Programmazione e Padronanza", suggeriscono alcuni criteri di selezione:
Prestazioni
L'utente non sa come il DBMS "riesca" a trovare i risultati richiesti. È quindi solo attraverso l'esperienza che scoprirà che una query è più efficiente di un'altra. MAREE e LEDANT affermano, sulla base della loro esperienza, che le query correlate sembrano generalmente più lente delle query annidate o dei join.
Formulazione
La formulazione che utilizza query annidate è spesso più leggibile e intuitiva rispetto ai join. Tuttavia, non è sempre utilizzabile. È opportuno sottolineare in particolare due punti:
- Le tabelle contenenti le colonne specificate nella clausola SELECT (SELECT col1, col2, ...) devono essere elencate dopo la parola chiave FROM. Viene quindi eseguito il prodotto cartesiano di queste tabelle, noto come join.
- Quando la query visualizza i risultati di una singola tabella e per filtrare le righe di tale tabella è necessario consultare un'altra tabella, è possibile utilizzare le query annidate.
6.3. Estensioni della sintassi
Per comodità, abbiamo presentato per lo più sintassi abbreviate per i vari comandi. In questa sezione, presentiamo le loro sintassi espanse. Sono intuitive perché sono analoghe a quelle del comando SELECT, ampiamente studiato.
INSERT
INSERT INTO tabella (col1, col2, ...) VALUES (val1, val2, ...) | |
INSERT INTO tabella (col1, col2, ..) (query) | |
Sono state presentate queste due sintassi |
DELETE
DELETE FROM tabella WHERE condizione | |
Questa sintassi è ben nota. Si noti che la condizione può contenere una query che utilizza la sintassi WHERE espressione operatore (query) |
AGGIORNA
AGGIORNA tabella SET col1=espr1, col2=espr2, ... WHERE condizione | |
Questa sintassi è già stata presentata. Si noti che la condizione può contenere una query che utilizza la sintassi WHERE espressione operatore (query) |
AGGIORNA tabella SET (col1, col2, ..) = query1, (cola, colb, ..) = query2, ... WHERE condizione | |
I valori assegnati alle varie colonne possono provenire da una query. |