SELECT, interrogare il database

Nella lezione precedente abbiamo esaminato i costrutti che SQL mette a disposizione per inserire i dati in un database relazionale. In questa e nelle prossime lezioni ci concentreremo sulle istruzioni che ci permettono di estrarre i dati che ci interessano. L'istruzione SQL preposta a tale scopo è SELECT. L'interrogazione è la funzionalità più usata di un database e le clausole di cui dispone l'istruzione SELECT sono numerose e a volte possono dar luogo a combinazioni piuttosto complicate.

La sintassi completa è la seguente:

SELECT [DISTINCT ] lista_elementi_selezione
FROM lista_riferimenti_tabella
[ WHERE espressione_condizionale ]
[ GROUP BY lista_colonne ]
[ HAVING espressione_condizionale ]
[ ORDER BY lista_colonne ]

Come si vede, le uniche clausole obbligatorie sono SELECT e FROM. Quest'ultima, detta clausola di selezione, stabilisce da quale tabella (o da quali) estrarre i dati, mentre la prima è detta clausola di proiezione e stabilisce quali colonne devono essere riportate nel risultato finale.

La clausola WHERE definisce invece un filtro sulle righe che verranno analizzate, mentre ORDER BY indica l'ordinamento da applicare al risultato finale. Se WHERE non viene specificata, non viene applicato alcun filtro sulle righe, che quindi vengono esaminate tutte. Se invece non viene specificato alcun ordinamento, le righe vengono restituite senza un ordine definito, generalmente così come vengono trovate in tabella. Vedremo filtro e ordinamento in dettaglio nella prossima lezione.

Nella lezione 11 analizzeremo, invece, le clausole GROUP BY e HAVING, entrambe riguardanti le aggregazioni.

Per quanto riguarda selezione e la proiezione, di cui ci occupiamo in questa lezione, vediamo subito un esempio:

SELECT Person.surname  FROM Person

Questa query estrae dalla tabella Person tutti gli elementi, restituendo solamente la colonna surname. Nel nostro caso, il risultato è una tabella di 20 righe con i cognomi dei nostri autori. Si possono indicare più colonne separandole con una virgola:

SELECT Person.surname, Person.given_names FROM Person

La sintassi completa prevede di specificare la colonna con la tabella di appartenenza; quest'ultima, inoltre, si può omettere se non ci sono ambiguità:

SELECT surname, given_names FROM Person

Si può utilizzare l'asterisco (*) per indicare che si vogliono analizzare tutte le colonne di una tabella. Ad esempio:

SELECT Person.* FROM Person

che, se non ci sono ambiguità, equivale alla forma più comunemente utilizzata:

SELECT * FROM Person

È possibile anche specificare dei nomi alternativi (alias) per le colonne restituite come risultato, usando la parola chiave AS:

SELECT surname, given_names AS name FROM Person

