INNER JOIN ON vs WHERE-Klausel

Andere haben darauf hingewiesen, dass INNER JOIN die menschliche Lesbarkeit fördert, und das hat höchste Priorität. Genau. Lassen Sie mich versuchen zu erklären , warum die Join-Syntax besser lesbar ist.

Eine grundlegende SELECT-Abfrage lautet wie folgt:

SELECT stuff
FROM tables
WHERE conditions

Die SELECT-Klausel sagt uns was wir kommen zurück; Die FROM-Klausel sagt uns woher wir bekommen es, und die WHERE-Klausel sagt uns welche diejenigen, die wir bekommen.

JOIN ist eine Aussage über die Tabellen, wie sie miteinander verbunden sind (konzeptionell tatsächlich in einer einzigen Tabelle). Alle Abfrageelemente, die die Tabellen steuern - von denen wir Daten erhalten -, gehören semantisch zur FROM-Klausel (und natürlich gehen JOIN-Elemente dorthin). Das Einfügen von Verbindungselementen in die WHERE-Klausel setzt die Zeichen which und where-from ineinander. Aus diesem Grund wird die JOIN-Syntax bevorzugt.

Anwenden bedingter Anweisungen in ON/WHERE

Hier habe ich die Verarbeitungsschritte für logische Abfragen erläutert.

Referenz: In Microsoft® SQL Server ™ 2005 T-SQL-Abfragen
Herausgeber: Microsoft Press
Veröffentlichungsdatum: 7. März 2006
Print ISBN-10: 0-7356-2313-9
Print ISBN-13: 978-0-7356-2313-2
Seiten: 640

In Microsoft® SQL Server ™ 2005 T-SQL-Abfragen

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

Der erste erkennbare Aspekt von SQL, der sich von anderen Programmiersprachen unterscheidet, ist die Reihenfolge, in der der Code verarbeitet wird. In den meisten Programmiersprachen wird der Code in der Reihenfolge verarbeitet, in der er geschrieben wurde. In SQL ist die erste Klausel, die verarbeitet wird, die FROM-Klausel, während die SELECT-Klausel, die zuerst angezeigt wird, fast zuletzt verarbeitet wird.

Jeder Schritt generiert eine virtuelle Tabelle, die als Eingabe für den folgenden Schritt verwendet wird. Diese virtuellen Tabellen stehen dem Aufrufer nicht zur Verfügung (Client-Anwendung oder äußere Abfrage). Nur die vom letzten Schritt erzeugte Tabelle wird an den Aufrufer zurückgegeben. Wenn in einer Abfrage eine bestimmte Klausel nicht angegeben ist, wird der entsprechende Schritt einfach übersprungen.

Kurze Beschreibung der Verarbeitungsphasen für logische Abfragen

Machen Sie sich keine allzu großen Sorgen, wenn die Beschreibung der Schritte vorerst wenig Sinn macht. Diese dienen als Referenz. Abschnitte, die nach dem Szenariobeispiel folgen, werden die Schritte ausführlicher behandeln.

1. FROM: Ein kartesisches Produkt (Cross-Join) wird zwischen den ersten beiden Tabellen in der FROM-Klausel ausgeführt, und als Ergebnis wird die virtuelle Tabelle VT1 generiert.

2. ON: Der ON-Filter wird auf VT1 angewendet. In VT2 werden nur Zeilen eingefügt, für die <join_condition> TRUE ist.

3. OUTER (Join): Wenn ein OUTER JOIN angegeben ist (im Gegensatz zu einem CROSS JOIN oder einem INNER JOIN), werden Zeilen aus der oder den beibehaltenen Tabellen, für die keine Übereinstimmung gefunden wurde, zu den Zeilen von VT2 als äußere Zeilen hinzugefügt und generiert VT3. Wenn mehr als zwei Tabellen in der FROM-Klausel enthalten sind, werden die Schritte 1 bis 3 zwischen dem Ergebnis des letzten Joins und der nächsten Tabelle in der FROM-Klausel wiederholt angewendet, bis alle Tabellen verarbeitet sind.

4. WHERE: Der WHERE-Filter wird auf VT3 angewendet. In VT4 werden nur Zeilen eingefügt, für die <where_condition> TRUE ist.

5. GROUP BY: Die Zeilen von VT4 werden anhand der in der GROUP BY-Klausel angegebenen Spaltenliste in Gruppen angeordnet. VT5 wird generiert.

6. CUBE | ROLLUP: Supergroups (Gruppen von Gruppen) werden zu den Zeilen von VT5 hinzugefügt, wodurch VT6 generiert wird.

7. HAVING: Der Filter HAVING wird auf VT6 angewendet. In VT7 werden nur Gruppen eingefügt, für die <having_condition> TRUE ist.

8. SELECT: Die SELECT-Liste wird verarbeitet und VT8 generiert.

9. DISTINCT: Doppelte Zeilen werden aus VT8 entfernt. VT9 wird generiert.

10. ORDER BY: Die Zeilen von VT9 werden nach der in der ORDER BY-Klausel angegebenen Spaltenliste sortiert. Ein Cursor wird generiert (VC10).

11. TOP: Die angegebene Anzahl oder der angegebene Prozentsatz der Zeilen wird ab dem Beginn von VC10 ausgewählt. Die Tabelle VT11 wird generiert und an den Aufrufer zurückgegeben.

(Das Anwenden bedingter Anweisungen in ON/WHERE macht in einigen Fällen keinen großen Unterschied. Dies hängt davon ab, wie viele Tabellen Sie verknüpft haben und wie viele Zeilen in den einzelnen Verknüpfungstabellen verfügbar sind.)

Die ANSI-Syntax für implizite Verknüpfungen ist älter, weniger offensichtlich und wird nicht empfohlen.

