Ein Leitfaden für Anfänger über die wahre Reihenfolge der SQL-Operationen

Die SQL-Sprache ist sehr intuitiv. Bis sie es nicht mehr ist.

Im Laufe der Jahre haben viele Leute die SQL-Sprache aus einer Vielzahl von Gründen kritisiert. Zum Beispiel: IDEs können nicht ohne weiteres erraten, welche Autovervollständigungsoptionen sie anbieten sollen, denn solange man die FROM-Klausel nicht angibt, gibt es (noch) keine Tabellen im Geltungsbereich:

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Diese Dinge sind seltsam, weil die lexikalische Reihenfolge der Operationen nicht mit der logischen Reihenfolge der Operationen übereinstimmt. Wir Menschen können diesen Unterschied in der Reihenfolge manchmal (oft) intuitiv verstehen. Wir wissen z.B., dass wir aus der Kundentabelle auswählen wollen. Aber die IDE weiß das nicht.

GROUP BY stiftet die meiste Verwirrung

Wenn ein Nachwuchsentwickler / SQL-Anfänger anfängt, mit SQL zu arbeiten, wird er ziemlich schnell etwas über Aggregation und GROUP BY herausfinden. Und sie werden schnell Dinge schreiben wie:

SELECT count(*)FROM customer

Ja, wir haben 200 Kunden!

Und dann:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Wow, 90 von ihnen heißen Steve! Interessant! Lass uns herausfinden, wie viele wir pro Name haben…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Sehr schön. Aber sind sie alle gleich? Schauen wir uns auch den Nachnamen an

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jeez, was bedeutet das? (Anmerkung: Leider erhalten MySQL-Benutzer, die nicht den STRICT-Modus verwenden, hier immer noch ein Ergebnis mit willkürlichen Nachnamen, so dass ein neuer MySQL-Benutzer ihren Fehler nicht verstehen wird)

Wie kann man das einem SQL-Neuling leicht erklären? Für „Profis“ scheint es offensichtlich zu sein, aber ist es wirklich offensichtlich? Ist es offensichtlich genug, dass Sie es einem Anfänger leicht erklären können? Denken Sie darüber nach. Warum ist jede dieser Aussagen semantisch richtig oder falsch?

Das Problem ist syntaktisch bedingt

Die SQL-Syntax funktioniert ähnlich wie die englische Sprache. Sie ist ein Befehl. Wir beginnen Befehle mit Verben. Das Verb ist SELECT (oder INSERT, UPDATE, DELETE, CREATE, DROP, usw. usw.)

Unglücklicherweise ist die menschliche Sprache für die viel formalere Welt der Programmierung unglaublich schlecht geeignet. Das ist zwar ein gewisser Trost für neue Benutzer (möglicherweise Nicht-Programmierer), die absolute Anfänger sind, aber für alle anderen macht es die Sache nur schwer. All die verschiedenen SQL-Klauseln haben extrem komplexe Abhängigkeiten untereinander. Zum Beispiel:

  • Bei Vorhandensein einer GROUP BY-Klausel können nur Ausdrücke, die aus GROUP BY-Ausdrücken (oder funktionalen Abhängigkeiten davon) aufgebaut sind, oder Aggregatfunktionen in HAVING-, SELECT– und ORDER BY-Klauseln verwendet werden.
  • Der Einfachheit halber reden wir nicht einmal über GROUPING SETS
  • Es gibt sogar ein paar Fälle, in denen GROUP BY impliziert ist. Z.B.. wenn Sie eine „nackte“ HAVING-Klausel schreiben
  • Eine einzelne Aggregatfunktion in der SELECT-Klausel (in Abwesenheit von GROUP BY) erzwingt die Aggregation in einer einzigen Zeile
  • In der Tat kann dies auch impliziert werden, indem man diese Aggregatfunktion in ORDER BY einfügt (aus welchem Grund auch immer)
  • Sie können ORDER BYeine ganze Reihe von Ausdrücken verwenden, die auf beliebige Spalten der FROM-Klausel verweisen, ohne sie SELECTzu verknüpfen. Aber das gilt nicht mehr, wenn man SELECT DISTINCT

