Comment ORDER BY et NULL fonctionnent ensemble en SQL

Les valeurs NULL, viennent-elles en premier ou en dernier lorsque vous utilisez ORDER BY ? Sont-elles considérées comme supérieures ou inférieures aux valeurs non NULL ? Dans cet article, je vais vous expliquer comment différentes bases de données relationnelles traitent les valeurs NULL lors du tri des résultats et comment modifier le comportement par défaut de la clause ORDER BY.

Lorsque les utilisateurs de LearnSQL s'exercent à l'utilisation de la clause ORDER BY dans notre cours, ils se demandent souvent pourquoi les valeurs NULL ne sont pas utilisées. Lors des cours SQL pour débutants, ils demandent souvent pourquoi les valeurs NULL apparaissent en premier dans le résultat et comment ils peuvent modifier ce comportement. Inspiré par ces questions, je vais plonger en profondeur dans le sujet du classement des lignes qui contiennent des valeurs NULL.

Les valeurs NULL, sont-elles toujours placées en premier par défaut ? Est-il possible de modifier la façon dont la clause ORDER BY trie les valeurs NULL ? Comment appliquer les options NULLS FIRST et NULLS LAST ? C'est ce que nous allons découvrir.

Comment les valeurs NULL sont-elles triées par défaut ?

La norme SQL ne définit pas l'ordre par défaut des valeurs NULL. Qu'est-ce que cela signifie ?

Si vous appliquez la clause ORDER BY à une colonne contenant des NULL, les valeurs NULL seront placées en premier ou en dernier dans le jeu de résultats. Le résultat dépend du type de base de données. Voyons donc comment différentes bases de données relationnelles trient les valeurs NULL.

PostgreSQL

Par défaut, PostgreSQL considère que les valeurs NULL sont plus grandes que toute valeur non NULL. Si vous triez votre sortie dans l'ordre croissant - en ajoutant le mot-clé ASC ou par défaut (c'est-à-dire sans spécifier l'ordre) - toutes les valeurs NULL seront affichées en dernier dans la sortie. Voici un exemple :

SELECT *
FROM paintings
ORDER BY year;

Si vous utilisez le mot-clé DESC dans ORDER BY pour trier les valeurs dans l'ordre décroissant, vous obtiendrez les valeurs NULL en haut de la table des résultats.

SELECT *
FROM paintings
ORDER BY DESC year;

Oracle

Oracle traite les valeurs NULL de la même manière que PostgreSQL. Plus précisément, la documentation d'Oracle indique que "si l'ordre des valeurs NULL n'est pas spécifié, alors le traitement des valeurs NULL est NULLS LAST si le tri est ASC, NULLS FIRST si le tri est DESC". En fait, Oracle considère que les valeurs NULL sont plus grandes que les valeurs non NULL.

SQLite

Contrairement aux types de bases de données ci-dessus, SQLite considère que les valeurs NULL sont plus petites que toute autre valeur. Si vous triez une colonne contenant des valeurs NULL dans l'ordre croissant, les valeurs NULL viendront en premier.

SELECT *
FROM paintings
ORDER BY year;

Par contre, si vous ajoutez le mot-clé DESC pour obtenir un ordre décroissant, les NULL apparaîtront en dernier.

SELECT *
FROM paintings
ORDER BY year DESC;

MySQL

Comme SQLite, MySQL considère les valeurs NULL comme inférieures à toute valeur non NULL. Si vous utilisez cette base de données, attendez-vous au même traitement des valeurs NULL que celui illustré ci-dessus : Les valeurs NULL, apparaîtront en premier si les valeurs sont triées par ordre croissant et en dernier si l'ordre décroissant est utilisé.

Le serveur SQL

Le serveur SQL traite également les valeurs NULL comme étant plus petites que les valeurs non NULL. Vous verrez les NULL en premier lorsqu'une colonne est triée par ordre croissant et en dernier lorsque la colonne est triée par ordre décroissant.

Résumons comment les NULL sont triés par défaut dans différentes bases de données :

Comment modifier le comportement par défaut d'ORDER BY ?

Maintenant que vous connaissez le comportement par défaut des différentes bases de données en matière de tri des valeurs NULL, vous vous demandez peut-être s'il est possible de le modifier.

La réponse varie en fonction du type de base de données que vous utilisez. La norme SQL propose des options NULLS FIRST / NULLS LAST qui modifient le tri des valeurs NULL lorsqu'elles sont ajoutées à ORDER BY.

Malheureusement, toutes les bases de données ne prennent pas en charge cette norme. Penchons-nous sur la question.

PostgreSQL et Oracle

Comme vous vous en souvenez, PostgreSQL et Oracle considèrent les valeurs NULL comme très grandes et les placent à la fin d'un ordre de tri croissant et au début d'un ordre de tri décroissant. Cependant, vous pouvez facilement modifier ce comportement en ajoutant simplement NULLS FIRST ou NULLS LAST à la clause ORDER BY.

SELECT *
FROM paintings
ORDER BY year NULLS FIRST;

Ci-dessus, nous avons une table triée par ordre croissant, mais avec les NULL en premier. Ci-dessous, nous allons faire l'inverse - trier par ordre décroissant avec les NULLs en dernier :

SELECT *
FROM paintings
ORDER BY year DESC NULLS LAST;

SQLite