Il risultato produrrà due colonne, la prima denominata surname, e la seconda name (in virtù dell'alias definito).

L'uso degli alias è particolarmente comodo quando si usano le espressioni: infatti, è possibile utilizzare espressioni SQL nella clausola di proiezione per effettuare calcoli o elaborazioni sui valori presenti nei campi. Ad esempio, la seguente interrogazione restituisce il cognome e l'iniziale del nome di ogni persona:

SELECT surname, SUBSTRING(given_names, 1, 1) as iniziale FROM Person

Le possibili espressioni utilizzabili sono moltissime e dipendono in gran parte dal database utilizzato, quindi spesso le stesse funzioni hanno nomi o sintassi diverse in MySQL, Oracle o SQL Server. Purtroppo ciò limita la portabilità delle espressioni tra i vari database. Per i dettagli è quindi necessario ricorrere alla documentazione ufficiale dei singoli database.

Ci sono funzioni per lavorare con le date, con i numeri, con le stringhe di testo o per gestire i valori NULL. Ad esempio, la funzione COALESCE restituisce il primo valore non nullo tra le espressioni indicate:

SELECT COALESCE(surname, given_names, 'Senza nome') AS name FROM Person

Esistono anche funzioni condizionali, come ad esempio CASE, che restituisce un valore in base al soddisfacimento di un certo predicato. Per esempio, la seguente query calcola l'iniziale solo quando (CASE WHEN) il nome della persona è più lungo (LENGTH) di cinque caratteri:

SELECT CASE WHEN LENGTH(given_names) > 5
  THEN CONCAT(SUBSTRING(given_names, 1, 1), '.')
  ELSE given_names
  END AS Abbrev
 FROM Person

Si possono concatenare più espressioni CASE WHEN, esattamente come un'espressione else if del C o degli altri linguaggi imperativi.

Nella lezione 11 vedremo una particolare classe di funzioni, dette di aggregazione, che possono essere usate per eseguire operazioni quali conteggi, somme, medie.

DISTINCT, eliminare i duplicati

La prima query che abbiamo visto restituirebbe righe duplicate nel caso in cui nella tabella fossero presenti persone con lo stesso cognome. Per evitare ciò occorre specificare l'opzione DISTINCT:

SELECT DISTINCT surname FROM Person

Questa opzione fa sì che nel risultato non ci siano righe uguali tra loro, ed è utile soprattutto quando si lavora con i join. Un join (congiunzione) è una funzione che stabilisce come combinare righe di due tabelle diverse per ottenere una tabella unica. Si distinguono generalmente tre tipi di join, che vediamo di seguito.

Inner join

Questa operazione, che in SQL è implementata dalla clausola INNER JOIN o semplicemente JOIN, combina solo le righe delle due tabelle che soddisfano un certo predicato di confronto, come in un'operazione di intersezione.

Si possono effettuare confronti tra date o numeri; ad esempio per avere tutti i libri usciti dopo la data di pubblicazione di ogni articolo:

SELECT Book.title, Article.title
  FROM Book
  JOIN Article on Article.pub_year > Book.pub_year

Comunque nella maggior parte dei casi questo predicato è un'uguaglianza tra ID:

SELECT surname, given_names, title
  FROM Person
  JOIN Author on person.ID = Author.personID
  JOIN Book on Book.ID = Author.publicationID

Questa query restituisce i nominativi e i titoli dei libri pubblicati.

Outer join

La query precedente restituirà solo i libri e le persone per cui esiste un collegamento. Quindi non restituirà né le persone che non hanno scritto libri né i libri senza autore. A seconda se vogliamo anche questi avremo rispettivamente un LEFT OUTER o un RIGHT OUTER JOIN. Ad esempio:

SELECT surname, given_names, title
  FROM Person
  LEFT JOIN Author on person.ID = Author.personID
  LEFT JOIN Book on Book.ID = Author.publicationID

Come si vede la parola chiave OUTER è opzionale. In questo caso, per le persone che non hanno pubblicato un libro, la colonna title avrà valore NULL.

MySQL non supporta nativamente il FULL OUTER JOIN, che permette di fare un join destro e sinistro simultaneamente, per ottenere, nel nostro caso, oltre agli autori e ai libri anche i libri pubblicati anonimamente e le persone che non hanno scritto libri:

SELECT surname, given_names, title
  FROM Person
  FULL JOIN Author on person.ID = Author.personID
  FULL JOIN Book on Book.ID = Author.publicationID

Cross join

Rappresenta l'operazione sistemistica del prodotto cartesiano. Significa che per ogni riga della prima tabella vengono considerate tante righe quante ne ha la seconda combinandole. Quindi alla fine, date due tabelle rispettivamente di dimensioni N ed M, otterremo un risultato di NxM righe. Si usa questo tipo di join quando si vogliono tutte le combinazioni possibili. In SQL, per fare un cross join basta separare i nomi della tabelle con una virgola:

SELECT Person.surname, Publisher.name
FROM Person, Publisher

I cross join vengono usati anche quando si vogliono fare join molto particolari, non ottenibili con le altre clausole di join, specificando le condizioni nella clausola WHERE.

Per un'interpretazione visiva sistemistica delle operazioni di join, segnaliamo un interessante post sul blog CodingHorror.

Unioni

L'operazione sistemistica di unione si può fare in SQL utilizzando la parola chiave UNION.

Ad esempio, per effettuare qualcosa di simile ad un FULL JOINcon MySQL si può scrivere:

SELECT surname, given_names, title  FROM Person
  LEFT JOIN Author on person.ID = Author.personID
  LEFT JOIN Book on Book.ID = Author.publicationID
UNION
SELECT surname, given_names, title  FROM Person
  RIGHT JOIN Author on person.ID = Author.personID
  RIGHT JOIN Book on Book.ID = Author.publicationID

Nell'uso di UNION bisogna fare in modo che le colonne delle varie SELECT da concatenare abbiano lo stesso numero e siano dello stesso tipo altrimenti si avrà un errore.

Proprio come l'operazione sistemistica di unione, questa ignora le righe duplicate. Quindi, come per DISTINCT, è necessario prestare attenzione nell'utilizzo perché se le righe restituite sono in grande quantità, l'eliminazione dei duplicati può impegnare molto tempo il server. Per evitare la ricerca dei duplicati su deve usare l'istruzione UNION ALL:

SELECT surname, given_names, title  FROM Person
  JOIN Author on person.ID = Author.personID
  JOIN Book on Book.ID = Author.publicationID
UNION ALL
SELECT surname, given_names, title  FROM Person
  JOIN Author on person.ID = Author.personID
  JOIN Article on Article.ID = Author.publicationID