schreibt Die Liste ist endlos. Wenn Sie daran interessiert sind, können Sie die SQL-Standarddokumente lesen und herausfinden, wie viele seltsame und komplizierte Abhängigkeiten es zwischen den vielen Klauseln der SELECT-Anweisung gibt.

Kann man das überhaupt verstehen?

Glücklicherweise, ja! Es gibt einen einfachen Trick, den ich den Teilnehmern meiner SQL Masterclass immer wieder erkläre. Die lexikalische (syntaktische) Reihenfolge der SQL-Operationen (Klauseln) entspricht keineswegs der logischen Reihenfolge der Operationen (auch wenn sie es manchmal zufällig tun). Dank der modernen Optimierer entspricht die Reihenfolge auch nicht der tatsächlichen Reihenfolge der Operationen, so dass wir in Wirklichkeit haben: syntaktische -> logische -> tatsächliche Reihenfolge, aber lassen wir das erst einmal beiseite.

Die logische Reihenfolge der Operationen ist die folgende (der „Einfachheit halber“ lasse ich anbieterspezifische Dinge wie CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT und all die anderen weg):

  • FROM: Dies ist tatsächlich das erste, was logisch passiert. Vor allem laden wir alle Zeilen aus allen Tabellen und fügen sie zusammen. Bevor Sie aufschreien und wütend werden: Auch dies ist das, was logisch zuerst passiert, nicht tatsächlich. Der Optimierer wird höchstwahrscheinlich nicht zuerst diesen Vorgang durchführen, das wäre dumm, sondern auf einen Index zugreifen, der auf der WHERE-Klausel basiert. Aber auch dies geschieht logischerweise zuerst. Außerdem: Alle JOIN-Klauseln sind eigentlich Teil dieser FROM-Klausel. JOIN ist ein Operator in der relationalen Algebra. So wie + und - Operatoren in der Arithmetik sind. Es handelt sich nicht um eine unabhängige Klausel wie SELECT oder FROM
  • WHERE: Nachdem wir alle Zeilen aus den obigen Tabellen geladen haben, können wir sie nun wieder wegwerfen, indem wir WHERE
  • GROUP BY verwenden: Wenn Sie möchten, können Sie die Zeilen, die nach WHERE übrig bleiben, in Gruppen oder Eimern zusammenfassen, wobei jede Gruppe denselben Wert für den Ausdruck GROUP BY enthält (und alle anderen Zeilen in eine Liste für diese Gruppe aufgenommen werden). In Java würde man etwas erhalten wie: Map<String, List<Row>>. Wenn Sie eine GROUP BY-Klausel angeben, enthalten Ihre tatsächlichen Zeilen nur noch die Gruppenspalten, nicht mehr die übrigen Spalten, die sich jetzt in der Liste befinden. Diese Spalten in der Liste sind nur für Aggregatfunktionen sichtbar, die mit dieser Liste arbeiten können. Siehe unten.
  • Aggregationen: Dies ist wichtig zu verstehen. Unabhängig davon, wo Sie Ihre Aggregatfunktion syntaktisch platzieren (d. h. in der SELECT-Klausel oder in der ORDER BY-Klausel), ist dies der Schritt, in dem die Aggregatfunktionen berechnet werden. Direkt nach GROUP BY. (Zur Erinnerung: logisch. Clevere Datenbanken haben sie vielleicht sogar schon vorher berechnet). Das erklärt, warum Sie eine Aggregatfunktion nicht in die WHERE-Klausel setzen können, weil auf ihren Wert noch nicht zugegriffen werden kann. Die WHERE-Klausel steht logischerweise vor dem Aggregationsschritt. Aggregatfunktionen können auf Spalten zugreifen, die Sie oben in „diese Liste“ für jede Gruppe angegeben haben. Nach der Aggregation wird „diese Liste“ verschwinden und nicht mehr verfügbar sein. Wenn Sie keine GROUP BY-Klausel haben, wird es nur eine große Gruppe ohne Schlüssel geben, die alle Zeilen enthält.
  • HAVING: … aber jetzt können Sie auf die Werte der Aggregationsfunktionen zugreifen. Sie können zum Beispiel prüfen, ob count(*) > 1 in der HAVING-Klausel steht. Da HAVING nach GROUP BY steht (oder GROUP BY impliziert), können wir nicht mehr auf Spalten oder Ausdrücke zugreifen, die keine GROUP BY-Spalten waren.
  • WINDOW: Wenn Sie die fantastische Fensterfunktion verwenden, ist dies der Schritt, in dem sie alle berechnet werden. Nur jetzt. Und das Tolle daran ist, dass wir alle Aggregatfunktionen bereits (logisch!) berechnet haben und daher Aggregatfunktionen in Fensterfunktionen verschachteln können. Es ist also völlig in Ordnung, Dinge wie sum(count(*)) OVER () oder row_number() OVER (ORDER BY count(*)) zu schreiben. Dass Fensterfunktionen erst jetzt logisch berechnet werden, erklärt auch, warum man sie nur in die Klauseln SELECT oder ORDER BY einfügen kann. In der WHERE-Klausel sind sie nicht verfügbar, was vorher der Fall war. Beachten Sie, dass PostgreSQL und Sybase SQL Anywhere eine echte WINDOW-Klausel haben!
  • SELECT: Endlich! Wir können nun alle Zeilen verwenden, die aus den obigen Klauseln erzeugt werden, und mit SELECT neue Zeilen/Tupel aus ihnen erstellen. Wir können auf alle Fensterfunktionen zugreifen, die wir berechnet haben, auf alle Aggregatfunktionen, die wir berechnet haben, auf alle Gruppierungsspalten, die wir angegeben haben, oder wenn wir nicht gruppiert/aggregiert haben, können wir alle Spalten aus unserer FROM-Klausel verwenden. Denken Sie daran: Auch wenn es so aussieht, als würden wir innerhalb von SELECT etwas aggregieren, ist dies längst geschehen, und die süße count(*)-Funktion ist nichts weiter als ein Verweis auf das Ergebnis.
  • DISTINCT: Ja! DISTINCT kommt nach SELECT, auch wenn es syntaktisch vor deiner SELECT-Spaltenliste steht. Aber denken Sie darüber nach. Es macht absolut Sinn. Wie können wir sonst eindeutige Zeilen entfernen, wenn wir noch nicht alle Zeilen (und ihre Spalten) kennen?
  • UNION, INTERSECT, EXCEPT: Das ist ein Kinderspiel. Ein UNION ist ein Operator, der zwei Unterabfragen miteinander verbindet. Alles, worüber wir bis jetzt gesprochen haben, war eine Unterabfrage. Die Ausgabe einer Union ist eine neue Abfrage, die dieselben Zeilentypen (d. h. dieselben Spalten) enthält wie die erste Unterabfrage. Normalerweise. Denn im verrückten Oracle ist die vorletzte Subquery die richtige, um den Spaltennamen zu definieren. Oracle-Datenbank, der syntaktische Troll 😉
  • ORDER BY: Es macht durchaus Sinn, die Entscheidung über die Reihenfolge eines Ergebnisses bis zum Ende aufzuschieben, denn alle anderen Operationen könnten intern Hashmaps verwenden, so dass jede Zwischenordnung wieder verloren gehen könnte. Wir können also jetzt das Ergebnis ordnen. Normalerweise können Sie mit der ORDER BY-Klausel auf viele Zeilen zugreifen, auch auf Zeilen (oder Ausdrücke), die Sie nicht SELECT angegeben haben. Aber wenn Sie vorher DISTINCT angegeben haben, können Sie nicht mehr nach Zeilen/Ausdrücken ordnen, die nicht ausgewählt wurden. Warum? Weil die Reihenfolge völlig undefiniert wäre.
  • OFFSET: Don’t use offset
  • LIMIT, FETCH, TOP: Jetzt setzen vernünftige Datenbanken die LIMIT (MySQL, PostgreSQL) oder FETCH (DB2, Oracle 12c, SQL Server 2012) Klausel syntaktisch ganz ans Ende. Früher dachten Sybase und SQL Server, es wäre eine gute Idee, TOP als Schlüsselwort in SELECT zu haben. Als ob die richtige Reihenfolge von SELECT DISTINCT nicht schon verwirrend genug wäre.

