Order by hat einen langen Weg hinter sich

1. Eingeschränkte Unterstützung in union, except und intersect
2. Ausdrücke dürfen keine Select-Namen enthalten

Die Order By-Klausel ist mit Sicherheit einer der bekanntesten SQL-Klauseln. Dennoch gibt es einige weitverbreitete Missverständnisse, die es wert sind, aufgeklärt zu werden. Dafür erzählt dieser Artikel die Evolution der Order By-Klausel in ISO/IEC 9075-2:2023– das ist: der SQL-Standard.

Inhalt:

1. Die 1980er
2. Drei Reihenfolgen: Syntax, Logik, Tatsächlich
3. Spaltennummern
4. Nicht-selektierte Spalten
5. Ausdrücke
6. Null-Sortierung
7. In Unterabfragen

Die 1980er

Am Anfang,0 hatte die Order By-Klausel einige Einschränkungen, die später aufgehoben wurden: (1) sie konnte nur Spalten benutzen, die von der zugehörigen Select-Klausel erzeugt wurden; (2) sie akzeptierte keine Ausdrücke; (3) sie hatte keine direkte Funktion, um die Sortierung von Null-Werte festzulegen und (4) sie war nur ganz am Ende einer Abfrage als letzte Klausel erlaubt.

Die folgende Abfrage war zum Beispiel bis ISO/IEC 9075-2:1999 ungültig, weil die Order By-Klausel eine nicht-selektierte Spalte benennt.

SELECT a
  FROM t
 ORDER BY b

Da kann man sich schon fragen, warum der Standard diesen Zugriff auf nicht-selektierte Spalten nicht erlaubt hat. Diese Frage geht allerdings von einem völlig falschen Blickwinkel aus. Die Erklärung bedarf eines Exkurses.

Drei Reihenfolgen: Syntax, Logik, Tatsächlich

Aktuell schwirren unzählige AI-generierte Infografiken durch die sozialen Netzwerke. Viele davon thematisieren den Unterschied zwischen der syntaktischen Reihenfolge der SQL-Klauseln und der logischen Reihenfolge der Auswertung. Die syntaktische Reihenfolge kann man sofort in der Abfrage sehen. Im oberen Beispiel ist sie: select, from, order by. Die logische Reihenfolge der Auswertung ist nicht in der Abfrage ersichtlich. Obwohl sie identisch mit der syntaktischen Reihenfolge ist, gibt es Ausnahmen wie die Select-Klausel.

Für die Abfrage von oben ist die logische Reihenfolge der Auswertung From, Select, Order By. Das Ergebnis kommt also so zustande, dass zuerst die From-Klausel eine imaginäre Ergebnistabelle erzeugt, die aus dem Inhalt der Tabelle „t“ besteht. Dazu zählt auch die Spalte „b“, wenn es sie gibt. Diese imaginäre Tabelle wird dann an die Select-Klausel übergeben, welche wiederum eine imaginäre Ergebnistabelle erzeugt. Diese hat jedoch nur noch die Spalte „a“. Sie wird an die Order By-Klausel weitergegeben, die daher keine Spalte „b“ finden kann. Daher war diese Abfrage ungültig. Nicht, weil es im Standard einen extra Satz gegeben hätte, der diesen Fall für ungültig erklärt, sondern als unglückliche Konsequenz davon, wie der Standard imaginäre Tabellen von Klausel zu Klausel reicht.

Damit haben wir zwei verschiedene SQL-Reihenfolgen gesehen: die syntaktische und die logische Reihenfolge der Auswertung. Beide sind im Standard festgelegt. Was die aktuellen Infografiken jedoch nicht erwähnen, ist, dass es noch eine dritte gibt: die tatsächliche Reihenfolge der Ausführung. Auch wenn es auf den ersten Blick so scheint, als würde der Standard vorschreiben, wie eine Anweisung abzuarbeiten ist, tut er das eben nicht! Der Standard sagt das auch ganz explizit:

Eine konforme SQL-Implementierung muss nicht die genaue Reihenfolge der Aktionen befolgen, die in den Generellen Regeln definiert sind, solange der Effekt [...] jenem dieser Reihenfolge gleich ist.1

— ISO/IEC 9075-1:2023 §6.3.3.3

In anderen Worten, bedeutet alles im Standard eigentlich folgendes: „Das Ergebnis muss so sein, als ob man diese Schritte befolgt hätte“.

