Wie ORDER BY und NULL in SQL zusammenarbeiten

Stehen NULL-Werte an erster oder letzter Stelle, wenn Sie ORDER BY verwenden? Werden sie höher oder niedriger eingestuft als Nicht-NULL-Werte? In diesem Artikel erkläre ich, wie verschiedene relationale Datenbanken NULL-Werte bei der Sortierung der Ausgabe behandeln und wie man das Standardverhalten der ORDER BY-Klausel ändern kann.

Wenn LearnSQL-Benutzer die ORDER BY-Klausel in unserem SQL für Anfänger Kurs üben, fragen sie oft, warum NULL-Werte in der Ausgabe zuerst erscheinen und wie sie dieses Verhalten ändern können. Angeregt durch diese Anfragen werde ich das Thema der Sortierung von Zeilen, die NULL-Werte enthalten, etwas näher beleuchten.

Stehen NULL-Werte standardmäßig immer an erster Stelle? Ist es möglich, das Verhalten, wie "ORDER BY" NULL-Werte sortiert, zu ändern? Wie können Sie die Optionen NULLS FIRST und NULLS LAST anwenden? Finden wir es heraus.

Wie werden NULL-Werte standardmäßig sortiert?

Der SQL-Standard definiert keine Standardreihenfolge für NULL-Werte. Was bedeutet das für Sie?

Wenn Sie die ORDER BY Klausel auf eine Spalte mit NULLen anwenden, werden die NULL-Werte entweder an erster oder letzter Stelle in der Ergebnismenge platziert. Die Ausgabe hängt vom Datenbanktyp ab. Schauen wir uns also an, wie verschiedene relationale Datenbanken NULL-Werte sortieren.

PostgreSQL

Standardmäßig betrachtet PostgreSQL NULL-Werte als größer als alle Nicht-NULL-Werte. Wenn Sie Ihre Ausgabe in aufsteigender Reihenfolge sortieren - entweder durch Hinzufügen des ASC-Schlüsselworts oder standardmäßig (d.h. ohne Angabe der Reihenfolge) - werden alle NULL-Werte in der Ausgabe als letztes angezeigt. Hier ist ein Beispiel:

SELECT *
FROM paintings
ORDER BY year;

Wenn Sie das Schlüsselwort DESC in ORDER BY verwenden, um Werte in absteigender Reihenfolge zu sortieren, erhalten Sie NULL-Werte am Anfang der Ergebnistabelle.

SELECT *
FROM paintings
ORDER BY DESC year;

Oracle

Oracle behandelt NULL-Werte auf die gleiche Weise wie PostgreSQL. In der Oracle-Dokumentation heißt es: "Wenn die Null-Reihenfolge nicht angegeben ist, werden die Null-Werte als NULLS LAST behandelt, wenn die Sortierung ASC ist. Und als NULLS FIRST, wenn die Sortierung DESC ist." In der Tat betrachtet Oracle NULL-Werte als größer als alle Nicht-NULL-Werte.

SQLite

Im Gegensatz zu den oben genannten Datenbanktypen betrachtet SQLite NULL-Werte als kleiner als jeden anderen Wert. Wenn Sie eine Spalte mit NULL-Werten in aufsteigender Reihenfolge sortieren, werden die NULL-Werte zuerst angezeigt.

SELECT *
FROM paintings
ORDER BY year;

Fügen Sie alternativ ein DESC-Schlüsselwort hinzu, um eine absteigende Reihenfolge zu erhalten, werden die NULL-Werte zuletzt angezeigt.

SELECT *
FROM paintings
ORDER BY year DESC;

MySQL

Wie SQLite betrachtet MySQL NULL-Werte als niedriger als alle Nicht-NULL-Werte. Wenn Sie diese Datenbank verwenden, erwarten Sie die gleiche Behandlung von NULL-Werten wie oben dargestellt: NULL-Werte erscheinen zuerst, wenn die Werte in aufsteigender Reihenfolge sortiert sind. Und zuletzt, wenn sie absteigend sortiert sind.

SQL-Server