Da haben wir es. Es macht absolut Sinn. Und wenn Sie jemals etwas tun wollen, das nicht in der „richtigen Reihenfolge“ ist, ist der einfachste Trick immer, auf eine abgeleitete Tabelle zurückzugreifen. Wenn Sie z.B. nach einer Fensterfunktion gruppieren wollen:

Warum funktioniert das? Weil:

  • In der abgeleiteten Tabelle geschieht zuerst FROM, dann wird die WINDOW berechnet, dann wird der Bereich SELECTgefüllt.
  • Das äußere SELECT kann nun das Ergebnis dieser Fensterfunktionsberechnung wie eine gewöhnliche Tabelle in der FROM-Klausel behandeln, dann GROUP BY eine gewöhnliche Spalte, dann Aggregat, dann SELECT

Lassen Sie uns unsere ursprünglichen Beispiele mit einer Erklärung, warum sie funktionieren oder warum sie nicht funktionieren, überprüfen.

Denken Sie immer an die logische Reihenfolge der Operationen

Wenn Sie nicht häufig SQL schreiben, kann die Syntax tatsächlich verwirrend sein. Insbesondere GROUP BY und Aggregationen „infizieren“ den Rest der gesamten SELECTKlausel, und die Dinge werden wirklich seltsam. Wenn wir mit dieser Verwirrung konfrontiert werden, haben wir zwei Möglichkeiten:

  • Wütend werden und die Entwickler der SQL-Sprache anschreien
  • Unser Schicksal akzeptieren, die Augen schließen, die Snytax vergessen und sich an die Reihenfolge der logischen Operationen erinnern

