Frage INNER JOIN ON vs WHERE-Klausel


Der Einfachheit halber werden alle relevanten Felder angenommen NOT NULL.

Du kannst tun:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Oder aber:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Arbeiten diese beiden in gleicher Weise? MySQL?


761
2018-06-19 16:16


Ursprung


Antworten:


INNER JOIN ist ANSI-Syntax, die Sie verwenden sollten.

Es wird allgemein als besser lesbar angesehen, besonders wenn Sie vielen Tabellen beitreten.

Es kann auch leicht mit einem ersetzt werden OUTER JOIN wann immer eine Notwendigkeit entsteht.

Das WHERE Syntax ist mehr relationales Modell orientiert.

Ein Ergebnis von zwei Tabellen JOINed ist ein kartesisches Produkt der Tabellen, auf die ein Filter angewendet wird, der nur die Zeilen mit übereinstimmenden Spalten auswählt.

Es ist einfacher, dies mit dem zu sehen WHERE Syntax.

Was Ihr Beispiel betrifft, sind diese beiden Abfragen in MySQL (und in SQL im Allgemeinen) Synonyme.

Beachten Sie auch, dass MySQL auch eine STRAIGHT_JOIN Klausel.

Mit dieser Klausel können Sie die JOIN Reihenfolge: Welche Tabelle wird in der äußeren Schleife und welche in der inneren Schleife gescannt.

Sie können dies nicht in MySQL mit steuern WHERE Syntax.


611
2018-06-19 16:17



Andere haben darauf hingewiesen, dass INNER JOIN der menschlichen Lesbarkeit dient, und das hat oberste Priorität; Ich stimme zu. Lass mich versuchen zu erklären Warum Die Joinsyntax ist besser lesbar.

Eine grundlegende SELECT-Abfrage ist dies:

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 die wir bekommen.

JOIN ist eine Aussage über die Tabellen, wie sie miteinander verbunden sind (konzeptionell eigentlich in einer einzigen Tabelle). Alle Abfrageelemente, die die Tabellen steuern - von denen wir Sachen beziehen - gehören semantisch zur FROM-Klausel (und natürlich gehen die JOIN-Elemente dorthin). Putting-Elemente in die WHERE-Klausel verbindet die welche und das woher; Aus diesem Grund wird die JOIN-Syntax bevorzugt.


145
2018-06-19 16:30



 Anwenden bedingter Anweisungen in ON / WHERE

Hier habe ich die logischen Abarbeitungsschritte erläutert.


Referenz: Innerhalb von Microsoft SQL Server 2005 T-SQL-Abfrage
Herausgeber: Microsoft Press
Pub-Datum: 7. März 2006
Drucken Sie ISBN-10: 0-7356-2313-9
Druck ISBN-13: 978-0-7356-2313-2
Seiten: 640

Innerhalb Microsoft SQL Server 2005 T-SQL-Abfrage

