webentwicklung-frage-antwort-db.com.de

SQL Left Join gegen mehrere Tabellen in der FROM-Zeile?

Die meisten SQL-Dialekte akzeptieren beide folgenden Abfragen:

SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x

SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x

Wenn Sie jetzt offensichtlich einen Outer Join benötigen, ist die zweite Syntax erforderlich. Aber warum sollte ich bei einem Inner Join die zweite Syntax der ersten vorziehen (oder umgekehrt)?

242
jmucchiello

Die alte Syntax, bei der nur die Tabellen aufgelistet und die WHERE -Klausel zum Angeben der Verknüpfungskriterien verwendet werden, wird in den meisten modernen Datenbanken nicht mehr empfohlen.

Es ist nicht nur zum Anzeigen gedacht, die alte Syntax kann mehrdeutig sein, wenn Sie sowohl INNER- als auch OUTER-Joins in derselben Abfrage verwenden.

Lassen Sie mich Ihnen ein Beispiel geben.

Nehmen wir an, Sie haben 3 Tabellen in Ihrem System:

Company
Department
Employee

Jede Tabelle enthält mehrere miteinander verknüpfte Zeilen. Sie haben mehrere Unternehmen, und jedes Unternehmen kann mehrere Abteilungen haben, und jede Abteilung kann mehrere Mitarbeiter haben.

Ok, jetzt wollen Sie folgendes machen:

Listen Sie alle Unternehmen mit allen Abteilungen und Mitarbeitern auf. Beachten Sie, dass einige Unternehmen noch keine Abteilungen haben, aber stellen Sie sicher, dass Sie auch diese einbeziehen. Stellen Sie sicher, dass Sie nur Abteilungen mit Mitarbeitern abrufen, aber immer alle Unternehmen auflisten.

Also machst du das:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

Beachten Sie, dass es sich bei der letzten Verknüpfung um eine innere Verknüpfung handelt, um die Kriterien zu erfüllen, die Sie nur für Abteilungen mit Personen benötigen.

Ok, was passiert jetzt? Das Problem ist, dass es vom Datenbankmodul, dem Abfrageoptimierer, den Indizes und den Tabellenstatistiken abhängt. Lassen Sie mich erklären.

Wenn das Abfrageoptimierungsprogramm feststellt, dass zuerst eine Firma gesucht, dann die Abteilungen gesucht und anschließend eine interne Verknüpfung mit den Mitarbeitern hergestellt wird, erhalten Sie keine Firmen, die keine Abteilungen haben.

Der Grund dafür ist, dass die WHERE -Klausel bestimmt, welche Zeilen im Endergebnis enden, nicht einzelne Teile der Zeilen.

In diesem Fall ist die Spalte "Department.ID" aufgrund des Links-Joins NULL. Wenn es also um "INNER JOIN to Employee" geht, kann diese Einschränkung für die Zeile "Employee" nicht erfüllt werden erscheinen.

Wenn sich das Abfrageoptimierungsprogramm dagegen entscheidet, zuerst den Beitritt von Abteilungsmitarbeitern in Angriff zu nehmen und dann einen Link-Beitritt mit den Unternehmen durchzuführen, werden diese angezeigt.

Die alte Syntax ist also nicht eindeutig. Es gibt keine Möglichkeit anzugeben, was Sie möchten, ohne mit Abfragetipps umzugehen, und einige Datenbanken haben überhaupt keine Möglichkeit.

Geben Sie die neue Syntax ein, mit der Sie wählen können.

Wenn Sie beispielsweise alle Unternehmen haben möchten, wie in der Problembeschreibung angegeben, schreiben Sie Folgendes:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

Hier geben Sie an, dass der Beitritt zwischen Abteilung und Mitarbeiter als ein Beitritt erfolgen soll, und lassen die Ergebnisse dann bei den Unternehmen.

Angenommen, Sie möchten nur Abteilungen, deren Name den Buchstaben X enthält. Wiederum besteht bei Joins im alten Stil die Gefahr, dass Sie das Unternehmen ebenfalls verlieren. Wenn es keine Abteilungen mit einem X im Namen hat, aber mit der neuen Syntax, können Sie dies tun:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