Das führt mich zu einem anderen Typ von Infografik, bei dem das „frühe Filtern“-Mantra gepredigt wird, um die Performance zu verbessern. Dieses Mantra stimmt zwar, wenn man es auf die tatsächliche Ausführungsreihenfolge bezieht. Es ist jedoch sinnlos, es auf die syntaktische oder logische Reihenfolge anzuwenden. Nehmen wir dazu das folgendes Beispiel:

SELECT *
  FROM t
 WHERE a = 42

Der SQL-Standard legt fest, dass das Ergebnis dieser Abfrage gleich dem Ergebnis sein muss, das entstanden wäre, wenn man die folgenden Schritte ausgeführt hätte: Zuerst wird der gesamte Inhalt der Tabelle „t“ beschafft, anschließend wird dieser Inhalt an die Where-Klausel übergeben, welche nur die passenden Zeilen in ihr Ergebnis übernimmt. Nur diese Zeilen werden dann an die Select-Klausel übergibt, welche das Endergebnis der Abfrage produziert. Würde der Standard diese Reihenfolge tatsächlich vorschreiben, wäre Indizierung generell unmöglich.

Tatsächlich verwenden die Systeme in diesem Fall sehr gerne einen Index auf der Spalte „a“, wenn ein solcher verfügbar ist. Damit geht die Where-Klausel de-facto in der From-Klausel auf. Wenn es nur irgendwie, ohne das Ergebnis zu verfälschen, möglich ist, frühzeitig zu filtern, machen SQL-Systeme das auch. Diese Optimierung ist als „Push-Down“ bekannt. Der Tipp, eine Unterabfrage oder eine CTE zu nutzen, um einen Filter früher anzuwenden, zum Beispiel vor einem Join, verschwendet deine Zeit. Das macht das System ohnehin. Automatisch. Seit Jahrzehnten.2 Vertraue dem Ausführungsplan, nicht Infografiken. Im Ausführungsplan sieht man, was das System tatsächlich macht.

Spaltennummern

Nach diesem Exkurs geht’s zurück zur Order By-Klausel. Damals, bis ISO/IEC 9075:1992, gab es noch einen anderen Weg, festzulegen, welche Spalte zur Sortierung verwendet werden soll: Mittels einer Spaltennummer. Das Literal „1“ im folgenden Beispiel bedeutet, dass die erste Spalte zur Sortierung herangezogen werden soll.

SELECT a
  FROM t
 ORDER BY 1

Beachte, dass diese Möglichkeit im Standard von 1999 entfernt wurde.3 Dennoch wird sie weitgehend unterstützt – und auch häufig genutzt.

1. Auch als Bind-Parameter: order by ? • Negative Parameter (nicht aber Literale) kehren die Reihenfolge um
2. Auch als Bind-Parameter: order by ?

Nicht-selektierte Spalten

Bisher haben wir die SQL-Standard-Funktionen der Order By-Klausel im vergangenen Jahrhundert gesehen. Dann kam der „Urknall“ des modernen SQLs: Im Dezember 1999 wurde eine neue Ausgabe des Standards veröffentlicht, die nahezu alles veränderte. Vom Boolean-Typ bis zu benutzerdefinierten Typen, von CTEs bis zu grouping sets. Unzählige neue Funktionen haben SQL über seinen relationalen Ursprung wachsen lassen. Bei SQL:1999 wurde aber auch nicht davor zurückgeschreckt, alte und einfache Funktionen wie die Order By-Klausel zu überarbeiten.

Insbesondere erlaubt SQL:1999 die Verwendung von nicht-selektierte Spalten in der Order By-Klausel. Wie wir oben gesehen haben, hat die Order By-Klausel jedoch keinen Zugriff auf diese Spalten. Die Frage ist also: Wie hat man das mit SQL:1999 ermöglicht?

Wenn die Order By-Klausel eine Spalte nennt, die nicht im imaginären Ergebnis der Select-Klausel ist, fügt der Standard sie einfach der Select-Klausel hinzu.4 Der Text im Standard schreibt die Abfrage für uns einfach um. Diese Technik ist im Standard als syntaktische Transformation bekannt. Solche Transformationen nutzt der Standard sehr oft, meist jedoch nur innerhalb einer syntaktischen Einheit. Diese spezielle Transformation ändert ohne zu zögern eine andere Klausel.5

