Ricerca, filtraggio e ordinamento

Continuiamo con l’istruzione SELECT e stavolta vediamo le clausole WHERE e ORDER BY.

Filtraggio

Già nella lezione precedente, quando abbiamo parlato di inner join, abbiamo visto un particolare tipo di filtraggio, l’intersezione tra insiemi. Ora vediamo invece come, con la clausola WHERE, possiamo filtrare le nostre righe in modo molto preciso sulla base dei valori che assumono i campi.

In generale, la sintassi di questa clausola è un espressione che può essere o vera o falsa per una certa riga del nostro insieme di dati. L’espressione più semplice ovviamente è l’eguaglianza:

SELECT *
FROM Person
WHERE surname = 'Knuth'

In questo caso verranno restituite le persone aventi un certo cognome. Usando l’operatore AND possiamo combinare due espressioni per richiedere di restituire le righe che soddisfano entrambe le condizioni:

SELECT * FROM Person
WHERE surname = 'Knuth' AND given_names = 'Donald'

In questo caso, nel nostro database di esempio, non avremo alcun risultato perché l’operatore di uguaglianza (=) viene soddisfatto solo se i valori sono esattamente uguali, mentre 'Donald E.' è diverso da 'Donald'. Con l’operatore LIKE, utilizzabile solamente con i dati di tipo testuale come VARCHAR, possiamo invece cercare i valori che corrispondono ad un certo pattern. Si possono utilizzare due caratteri jolly:

• il carattere % verrà soddisfatto da qualsiasi sequenza di caratteri trovata;
• il carattere _ verrà soddisfatto da qualsiasi carattere (singolo) trovato.

Stavolta, quindi, questa interrogazione restituirà esattamente il record di Donald E. Knuth:

SELECT * FROM Person
WHERE surname = 'Knuth' AND given_names LIKE 'Donald%'

Se non viene specificato alcun carattere jolly, LIKE si comporta esattamente come un’uguaglianza semplice.

Si noti che MySQL supporta anche la parola chiave REGEXP, che effettua un confronto tramite espressioni regolar, molto più potenti della ricerca tramite LIKE. Oracle supporta la funzione REGEXP_LIKE. Ad esempio, con la seguente interrogazione MySQL si ottengono i libri che nel titolo contengono prima il testo “Object” poi qualsiasi carattere, quindi il testo “Oriented” e infine un testo che può essere o “Databases” o “Concepts”:

SELECT * FROM Book
WHERE title REGEXP 'Object.*Oriented.*(Databases|Concepts)'

In Oracle la sintassi è leggermente diversa:

REGEXP_LIKE(title, 'Object.*Oriented.*(Databases|Concepts)')

Per dettagli sulle espressioni regolari supportate dai database, rimandiamo alle guide ufficiali dei rispettivi database.

Oltre all’operatore AND, le espressioni si possono combinare con l’operatore OR, soddisfatto quando almeno una delle espressioni è vera:

SELECT * FROM Book WHERE pub_year = 1983 OR pub_year = 1993 OR pub_year = 1980

Questo tipo di filtri si può scrivere in maniera più compatta e più comoda con l’operatore IN:

SELECT * FROM Book WHERE pub_year IN (1983, 1993, 1980)

L'operato IN permette di indicare un elenco di possibili valori. Non solo: è possibile specificare anche query secondarie:

SELECT * FROM Book WHERE pub_year IN (SELECT pub_year FROM Article)

Nel combinare espressioni AND e OR, dobbiamo ricordare che AND ha la precedenza nella valutazione. Quindi (come per la maggior parte dei linguaggi) dobbiamo usare le parentesi per modificare l’ordine degli operatori, altrimenti, ad esempio, questa interrogazione potrebbe dare risultati inaspettati:

SELECT * FROM Book
WHERE (pub_year = 1983 OR pub_year = 1993) AND publisher = 1

Infatti, eseguendola senza le parentesi, otterremmo anche i libri pubblicati nel 1983, indipendentemente dall’editore, oltre ai libri pubblicati nel 1993 dall’editore avente ID uguale a 1.

Un altro operatore logico (stavolta unario) importante è il NOT, utilizzato per negare una condizione:

SELECT * FROM Book  WHERE NOT(volume = 1)

Contrariamente a quanto si possa pensare, questa query non restituirà né i volumi diversi dal primo, ma neanche i libri aventi il campo volume impostato a NULL. Questo perché in SQL il valore NULL è speciale nel senso che le espressioni che contengono valori nulli vengono valutate come NULL, quindi richiedono i seguenti operatori speciali:

• IS NULL
• IS NOT NULL

Possiamo quindi riscrivere la query dell’esempio così:

SELECT * FROM Book  WHERE volume IS NULL OR NOT(volume = 1)

Oppure possiamo utilizzare la funzione COALESCE che abbiamo visto nella lezione precedente:

SELECT * FROM Book  WHERE NOT(COALESCE(volume, 0) = 1)

Naturalmente, si può testare anche la disuguaglianza, con gli appositi operatori:

• disuguaglianza semplice: <>

;

• minore < e minore o uguale: <=

;

• maggiore > e maggiore o uguale: >=

.

Nel caso dei valori testuali, come CHAR e VARCHAR, l’ordinamento è quello alfabetico in base alla codifica scelta per il database.

L’operatore BETWEEN è comodo per cercare valori in un intervallo, ad esempio di date, ma può essere usato anche con gli altri tipi. L’intervallo si intende chiuso (con gli estremi); quindi con la seguente interrogazione avremo anche i libri pubblicati nel 1980 e nel 1993:

SELECT title, pub_year
FROM Book
WHERE pub_year BETWEEN 1980 AND 1993

Ordinamento

Per ordinare i risultati di una query si usa la clausola ORDER BY, seguita dalle espressioni da valutare per effettuare l’ordinamento. Ad esempio:

SELECT title, series, pub_year FROM Book
ORDER BY title

Questa query restituisce i libri pubblicati in ordine di titolo; se li volessimo ordinati prima per anno di pubblicazione e poi per titolo, basterebbe modificare la clausola in questo modo:

SELECT title, series, pub_year FROM Book
ORDER BY pub_year DESC, title

Abbiamo semplicemente separato i due campi da una virgola. Con la parola chiave DESC abbiamo specificato che vogliamo un ordinamento discendente, ossia mettendo prima i più recenti. La parola chiave ASC, che indica l’ordinamento ascendente, è opzionale perché corrisponde all’ordinamento di default.

Il comportamento di ORDER BY con i valori nulli dipende dal tipo di database utilizzato. Ad esempio MySQL e SQL Server li mettono sempre in testa, mentre Oracle li mette in coda. Molti database, come Oracle e PostgreSQL hanno le parola chiave NULLS FIRST e NULLS LAST per indicare come trattare i valori nulli; la seguente query, in Oracle, restituisce prima i libri ordinati per collana, mettendo in testa i libri che non appartengono ad una collana:

SELECT title, series FROM Book ORDER BY series NULLS FIRST

Considerato che nella clausola di ordinamento possiamo usare tutte le funzioni che abbiamo già visto (non quelle di aggregazione che vedremo nella prossima lezione), un modo portabile per ottenere lo stesso effetto è di utilizzare la funzione COALESCE. Infatti, anche con MySQL possiamo ottenere lo stesso effetto scrivendo:

SELECT title, series FROM Book ORDER BY COALESCE(series, '0')

In questo modo i valori nulli verranno considerato come una stringa contenente il solo zero.