Diese zusätzliche Klausel wird für das Zusammenfügen verwendet, ist jedoch kein Filter für die gesamte Zeile. Die Zeile wird möglicherweise mit Unternehmensinformationen angezeigt, enthält jedoch möglicherweise NULL-Werte in allen Abteilungs- und Mitarbeiterspalten für diese Zeile, da für dieses Unternehmen keine Abteilung mit einem X im Namen vorhanden ist. Dies ist mit der alten Syntax schwierig.

Aus diesem Grund hat Microsoft unter anderen Anbietern die alte äußere Verknüpfungssyntax, aber nicht die alte innere Verknüpfungssyntax seit SQL Server 2005 und höher verworfen. Die einzige Möglichkeit, mit einer Datenbank unter Microsoft SQL Server 2005 oder 2008 unter Verwendung der Outer Join-Syntax des alten Stils zu kommunizieren, besteht darin, diese Datenbank in den Kompatibilitätsmodus 8.0 (auch bekannt als SQL Server 2000) zu versetzen.

Außerdem war der alte Weg, eine Reihe von Tabellen mit WHERE-Klauseln in das Abfrageoptimierungsprogramm zu werfen, vergleichbar mit dem Sprichwort "Hier sind Sie, tun Sie das Beste, was Sie können". Mit der neuen Syntax muss der Abfrageoptimierer weniger Arbeit leisten, um herauszufinden, welche Teile zusammengehören.

Da haben Sie es also.

LINKS und INNEN VERBINDEN ist die Welle der Zukunft.

Die JOIN-Syntax hält die Bedingungen in der Nähe der Tabelle, für die sie gelten. Dies ist besonders nützlich, wenn Sie eine große Anzahl von Tabellen verknüpfen.

Übrigens können Sie auch einen Outer Join mit der ersten Syntax durchführen:

WHERE a.x = b.x(+)

Oder

WHERE a.x *= b.x

Oder

WHERE a.x = b.x or a.x not in (select x from b)
16
Andomar

Grundsätzlich, wenn Ihre FROM-Klausel Tabellen wie folgt auflistet:

SELECT * FROM
  tableA, tableB, tableC

das Ergebnis ist ein Kreuzprodukt aller Zeilen in den Tabellen A, B, C. Dann wenden Sie die Einschränkung WHERE tableA.id = tableB.a_id das wird eine große Anzahl von Reihen wegwerfen, dann weiter ... AND tableB.id = tableC.b_id und Sie sollten dann nur die Zeilen erhalten, die Sie wirklich interessieren.

DBMS wissen, wie man diese SQL optimiert, sodass der Leistungsunterschied zum Schreiben mit JOINs (falls vorhanden) vernachlässigbar ist. Die Verwendung der JOIN-Notation macht die SQL-Anweisung more lesbar (IMHO, wenn keine Joins verwendet werden, verwandelt sich die Anweisung in ein Chaos). Wenn Sie das Cross-Produkt verwenden, müssen Sie in der WHERE-Klausel Join-Kriterien angeben. Dies ist das Problem mit der Notation. Sie füllen Ihre WHERE-Klausel mit Dingen wie

    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 

dies wird nur verwendet, um das Kreuzprodukt einzuschränken. Die WHERE-Klausel sollte nur EINSCHRÄNKUNGEN für die Ergebnismenge enthalten. Wenn Sie Tabellenverknüpfungskriterien mit Ergebnismengeneinschränkungen mischen, ist Ihre Abfrage für Sie (und andere) schwerer zu lesen. Sie sollten auf jeden Fall JOINs verwenden und die FROM-Klausel als FROM-Klausel und die WHERE-Klausel als WHERE-Klausel beibehalten.

11
Peter Perháč

Der erste Weg ist der ältere Standard. Die zweite Methode wurde in SQL-92 eingeführt, http://en.wikipedia.org/wiki/SQL . Der vollständige Standard kann unter http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt eingesehen werden.

Es dauerte viele Jahre, bis Datenbankunternehmen den SQL-92-Standard einführten.

Der Grund, warum die zweite Methode bevorzugt wird, ist der SQL-Standard gemäß dem ANSI- und ISO-Standardkomitee.