Nachdem die nicht-selektierten Spalten in die Select-Klausel aufgenommen wurden, kann die Order By-Klausel darauf zugreifen. Damit diese Spalten nicht im Endergebnis aufscheinen, entfernt die Order By-Klausel sie wieder aus ihrem Ergebnis.6 Die Trennung der Zuständigkeiten zwischen den Klauseln wird hier rücksichtslos gebrochen. Hier heiligt der Zweck aber die Mittel, würde ich sagen. Und denke immer daran: Die Systeme dürfen noch immer machen was sie wollen, solange sie dasselbe Ergebnis liefern wie das im Standard festgeschriebene Vorgehen.

Beachte auch die Vorrangregeln dieser Transformation: Sie wird nur für Spalten angewendet, die nicht in der ursprünglichen Select-Klausel vorkommen. Als Beispiel:

CREATE TABLE t (
  a INTEGER,
  b INTEGER
)

SELECT b AS a
  FROM t
 ORDER BY a -- verweist auf t.b

Der Spaltenname „a“ in der Order By-Klausel bezieht sich eindeutig auf die Spalte „a“, die von der Select-Klausel erzeugt wird. Nur wenn keine passende Spalte in der Select-Klausel vorhanden ist, werden die Spalten aus der From- oder Join-Klausel in Erwägung gezogen. Dadurch ist sichergestellt, dass sich die Bedeutung dieser Abfrage von SQL-92 auf SQL:1999 nicht ändert.

Ausdrücke

Eine weitere, nicht weniger ärgerliche Einschränkung der frühen Order By-Klausel wurde ebenfalls 1999 behoben: Die Order By-Schlüssel waren nicht länger auf Spaltennamen und Spaltennummern beschränkt.

Seither kann man in der Order By-Klausel beliebige Ausdrücke (Formeln)7 verwenden, die zumindest einen Spaltennamen enthalten. Order By 1, wie oben gezeigt, ist damit ausdrücklich nicht mehr Standard-SQL.8 27 Jahre später, im wissen, dass Spaltennummern noch immer akzeptiert werden, könnte man natürlich darüber nachdenken, das wieder in den Standard aufzunehmen.

Ein relevanteres Problem ist jedoch, dass es noch immer Systeme gibt, die nicht alle Ausdrücke in Order By-Klauseln erlauben. Diese Systeme erlauben in Ausdrücken nur Spaltennamen, die aus der From- oder Join-Klausel kommen. Die nächsten beiden Beispiele verdeutlichen das. Das Erste wird von allen getesteten Systemen akzeptiert, während das Zweite in manchen Systemen einen Fehler liefert.

SELECT a + b
  FROM t
 ORDER BY COALESCE(a + b, 0)

SELECT a + b AS x
  FROM t
 ORDER BY COALESCE(x, 0)

Diese Einschränkung wird in den folgenden Dokumentationen genannt: PostgreSQL, SQL Server.

Null-Sortierung

Die nächste nennenswerte Erweiterung der Order By-Klausel kam in 2003: Die Nulls First|Last-Spezifikation.

SELECT a
  FROM t
 ORDER BY b NULLS FIRST

Ohne diese Angabe ist die Sortierung von Null-Werten relativ zu Nicht-Null-Werten umsetzungsdefiniert (ID133). Die einzige Anforderung des Standards ist dann, dass das System alle Null-Werte gleich behandelt und entweder vor oder nach den Nicht-Null-Werten einsortieren muss. Dabei darf die Entscheidung, ob sie davor oder danach einsortiert werden, von der Asc- oder Desc-Angabe abhängen, sodass letztlich eine Behandlung als kleinst (≪)- oder größtmöglicher (≫) Wert entsteht. Tatsächlich behandeln die meisten Systeme den Null-Wert entweder als besonders klein oder als besonders groß.

1. Einstellbar: set default_null_order = [NULLS_FIRST | NULLS_LAST | NULLS_FIRST_ON_ASC_LAST_ON_DESC | NULLS_LAST_ON_ASC_FIRST_ON_DESC]

In Unterabfragen