Darüber hinaus ermöglicht die relationale Algebra die Austauschbarkeit der Prädikate in der WHERE -Klausel und der INNER JOIN -Klausel, sodass selbst bei INNER JOIN -Abfragen mit WHERE -Klauseln die Prädikate vom Optimierer neu angeordnet werden können .

Ich empfehle Ihnen, die Abfragen so lesbar wie möglich zu schreiben.

Manchmal schließt dies ein, den INNER JOIN relativ "unvollständig" zu machen und einige der Kriterien in den WHERE einzufügen, um die Listen der Filterkriterien einfacher pflegbar zu machen.

Zum Beispiel anstelle von:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Schreiben:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

Aber es kommt natürlich darauf an.

Implizite Verknüpfungen (wie Ihre erste Abfrage bezeichnet wird) werden viel verwirrender, sind schwer zu lesen und zu warten, sobald Sie beginnen müssen, Ihrer Abfrage weitere Tabellen hinzuzufügen. Stellen Sie sich vor, Sie führen dieselbe Abfrage und denselben Join-Typ an vier oder fünf verschiedenen Tabellen aus. Das ist ein Albtraum.

Die Verwendung eines expliziten Joins (Ihr zweites Beispiel) ist viel lesbarer und einfacher zu pflegen.

Ich werde auch darauf hinweisen, dass die Verwendung der älteren Syntax eher fehleranfällig ist. Wenn Sie innere Joins ohne ON-Klausel verwenden, wird ein Syntaxfehler angezeigt. Wenn Sie die ältere Syntax verwenden und eine der Join-Bedingungen in der where-Klausel vergessen, erhalten Sie einen Cross-Join. Die Entwickler beheben dies häufig, indem sie das eindeutige Schlüsselwort hinzufügen (anstatt den Join zu reparieren, da sie immer noch nicht erkennen, dass der Join selbst nicht funktioniert). Dies scheint das Problem zu beheben, verlangsamt aber die Abfrage erheblich.

Wenn Sie einen Cross-Join in der alten Syntax haben, woher weiß der Maintainer zusätzlich, ob Sie einen haben wollten (es gibt Situationen, in denen Cross-Joins erforderlich sind) oder ob es sich um einen Unfall handelte, der behoben werden sollte?

Lassen Sie mich auf diese Frage verweisen, um zu sehen, warum die implizite Syntax schlecht ist, wenn Sie Left-Joins verwenden. Sybase * = zu Ansi Standard mit 2 verschiedenen äußeren Tabellen für dieselbe innere Tabelle

Außerdem ist der Standard, der die expliziten Verknüpfungen verwendet, über 20 Jahre alt, was bedeutet, dass die implizite Verknüpfungssyntax in diesen 20 Jahren veraltet ist. Würden Sie Anwendungscode mit einer Syntax schreiben, die seit 20 Jahren veraltet ist? Warum möchten Sie also Datenbankcode schreiben?

Sie haben eine andere vom Menschen lesbare Bedeutung.

Abhängig vom Abfrageoptimierer haben sie jedoch möglicherweise dieselbe Bedeutung für den Computer.

Sie sollten immer codieren, um lesbar zu sein.

Das heißt, wenn es sich um eine integrierte Beziehung handelt, verwenden Sie den expliziten Join. Wenn Sie mit schwach verwandten Daten übereinstimmen, verwenden Sie die where-Klausel.

Der SQL: 2003-Standard hat einige Vorrangregeln geändert, sodass eine JOIN-Anweisung Vorrang vor einer "Komma" -Verbindung hat. Dies kann die Ergebnisse Ihrer Abfrage abhängig von der Einrichtung ändern. Dies führte bei einigen Leuten zu Problemen, als MySQL 5.0.12 auf die Einhaltung des Standards umstellte.

In Ihrem Beispiel würden Ihre Abfragen also genauso funktionieren. Wenn Sie jedoch eine dritte Tabelle hinzugefügt haben: SELECT ... FROM table1, table2 JOIN table3 ON ... WHERE ...

Vor MySQL 5.0.12 wurden zuerst Tabelle1 und Tabelle2 und dann Tabelle3 verbunden. Jetzt (5.0.12 und höher) werden zuerst Tabelle2 und Tabelle3 und dann Tabelle1 verbunden. Es ändert nicht immer die Ergebnisse, aber es kann und Sie können es nicht einmal realisieren.

Ich verwende nie mehr die "Komma" -Syntax und entscheide mich für Ihr zweites Beispiel. Es ist ohnehin viel besser lesbar, die JOIN-Bedingungen sind bei den JOINs nicht in einen separaten Abfrageabschnitt unterteilt.

Ich weiß, dass Sie über MySQL sprechen, aber trotzdem: In Oracle 9 würden explizite und implizite Joins unterschiedliche Ausführungspläne generieren. AFAIK, die in Oracle 10+ gelöst wurde: Es gibt keinen solchen Unterschied mehr.

Die ANSI-Join-Syntax ist definitiv portabler.

Ich werde ein Upgrade von Microsoft SQL Server durchführen und erwähne außerdem, dass die Syntax = * und * = für äußere Verknüpfungen in SQL Server (ohne Kompatibilitätsmodus) für SQL Server 2005 und höher nicht unterstützt wird.

Wenn Sie häufig dynamische gespeicherte Prozeduren programmieren, werden Sie sich in Ihr zweites Beispiel verlieben (mit where). Wenn Sie verschiedene Eingabeparameter und viel Morph-Chaos haben, ist dies der einzige Weg. Andernfalls werden beide denselben Abfrageplan ausführen, sodass bei klassischen Abfragen definitiv kein offensichtlicher Unterschied besteht.