Come ORDER BY e NULL lavorano insieme in SQL

I valori NULL vengono per primi o per ultimi quando si usa la clausola ORDER BY? Sono considerati più alti o più bassi dei valori non NULL? In questo articolo spiegherò come i diversi database relazionali trattano i valori NULL quando ordinano l'output e come modificare il comportamento predefinito della clausola ORDER BY.

Quando gli utenti di LearnSQL fanno pratica con la clausola ORDER BY nel nostro corso SQL Basics, spesso chiedono perché i valori NULL appaiono per primi nell'output e come possono cambiare questo comportamento. Prendendo spunto da queste domande, intendo approfondire l'argomento dell'ordinamento delle righe contenenti valori NULL.

I valori NULL vengono sempre anteposti per impostazione predefinita? È possibile modificare il modo in cui ORDER BY ordina i valori NULL? Come si possono applicare le opzioni NULLS FIRST e NULLS LAST? Scopriamolo!

Come vengono ordinati i valori NULL per impostazione predefinita?

Lo standard SQL non definisce l'ordinamento predefinito dei NULL. Che cosa significa?

Se si applica la clausola ORDER BY a una colonna con NULL, i valori NULL verranno inseriti per primi o per ultimi nell'insieme dei risultati. Il risultato dipende dal tipo di database. Vediamo quindi come i diversi database relazionali ordinano i valori NULL.

PostgreSQL

Per impostazione predefinita, PostgreSQL considera i valori NULL più grandi di qualsiasi valore non NULL. Se si ordina l'output in ordine crescente, aggiungendo la parola chiave ASC o per impostazione predefinita (cioè senza specificare l'ordine), tutti i valori NULL saranno mostrati per ultimi nell'output. Ecco un esempio:

SELECT *
FROM paintings
ORDER BY year;

Se si utilizza la parola chiave DESC in ORDER BY per ordinare i valori in ordine decrescente, si otterranno i valori NULL in cima alla tabella dei risultati.

SELECT *
FROM paintings
ORDER BY DESC year;

Oracle

Oracle tratta i NULL allo stesso modo di PostgreSQL. In particolare, la documentazione di Oracle afferma che: "se l'ordinamento dei null non è specificato, la gestione dei valori null è NULLS LAST se l'ordinamento è ASC, NULLS FIRST se l'ordinamento è DESC". In effetti, Oracle considera i valori NULL più grandi di qualsiasi valore non-NULL.

SQLite

A differenza dei tipi di database precedenti, SQLite considera i valori NULL più piccoli di qualsiasi altro valore. Se si ordina una colonna con valori NULL in ordine crescente, i NULL vengono prima.

SELECT *
FROM paintings
ORDER BY year;

In alternativa, se si aggiunge la parola chiave DESC per ottenere un ordine decrescente, i NULL appariranno per ultimi.

SELECT *
FROM paintings
ORDER BY year DESC;

MySQL

Come SQLite, MySQL considera i valori NULL inferiori a qualsiasi valore non NULL. Se si utilizza questo database, ci si aspetta lo stesso trattamento dei valori NULL illustrato sopra: I NULL appariranno per primi se i valori sono ordinati in ordine crescente e per ultimi se si utilizza l'ordine decrescente.

SQL Server

Anche SQL Server tratta i valori NULL come più piccoli dei valori non NULL. I valori NULL vengono visualizzati per primi quando una colonna è ordinata in ordine crescente e per ultimi quando la colonna è ordinata in ordine decrescente.

Riassumiamo il modo in cui i valori NULL sono ordinati per impostazione predefinita nei diversi database:

Come modificare il comportamento predefinito di ORDER BY

Ora che conosciamo il comportamento predefinito dei vari database nell'ordinamento dei valori NULL, potreste chiedervi se sia possibile cambiarlo.

La risposta varia a seconda del tipo di database utilizzato. Lo standard SQL offre le opzioni NULLS FIRST / NULLS LAST i quali modificano l'ordinamento dei valori NULL quando vengono aggiunti a ORDER BY.

Purtroppo, non tutti i database supportano questo standard. Approfondiamo l'argomento.

PostgreSQL e Oracle

Come ricorderete, PostgreSQL e Oracle trattano i valori NULL come molto grandi e li mettono alla fine di un ordinamento crescente e all'inizio di un ordinamento decrescente. Tuttavia, è possibile modificare facilmente questo comportamento aggiungendo semplicemente NULLS FIRST o NULLS LAST alla clausola ORDER BY.

SELECT *
FROM paintings
ORDER BY year NULLS FIRST;

Sopra abbiamo una tabella ordinata in ordine crescente, con NULL al primo posto. Di seguito, faremo l'operazione inversa: tabella in ordine decrescente con NULL all'ultimo posto:

SELECT *
FROM paintings
ORDER BY year DESC NULLS LAST;

SQLite

A differenza di PostgreSQL e Oracle, SQLite tratta NULL come valori molto piccoli e li mette per primi in un ordinamento crescente e per ultimi in un ordinamento decrescente. A partire dalla versione 3.30.0 di SQLite, questo comportamento può essere facilmente modificato utilizzando l'opzione NULLS FIRST / NULLS LAST.