SQL Server behandelt NULL-Werte ebenfalls als kleiner als alle Nicht-NULL-Werte. Sie sehen die NULL-Werte zuerst, wenn eine Spalte in aufsteigender Reihenfolge sortiert ist. Und zuletzt, wenn die Spalte in absteigender Reihenfolge sortiert ist.

Fassen wir zusammen, wie NULLs standardmäßig in verschiedenen Datenbanken sortiert werden:

Wie man das Standardverhalten von ORDER BY ändert

Da Sie nun das Standardverhalten verschiedener Datenbanken bei der Sortierung von NULL-Werten kennen, fragen Sie sich vielleicht, ob es möglich ist, es zu ändern?

Die Antwort hängt von dem von Ihnen verwendeten Datenbanktyp ab. Der SQL-Standard bietet die Optionen NULLS FIRST / NULLS LAST, die die Sortierung von NULL-Werten ändern, wenn man sie zu ORDER BY hinzufügt.

Leider unterstützen nicht alle Datenbanken diesen Standard. Lassen Sie uns das näher betrachten.

PostgreSQL und Oracle

Wie Sie sich erinnern, behandeln PostgreSQL und Oracle NULL-Werte als sehr groß und setzen sie an das Ende einer aufsteigenden Sortierreihenfolge und an den Anfang einer absteigenden Sortierreihenfolge. Sie können dieses Verhalten jedoch leicht ändern, indem Sie einfach NULLS FIRST oder NULLS LAST zur ORDER BY-Klausel hinzufügen.

SELECT *
FROM paintings
ORDER BY year NULLS FIRST;

Oben haben wir eine Tabelle, die in aufsteigender Reihenfolge sortiert ist, wobei die NULLs an erster Stelle stehen. Unten machen wir es nun umgekehrt: Wir sortieren in absteigender Reihenfolge, wobei die NULLs an letzter Stelle stehen:

SELECT *
FROM paintings
ORDER BY year DESC NULLS LAST;

SQLite

Im Gegensatz zu PostgreSQL und Oracle behandelt SQLite NULLs als sehr kleine Werte und setzt sie bei einer aufsteigenden Sortierung an die erste und bei einer absteigenden Sortierung an die letzte Stelle. Ab SQLite Version 3.30.0 kann dieses Verhalten auch einfach mit der Option NULLS FIRST / NULLS LAST geändert werden.

SELECT *
FROM paintings
ORDER BY year NULLS LAST;

Mit dem Schlüsselwort NULLS LAST erhalten wir eine aufsteigende Sortierreihenfolge, bei der die NULL-Werte an letzter Stelle stehen. Kehren wir dies um:

SELECT *
FROM paintings
ORDER BY year DESC NULLS FIRST;

Mit der Option NULLS FIRST werden die NULL-Werte an den Anfang der absteigenden Sortierreihenfolge gestellt.

MySQL

Ähnlich wie SQLite behandelt MySQL NULL-Werte als niedriger als alle Nicht-NULL-Werte. Daher setzt es diese Werte standardmäßig an die erste Stelle, wenn es in aufsteigender Reihenfolge sortiert, und an die letzte, wenn es in absteigender Reihenfolge sortiert. MySQL unterstützt jedoch nicht die Optionen NULLS FIRST / NULLS LAST, was es schwieriger macht, das Standardverhalten zu ändern.

Es gibt jedoch einige Tricks, um NULL-Werte dorthin zu bekommen, wo wir sie haben wollen:

Verwendung des Minus-Operators. (Hinweis: Dies funktioniert nur bei Zahlen oder Datumsangaben.) Um Werte in aufsteigender Reihenfolge zu sortieren, wobei NULL-Werte an letzter Stelle stehen, können wir die folgende Abfrage verwenden:

SELECT *
FROM paintings
ORDER BY -year DESC;

Hier bewirkt der Minus-Operator vor dem Spaltennamen, dass MySQL Nicht-NULL-Werte in umgekehrter Reihenfolge sortiert. Wenn wir das Schlüsselwort DESC hinzufügen, kehren wir zur aufsteigenden Reihenfolge der Nicht-NULL-Werte zurück. NULL-Werte werden vom Minus-Operator nicht beeinflusst, während das DESC-Schlüsselwort dafür sorgt, dass sie bei der Sortierung in absteigender Reihenfolge zuletzt erscheinen. Dieser Trick funktioniert also auf die gleiche Weise wie die Option NULLS LAST in SQLite.