Ich empfehle im Allgemeinen Letzteres, denn dann machen die Dinge viel mehr Sinn, einschließlich der folgenden schönen Berechnung der kumulierten Tageseinnahmen, die die Tageseinnahmen (SUM(amount) Aggregatfunktion) innerhalb der kumulierten Einnahmen (SUM(...) OVER (...) Fensterfunktion) verschachtelt:

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… weil Aggregationen logischerweise vor Fensterfunktionen stattfinden.

Caveat: ORDER BY-Klausel

Es gibt einige Caveats rund um die ORDER BY-Klausel, die zu weiterer Verwirrung beitragen könnten. Standardmäßig wird weiterhin davon ausgegangen, dass die logische Reihenfolge der Operationen korrekt ist. Es gibt jedoch einige Sonderfälle, insbesondere:

  • In Abwesenheit einer DISTINCT-Klausel
  • In Abwesenheit von Mengenoperationen wie UNION

Sie können in ORDER BY Ausdrücke referenzieren, die nicht durch SELECT projiziert werden. Die folgende Abfrage ist in den meisten Datenbanken völlig in Ordnung:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Es gibt eine „virtuelle“ / implizite ACTOR_ID-Projektion, als ob wir geschrieben hätten:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Aber dann die ACTOR_ID-Spalte wieder aus dem Ergebnis entfernt. Das ist sehr praktisch, obwohl es zu einiger Verwirrung über die Semantik und die Reihenfolge der Operationen führen kann. Insbesondere können Sie z.B. DISTINCT in einer solchen Situation nicht verwenden. Die folgende Abfrage ist ungültig:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Denn was ist, wenn es zwei Akteure mit demselben Namen, aber mit sehr unterschiedlichen IDs gibt? Die Reihenfolge wäre dann undefiniert.

Bei Mengenoperationen wird noch deutlicher, warum dies nicht zulässig ist:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

In diesem Fall ist die Spalte ACTOR_ID in der Tabelle CUSTOMER nicht vorhanden, so dass die Abfrage überhaupt keinen Sinn ergibt.

Weiter lesen

Sollen wir Ihnen mehr zeigen? Wir haben auch diese Artikel für Sie:

  • SQL GROUP BY und funktionale Abhängigkeiten: Ein sehr nützliches Feature
  • Wie SQL GROUP BY hätte gestaltet werden sollen – wie Neo4js implizites GROUP BY
  • Wie man SQL GROUP BY und Aggregationen in Java 8 übersetzt
  • Verstehen Sie wirklich die SQL GROUP BY und HAVING Klauseln?
  • GROUP BY ROLLUP / CUBE

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.