Contrairement à PostgreSQL et Oracle, SQLite traite les NULL comme de très petites valeurs et les place en premier dans un tri croissant et en dernier dans un tri décroissant. À partir de la version 3.30.0 de SQLite, ce comportement peut être facilement modifié en utilisant l'option NULLS FIRST / NULLS LAST.

SELECT *
FROM paintings
ORDER BY year NULLS LAST;

Ci-dessus, le mot-clé NULLS LAST nous donne un ordre de tri ascendant avec les valeurs NULL en dernier. Inversons cela :

SELECT *
FROM paintings
ORDER BY year DESC NULLS FIRST;

Une fois encore, l'option NULLS FIRST place les valeurs NULL en tête de l'ordre de tri décroissant.

MySQL

Comme SQLite, MySQL traite les valeurs NULL comme inférieures à toute valeur non NULL ; ainsi, par défaut, il place ces valeurs en premier lors du tri par ordre croissant et en dernier lors du tri par ordre décroissant. Cependant, MySQL ne prend pas en charge les options NULLS FIRST / NULLS LAST, ce qui rend plus difficile la modification du comportement par défaut.

Cependant, il existe quelques astuces permettant d'obtenir les valeurs NULL là où nous le souhaitons :

L'utilisation de l'opérateur moins. (Remarque : cela ne fonctionne que pour les nombres ou les dates.) Pour trier les valeurs par ordre croissant, les valeurs NULL arrivant en dernier, nous pouvons utiliser la requête suivante :

SELECT *
FROM paintings
ORDER BY -year DESC;

Ici, l'opérateur moins devant le nom de la colonne permet à MySQL de trier les valeurs non NULL dans l'ordre inverse. Si on ajoute le mot-clé DESC, on revient à l'ordre croissant des valeurs non NULL. Les valeurs NULL ne sont pas affectées par l'opérateur moins, tandis que le mot-clé DESC les fait apparaître en dernier lors du tri dans l'ordre décroissant. Ainsi, cette astuce fonctionne de la même manière que l'option NULLS LAST dans SQLite.

Pour trier les valeurs dans l'ordre décroissant mais avec les NULL en premier, nous pouvons utiliser la requête suivante dans MySQL :

SELECT *
FROM paintings
ORDER BY -year;

La requête fera en sorte que la sortie soit ordonnée par la colonne année dans l'ordre décroissant. Ici, les NULLs apparaissent en premier - le même résultat que nous obtenons avec l'option NULLS FIRST dans SQLite.

Utilisation de l'opérateur IS (NOT) NULL. (Fonctionne pour tous les types de données.) Dans cette astuce, nous pouvons compter sur le fait que IS NULL renvoie un 1 pour toutes les expressions NULL et un 0 sinon. Pour que les NULL apparaissent en dernier lors d'un tri par ordre croissant, nous pouvons utiliser la requête suivante :

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

De la même manière, nous pouvons également trier le résultat dans l'ordre décroissant, les NULL apparaissant en premier. Cette fois, nous utiliserons IS NOT NULL:

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

Les opérateurs IS NULL et IS NOT NULL peuvent être très pratiques pour modifier le comportement par défaut de MYSQL pour le tri des valeurs NULL.

En utilisant la fonction COALESCE. (Fonctionne pour tous les types de données.) Si vous n'êtes pas familier avec cette fonction, lisez notre guide sur la gestion des valeurs NULL avec la fonction COALESCE. Fondamentalement, nous pouvons trier les valeurs NULL en dernier tout en triant les valeurs non NULL dans l'ordre croissant en fournissant la valeur la plus élevée possible comme substitut aux valeurs NULL:

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

Ici, nous utilisons 2021 comme valeur la plus élevée possible pour la colonne année. (Nous voulons nous assurer qu'aucun résultat ne provient du futur. Nous pourrions utiliser n'importe quel nombre supérieur à 2020 pour y parvenir).

De même, pour trier les NULL en premier tout en classant les valeurs non NULL par ordre décroissant, nous pouvons utiliser la requête suivante :

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

Le résultat des deux requêtes ci-dessus sera identique à celui obtenu en utilisant les options NULLS FIRST / NULLS LAST dans SQLite.

Le serveur SQL

Comme MySQL, le serveur SQL ne prend pas en charge les options NULLS FIRST / NULLS LAST. Cependant, les astuces avec l'opérateur moins et la fonction COALESCE fonctionnent dans SQL Server de la même manière que dans MySQL. Vous pouvez utiliser ces options pour modifier le comportement par défaut du serveur SQL lors du tri des valeurs NULL.

Il est temps de s'entraîner à utiliser ORDER BY avec des valeurs NULL !

Vous savez maintenant que le comportement par défaut de la clause ORDER BY lors du tri des valeurs NULL varie selon la base de données que vous utilisez. Dans la plupart des cas, vous pouvez facilement modifier ce comportement par défaut. Utilisez l'option NULLS FIRST / NULLS LAST avec SQLite, Postgres et Oracle. Pour MySQL et SQL, utilisez les autres astuces que nous avons présentées.

Pour manipuler en toute confiance, les valeurs NULL, consultez ces cours interactifs LearnSQL :

• SQL pour les débutants couvre les valeurs NULL et l'ordonnancement. Obtenez plus d'informations sur le contenu de ce cours dans notre guide spécifique sur le cours SQL pour les débutants.
• La pratique du SQL Set fournit encore plus d'exemples pratiques sur la clause ORDER BY.

Bon apprentissage !