Wie ich in der Aufzählung ganz Oben erwähnt habe, war die Order By-Klausel ursprünglich nicht in Unterabfragen erlaubt. Das liegt, einmal mehr, an der Geschichte von SQL: Da „SQL auf dem relationalen Modell basiert, aber keine strikte Umsetzung davon ist“,9 erbt es viele Ideen aus dem relationalen Modell. Eine davon ist, dass Tabellen letztendlich Mengen, genauer Multi-Mengen, sind. Der wesentliche Punkt ist, dass die Eigenschaft von (Multi-)Mengen, keine Reihenfolge unter den Mitglieder zu kennen, auch für SQL-Tabellen gilt. Das betrifft sowohl für Basistabellen (create table) als die imaginären Tabellen die von Klausel zu Klausel wandern. Folgerichtig gab es keinen Grund einer Unterabfrage eine Order By-Klausel hinzuzufügen.

Bis zur Veröffentlichung von ISO/IEC 9075-2:2008. Mit dieser Ausgabe wurde die Fetch First-Klausel eingeführt. Falls du sie nicht kennst: Fetch First ist eine leistungsfähigere Variante der Limit oder Top-Klauseln. Diese Klauseln werden meist, aber nicht unbedingt immer, nach einer Order By-Klausel angewendet, damit bestimmte Zeilen – z. B. die aktuellsten – behalten werden.

Da die Fetch First-Klausel auch in Unterabfragen sinnvoll eingesetzt werden kann, insbesondere in Verbindung mit Lateral-Joins, wurde es notwendig, auch die Order By-Klausel in Unterabfragen zuzulassen. Daher wurde die syntaktische Position der Order By-Klausel von der <cursor specification>, welche nicht verschachtelt werden kann, in die <query expression>, die verschachtelt werden kann, verschoben.

Seither ist Order By in Unterabfragen erlaubt, damit Offset und Fetch First auch dort sinnvoll genutzt werden können. Ähnlich, aber technisch anders, wurde Order By auch in einzelnen Zweigen von Union, Intersect und Except erlaubt, wenn man den jeweiligen Zweig klammert (mehr dazu in F855, „Nested ORDER BY in query expression“).

1. Einige Varianten
2. Wird weg optimiert, wenn bedeutungslos
3. Einige Varianten • Nur in Kombination mit fetch first oder ähnlichen Klauseln
4. Einige Varianten

Der Standard erlaubt Order By in Unterabfragen auch ohne Offset oder Fetch First-Klausel. Eines der getesteten Systeme nimmt sich die (nachvollziehbare) Freiheit, solche Abfragen mit einer Fehlermeldung abzulehnen. Ein anderes System ignoriert solche bedeutungslosen Order By-Klauseln einfach, womit es zwar das Prinzip der geringsten Verblüffung verletzt, sich aber standard-konform verhält. Die anderen Systeme scheinen die Reihenfolge der Order By-Klausel einer Unterabfrage auch in der äußeren Abfrage zu erhalten.10 Das wird vom Standard zwar nicht verlangt, vermeidet aber verblüffendes Verhalten.

Um diese nicht-so-kurze Geschichte der Order By-Klausel abzuschließen möchte ich noch ein paar Beispiele für andere Positionen der Order By-Klausel nennen: over(order by), match_recognize(order by), within group (order by), json_arrayagg(order by).

Natürlich werden diese Themen auch in meinen Schulungen behandelt.

Siehe Auch

• Artikel

  • Wir brauchen Tool-Unterstützung zum Blättern mit Keysets

    Die Offset-Klausel sollte nicht für Blätterabfragen genutzt werden, weil sie zu falschen Ergebnissen und schlechter Performance führt. Falsch und langsam – was braucht man mehr?

  • Select-Namen in Group By

    Manche Systeme erlauben die Nutzung von Select-Namen in der Group By-Klausel. Das ist nicht Standard-SQL. Diese Funktion ist manchmal auch derart schlecht umgesetzt, dass sie die Standardkonformität dieser Systeme bricht.

• Standard-Funktionen

  • Pflichtfunktionen

    • E051, Basic query specification

      • E051-04, „GROUP BY can contain columns not in <select list>“

    • E121-01, „DECLARE CURSOR“

      • E121-02, „ORDER BY columns need not be in select list“

      • E121-03, „Value expressions in ORDER BY clause“

  • Optionale Funktionen

    • F850, „Top-level <order by clause> in <query expression>“

    • F851, „ORDER BY in subqueries“

    • F855, „Nested ORDER BY in query expression“

    • T611, „Elementary OLAP operations“

      • order by … nulls (first|last)

20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Bluesky oder RSS, um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.

Über den Autor

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»