11
Dwight T

Die zweite Option wird bevorzugt, da die Wahrscheinlichkeit eines versehentlichen Cross-Joins bei weitem geringer ist, da das Einfügen der where-Klausel vergessen wird. Ein Join ohne on-Klausel wird die Syntaxprüfung nicht bestehen, ein Join im alten Stil ohne where-Klausel wird nicht fehlschlagen, es wird ein Cross-Join durchgeführt.

Darüber hinaus ist es für die Wartung hilfreich, wenn Sie später einen linken Join erstellen müssen, dass sich alle in derselben Struktur befinden. Und die alte Syntax ist seit 1992 nicht mehr aktuell. Es ist längst nicht mehr an der Zeit, sie nicht mehr zu verwenden.

Außerdem habe ich festgestellt, dass viele Leute, die ausschließlich die erste Syntax verwenden, Verknüpfungen nicht wirklich verstehen und das Verständnis von Verknüpfungen entscheidend ist, um bei Abfragen korrekte Ergebnisse zu erzielen.

9
HLGEM

Ich denke, es gibt einige gute Gründe auf dieser Seite, um die zweite Methode zu übernehmen - mit expliziten JOINs. Der Clou ist jedoch, dass, wenn die JOIN-Kriterien aus der WHERE-Klausel entfernt werden, die verbleibenden Auswahlkriterien in der WHERE-Klausel viel einfacher zu sehen sind.

In wirklich komplexen SELECT-Anweisungen wird es für einen Leser viel einfacher zu verstehen, was vor sich geht.

6
Alan G

Das SELECT * FROM table1, table2, ... Syntax ist für ein paar Tabellen in Ordnung, aber es wird exponentiell (nicht unbedingt eine mathematisch genaue Aussage) schwieriger und schwerer zu lesen, wenn die Anzahl der Tabellen zunimmt.

Die JOIN-Syntax ist (zu Beginn) schwieriger zu schreiben, macht jedoch deutlich, welche Kriterien sich auf welche Tabellen auswirken. Dies macht es viel schwieriger, einen Fehler zu machen.

Wenn alle Verknüpfungen INNER sind, sind beide Versionen äquivalent. Sobald Sie jedoch irgendwo in der Anweisung eine OUTER-Verknüpfung haben, werden die Dinge viel komplizierter und es ist praktisch garantiert, dass das, was Sie schreiben, nicht in Frage gestellt wird, was Sie zu schreiben glauben.

5
Euro Micelli

Wenn Sie einen Outer Join benötigen, ist die zweite Syntax nicht immer erforderlich :

Orakel:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x = b.x(+)

MSSQLServer (obwohl es veraltet in der 2000-Version war)/Sybase:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x *= b.x

Aber zurück zu Ihrer Frage. Ich kenne die Antwort nicht, aber es hängt wahrscheinlich damit zusammen, dass ein Join natürlicher (zumindest syntaktisch) ist als das Hinzufügen eines Ausdrucks zu einer where -Klausel, wenn Sie genau das tun: Joining .

2

Ich höre viele Leute, die sich darüber beschweren, dass der erste zu schwer zu verstehen und unklar ist. Ich sehe kein Problem damit, aber nach dieser Diskussion verwende ich die zweite sogar für INNER JOINS, um Klarheit zu schaffen.

0
kemiller2002

Für die Datenbank sind sie alle gleich. In manchen Situationen müssen Sie jedoch diese zweite Syntax verwenden. Um Abfragen zu bearbeiten, die letztendlich verwendet werden müssen (um herauszufinden, dass Sie einen linken Join benötigen, bei dem Sie einen geraden Join hatten), und um die Konsistenz zu gewährleisten, würde ich nur die zweite Methode als Muster verwenden. Das erleichtert das Lesen von Abfragen.

0
Jeff Ferland

Nun, die erste und die zweite Abfrage können zu unterschiedlichen Ergebnissen führen, da ein LEFT JOIN alle Datensätze aus der ersten Tabelle enthält, auch wenn die rechte Tabelle keine entsprechenden Datensätze enthält.

0
Gavin H