Um Werte in absteigender Reihenfolge zu sortieren, wobei NULLs an erster Stelle stehen, können wir die folgende Abfrage in MySQL verwenden:

SELECT *
FROM paintings
ORDER BY -year;

Die Abfrage führt dazu, dass die Ausgabe nach der Spalte Jahr in absteigender Reihenfolge sortiert wird. Hier erscheinen die NULLs zuerst. Das gleiche Ergebnis, das wir mit der Option NULLS FIRST in SQLite erhalten.

Verwendung des IS (NOT) NULL-Operators. (Funktioniert für alle Datentypen.) Bei diesem Trick können wir uns auf die Tatsache verlassen, dass IS NULL für alle NULL-Ausdrücke eine 1 und ansonsten eine 0 zurückgibt. Um NULLs beim Sortieren in aufsteigender Reihenfolge zuletzt erscheinen zu lassen, können wir die folgende Abfrage verwenden:

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

Dementsprechend können wir die Ausgabe auch in absteigender Reihenfolge sortieren, wobei NULLs zuerst erscheinen. Diesmal verwenden wir IS NOT NULL:

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

Die Operatoren IS NULL und IS NOT NULL können sehr nützlich sein, um das Standardverhalten von MYSQL beim Sortieren von NULL-Werten zu ändern.

Verwenden Sie die Funktion COALESCE. (Funktioniert für alle Datentypen.) Wenn Sie mit dieser Funktion nicht vertraut sind, lesen Sie unsere Anleitung zur Behandlung von NULL-Werten mit der COALESCE-Funktion. Grundsätzlich können wir NULL-Werte zuletzt sortieren, während wir Nicht-NULL-Werte in aufsteigender Reihenfolge sortieren, indem wir den höchstmöglichen Wert als Ersatz für NULL-Werte angeben:

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

Hier verwenden wir 2021 als höchstmöglichen Wert für die Spalte Jahr. (Wir können sicher sein, dass keine Bilder in unserer Tabelle aus der Zukunft stammen. Wir könnten jede Zahl über 2020 verwenden, um dies zu erreichen.)

Um die NULL-Werte zuerst zu sortieren und gleichzeitig die Nicht-NULL-Werte in absteigender Reihenfolge zu ordnen, können wir die folgende Abfrage verwenden:

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

Die Ausgabe der beiden obigen Abfragen ist identisch mit der Verwendung der Optionen NULLS FIRST / NULLS LAST in SQLite.

SQL-Server

Wie MySQL unterstützt auch SQL Server die Optionen NULLS FIRST / NULLS LAST nicht. Allerdings funktionieren die Tricks mit dem Minus-Operator und der COALESCE-Funktion im SQL Server genauso wie in MySQL. Sie können diese Optionen verwenden, um das Standardverhalten von SQL Server beim Sortieren von NULL-Werten zu ändern.

Üben Sie jetzt die Verwendung von ORDER BY mit NULL-Werten!

Sie wissen nun, dass das Standardverhalten der ORDER BY-Klausel bei der Sortierung von NULL-Werten von der verwendeten Datenbank abhängt. In den meisten Fällen können Sie dieses Standardverhalten leicht ändern. Verwenden Sie die Option NULLS FIRST / NULLS LAST bei SQLite, Postgres und Oracle. Für MySQL und SQL verwenden Sie die anderen Tricks, die wir gezeigt haben.

Um sicher mit NULL-Werten umzugehen, schauen Sie sich diese interaktiven LearnSQL-Kurse an:

• SQL für Anfänger behandelt NULL-Werte und die Reihenfolge. Weitere Informationen über den Inhalt dieses Kurses finden Sie in unserem speziellen Leitfaden zum Kurs SQL für Anfänger .
• SQL-Übungssatz bietet noch mehr Übungen für die ORDER BY-Klausel.

Viel Spaß beim Lernen!