SELECT *
FROM paintings
ORDER BY year NULLS LAST;

Sopra, la parola chiave NULLS LAST ci dà un ordinamento crescente in cui i valori NULL vengono per ultimi. Invertiamo l'ordine:

SELECT *
FROM paintings
ORDER BY year DESC NULLS FIRST;

Ancora una volta, l'opzione NULLS FIRST mette i valori NULL in cima all'ordinamento decrescente.

MySQL

Analogamente a SQLite, MySQL considera i valori NULL come inferiori a qualsiasi valore non NULL; di conseguenza, per impostazione predefinita, mette questi valori per primi quando l'ordinamento è crescente e per ultimi quando l'ordinamento è decrescente. Tuttavia, MySQL non supporta le opzioni NULLS FIRST / NULLS LAST, il che rende più difficile modificare il comportamento predefinito.

Ciononostante, ci sono alcuni trucchi che possiamo usare per ottenere i valori NULL dove vogliamo:

L'uso dell'operatore meno. (Nota: funziona solamente con numeri o date.) Per ordinare i valori in ordine crescente con NULL per ultimo, si può usare la seguente query:

SELECT *
FROM paintings
ORDER BY -year DESC;

In questo caso, l'operatore meno prima del nome della colonna fa sì che MySQL ordini i valori non NULL in ordine inverso. Se aggiungiamo la parola chiave DESC, torniamo all'ordine crescente dei valori non NULL. I valori NULL non sono influenzati dall'operatore meno, mentre la parola chiave DESC li fa apparire per ultimi nell'ordinamento decrescente. Questo trucco funziona quindi come l'opzione NULL LAST di SQLite.

Per ordinare i valori in ordine decrescente, ma con NULL al primo posto, si può utilizzare la seguente query in MySQL:

SELECT *
FROM paintings
ORDER BY -year;

La query avrà come risultato l'ordinamento della colonna anno in ordine decrescente. In questo caso, i valori NULL appaiono per primi, lo stesso risultato che si ottiene con l'opzione NULLS FIRST in SQLite.

L'uso dell'operatore IS (NOT) NULL. (Funziona con tutti i tipi di dati.) Con questo trucco, possiamo fare affidamento sul fatto che per tutte le espressioni IS NULL viene restituito un 1, in tutti gli altri casi uno 0. Per far sì che NULL appia per ultimo durante l'ordinamento in ordine crescente, si può usare la seguente query:

SELECT *
FROM paintings
ORDER BY year IS NULL, year;

Analogamente, possiamo anche ordinare l'output in ordine decrescente con i valori NULL che appaiono per primi. Questa volta utilizzeremo l'operatore IS NOT NULL:

SELECT *
FROM paintings
ORDER BY year IS NOT NULL, year DESC;

Gli operatori IS NULL e IS NOT NULL possono essere molto utili per modificare il comportamento predefinito di MYSQL nell'ordinamento dei valori NULL.

L'uso della funzione COALESCE. (Funziona con tutti i tipi di dati.) Se non conoscete questa funzione, leggete la nostra guida sulla gestione dei valori NULL con la funzione COALESCE. In pratica, possiamo ordinare i valori NULL per ultimi e i non NULL in ordine crescente, fornendo il valore più alto possibile in sostituzione dei valori NULL:

SELECT *
FROM paintings
ORDER BY COALESCE(year, 2021);

In questo caso, utilizziamo 2021 come valore massimo per la colonna anno. (Per essere sicuri che nessun quadro della nostra tabella provenga dal futuro possiamo usare qualsiasi numero superiore a 2020).

Analogamente, per ordinare prima i valori NULL e in ordine decrescente i valori non NULL, si può usare la seguente query:

SELECT * 
FROM paintings
ORDER BY COALESCE(year, 2021) DESC;

Il risultato di queste due query sarà identico a quello ottenuto usando le opzioni NULLS FIRST / NULLS LAST di SQLite.

SQL Server

Così come MySQL, SQL Server non supporta le opzioni NULLS FIRST / NULLS LAST. Tuttavia, i trucchi con l'operatore meno e la funzione COALESCE funzionano in SQL Server proprio come in MySQL. È possibile utilizzare queste opzioni per modificare il comportamento predefinito di SQL Server nell'ordinamento dei valori NULL.

È ora di fare pratica con l'uso di ORDER BY con valori NULL!

Ora sapete che il comportamento predefinito della clausola ORDER BY nell'ordinamento dei valori NULL varia a seconda del database utilizzato. Nella maggior parte dei casi, è possibile modificare facilmente questo comportamento predefinito. Utilizzate l'opzione NULLS FIRST / NULLS LAST con SQLite, PostgreSQL e Oracle. Per MySQL e SQL, usate gli altri trucchi illustrati.

Per dominare i valori NULL, date un'occhiata ai corsi interattivi di LearnSQL:

• SQL Basics copre i valori NULL e l'ordinamento. Per maggiori informazioni sul contenuto di questo corso, consultate la nostra guida specifica sul corso SQL Basics .
• SQL Practice Set fornisce ancora più pratica sulla clausola ORDER BY.

Buon apprendimento!