(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 merkwürdige 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 Tabelle, die im letzten Schritt generiert wurde, wird an den Aufrufer zurückgegeben. Wenn eine bestimmte Klausel in einer Abfrage nicht angegeben ist, wird der entsprechende Schritt einfach übersprungen.

Kurze Beschreibung der logischen Abfrageverarbeitungsphasen

Mach dir keine Sorgen, wenn die Beschreibung der Schritte vorerst nicht sinnvoll erscheint. Diese werden als Referenz zur Verfügung gestellt. Abschnitte, die nach dem Szenariobeispiel folgen, werden die Schritte viel ausführlicher behandeln.

  1. FROM: Ein kartesisches Produkt (Kreuzverbindung) 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. Nur Zeilen, für die der <join_condition> ist TRUE sind in VT2 eingefügt.

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

  4. WHERE: Der WHERE-Filter wird auf VT3 angewendet. Nur Zeilen, für die der <where_condition> ist TRUE sind in VT4 eingefügt.

  5. GROUP BY: Die Zeilen von VT4 sind in Gruppen angeordnet, die auf der in der GROUP BY-Klausel angegebenen Spaltenliste basieren. VT5 wird generiert.

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

  7. HAVING: Der HAVING-Filter wird auf VT6 angewendet. Nur Gruppen, für die die <having_condition> ist TRUE sind in VT7 eingefügt.

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

  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 erzeugt (VC10).

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



     Daher wird (INNER JOIN) ON die Daten filtern (die Datenanzahl von VT wird hier selbst reduziert), bevor die WHERE-Klausel angewendet wird. Die nachfolgenden Join-Bedingungen werden mit gefilterten Daten ausgeführt, die die Leistung verbessern. Danach wendet nur die WHERE-Bedingung Filterbedingungen an.

(Das Anwenden von bedingten Anweisungen in ON / WHERE wird in wenigen Fällen keinen großen Unterschied machen. Dies hängt davon ab, wie viele Tabellen Sie verbunden haben und wie viele Zeilen in den Join-Tabellen verfügbar sind.)


112
2017-12-22 06:24



Die implizite Join-ANSI-Syntax ist älter, weniger offensichtlich und nicht empfehlenswert.

Darüber hinaus ermöglicht die relationale Algebra die Austauschbarkeit der Prädikate in der WHERE Klausel und die INNER JOINso eben INNER JOIN fragt mit ab WHERE Bei Klauseln können die Prädikate vom Optimierer neu angeordnet werden.

Ich empfehle Ihnen, die Suchanfragen auf möglichst lückenlose Weise zu schreiben.

Manchmal schließt das ein, das zu machen INNER JOIN relativ "unvollständig" und einige der Kriterien in die WHERE einfach, um die Listen der Filterkriterien leichter wartbar zu machen.

Zum Beispiel, anstatt:

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.


55
2018-06-19 16:23



Implizite Joins (zu denen Ihre erste Abfrage gehört) werden viel verwirrender, schwer zu lesen und zu warten, wenn Sie mehr Tabellen zu Ihrer Abfrage hinzufügen müssen. Stellen Sie sich vor, Sie würden dieselbe Abfrage und denselben Join-Typ auf vier oder fünf verschiedenen Tabellen ausführen ... das ist ein Albtraum.

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


25
2018-06-19 16:19



Ich werde auch darauf hinweisen, dass die Verwendung der älteren Syntax eher fehleranfällig ist. Wenn Sie innere Joins ohne ON-Klausel verwenden, erhalten Sie einen Syntaxfehler. 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 oft, indem sie das distinct-Schlüsselwort hinzufügen (anstatt den Join zu fixieren, weil sie immer noch nicht erkennen, dass der Join selbst unterbrochen ist), was das Problem zu beheben scheint, aber die Abfrage erheblich verlangsamen wird.

Zusätzlich zur Wartung, wenn Sie einen Cross-Join in der alten Syntax haben, wie wird der Betreuer wissen, ob Sie einen haben wollten (es gibt Situationen, in denen Cross-Joins benötigt werden) oder ob es ein Unfall war, der behoben werden sollte?

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

Plus (persönlich hier), ist der Standard mit den expliziten Joins über 20 Jahre alt, was bedeutet, dass implizite Join-Syntax seit diesen 20 Jahren veraltet ist. Würden Sie Anwendungscode mit einer seit 20 Jahren veralteten Syntax schreiben? Warum möchten Sie Datenbankcode schreiben?


21
2018-06-19 16:46



Sie haben eine andere von Menschen lesbare Bedeutung.

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

Sie sollten immer codieren, um lesbar zu sein.

Das heißt, wenn dies eine integrierte Beziehung ist, verwenden Sie den expliziten Join. Wenn Sie mit schwach verwandten Daten übereinstimmen, verwenden Sie die Where-Klausel.


12
2018-06-19 16:20



Der SQL: 2003-Standard hat einige Vorrangregeln geändert, sodass eine JOIN-Anweisung Vorrang vor einem "Komma" -Join hat. Dies kann die Ergebnisse Ihrer Abfrage abhängig von der Einrichtung ändern. Dies verursacht einige Probleme für einige Leute, wenn MySQL 5.0.12 auf den Standard umgestellt hat.

In Ihrem Beispiel würden Ihre Abfragen also gleich funktionieren. Aber wenn Sie eine dritte Tabelle hinzugefügt haben: SELECT ... FROM Tabelle1, Tabelle2 JOIN Tabelle3 ON ... WHERE ...

Vor MySQL 5.0.12 wurden zuerst Tabelle1 und Tabelle2 und dann Tabelle3 hinzugefügt. 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 werden es vielleicht nicht einmal erkennen.

Ich benutze nie mehr die "Komma" -Syntax und wähle das zweite Beispiel. Es ist sowieso viel besser lesbar, die JOIN-Bedingungen sind mit den JOINs, nicht getrennt in einem separaten Abfrageabschnitt.


10
2018-06-19 17:28



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


4
2018-06-19 17:03



ANSI-Joinsyntax ist definitiv portabler.

Ich gehe durch ein Upgrade von Microsoft SQL Server, und ich würde auch erwähnen, dass die = * und * = Syntax für Outer-Joins in SQL Server (ohne Kompatibilitätsmodus) für 2005 SQL Server und höher nicht unterstützt wird.


1
2018-06-19 16:50