Einführung in Äußere Joins

Äußere Joins

Äußere Joins werden verwendet, um Zeilen aus zwei Tabellen abzugleichen. Auch wenn es keine Übereinstimmung Zeilen enthalten sind. Zeilen aus einer der Tabellen sind immer enthalten, für die andere, wenn keine Übereinstimmungen vorliegen, sind Nullwerte enthalten.

Die Serie beginnt mit dem Artikel Einführung in Datenbank-Joins. Alle Beispiele für diese Lektion basieren auf Microsoft SQL Server Management Studio und der AdventureWorks2012-Datenbank. Sie können mit diesen kostenlosen Tools mit meinem Handbuch Erste Schritte mit SQL Server beginnen., In diesem Artikel werden wir äußere Joins behandeln.

Typen äußerer Joins

Es gibt drei Arten äußerer Joins:

• Linker äußerer Join-Alle Zeilen aus der linken Tabelle sind enthalten, nicht übereinstimmende Zeilen von rechts werden durch Nullwerte ersetzt.
• Right Outer Join-Alle Zeilen aus der rechten Tabelle sind enthalten, nicht übereinstimmende Zeilen von links werden durch Nullwerte ersetzt.
• Vollständiger äußerer Join – Alle Zeilen aus beiden Tabellen sind enthalten, Nullwerte füllen nicht übereinstimmende Zeilen.

Lassen Sie uns tiefer graben und den linken äußeren Join erkunden.,

Linker äußerer Join

Überprüfen Sie das folgende Datenmodell. Dies stammt aus der AdventureWorks2012-Datenbank. In diesem Modell gibt es 1 Person zu 0 oder 1 Angestellten.

Um eine Liste aller Personennamen zu erstellen, aber auch JobTitle anzuzeigen, wenn die Person ein Mitarbeiter ist, benötigen wir eine Möglichkeit, die beiden Tabellen zu verbinden und Personenzeilen in das Ergebnis aufzunehmen, auch wenn sie nicht mit Employee übereinstimmen.

Diese Art von Join wird als linker äußerer Join bezeichnet, da alle Zeilen für die Tabelle von der linken Seite des Schlüsselworts JOIN unabhängig von der Übereinstimmung enthalten sind., Die grundlegende Syntax für einen left outer Join lautet:

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

Die SQL für den Join im obigen Diagramm lautet:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Hier sind die ersten Ergebnisse aus der Abfrage

Beachten Sie, dass in der zweiten Zeile Nullwerte für NationalIDNumber und JobTitle. Dies liegt daran, dass es keine Mitarbeiter gibt, die BusinessEntityID 293 entsprechen.

Rechter äußerer Join

Werfen wir einen weiteren Blick auf das Diagramm, aber dieses Mal machen wir einen rechten äußeren Join., Wie Sie vielleicht erraten haben, gibt es in der SQL-Anweisung keinen allzu großen Unterschied zwischen einem left outer Join und einem right outer Join. Die grundlegende Syntax für einen rechten äußeren Join ist:

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

Unten ist unsere Beispielabfrage, die als rechter äußerer Join geschrieben wurde:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Der Hauptunterschied besteht darin, dass wir jetzt alle Datensätze aus der Employee-Tabelle zurückgeben, die die Tabelle rechts neben dem join-Schlüsselwort ist. Wenn kein übereinstimmender Mitarbeiterdatensatz gefunden wird, wird NULL für BusinessEntityID und LastName zurückgegeben.

Hier sind die Ergebnisse der Abfrage.,

Ich scrollte durch alle Ergebnisse und war überrascht, keine Null-Werte zu sehen.

Weißt du warum?

Die Antwort liegt im Datenmodell. Es gibt eine 0..1-zu-1-Beziehung zwischen Mitarbeiter und Person. Dies bedeutet, dass es für jeden Mitarbeiter eine Person gibt. Vor diesem Hintergrund gibt es für den richtigen Join keine nicht übereinstimmenden Zeilen. Bei dieser Art von Beziehung hätten Sie auch einen inneren Join verwenden können.

Linken und Rechten Äußeren Joins

Es gibt keinen Unterschied in der Funktionalität zwischen einem left outer join und right outer join.,

Die Anweisung

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Gibt das gleiche Ergebnis zurück wie

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID

Dies wäre natürlich nicht der Fall, wenn ich nur den Join von LINKS nach RECHTS geändert und die Tabellennamen nicht gewechselt hätte.

Normalerweise verwende ich left outer Joins mehr als right outer Joins. Ich denke, das liegt daran, dass ich beim Zeichnen von Beziehungen von links nach rechts dies tue. Außerdem durchquere ich Tabellen in meinem Kopf von links nach rechts.

Dies passt gut zu SQL, da sich die“ linke “ Tabelle in der FROM-Anweisung befindet.

Ich bin gespannt, was Sie verwenden., Ich bin wirklich neugierig zu wissen, ob eine rechte Sprache für Sie intuitiver erscheint, wenn Sie Muttersprachler von Arabisch oder einer anderen „von rechts nach links“ Sprache sind.

Full Outer Join

Ein full outer Join ist die Kombination von Ergebnissen aus einem left und right outer Join. Die von dieser Art von Join zurückgegebenen Ergebnisse enthalten alle Zeilen aus beiden Tabellen. Wo Übereinstimmungen auftreten, sind Werte verwandt. Wenn Übereinstimmungen aus beiden Tabellen nicht vorhanden sind, wird stattdessen NULL zurückgegeben.,

Die grundlegende Syntax für einen vollständigen Outer Join lautet:

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

Schauen wir uns einen anderen Teil der AdventureWork2012-Datenbank an. Dieses mal konzentrieren wir uns auf die Beziehungen zwischen SalesOrderHeader und CurrencyRate Tabellen.

Das Modell ist unten dargestellt:

Angenommen, wir möchten alle Währungen kennen, in denen wir Bestellungen aufgeben können und in denen Bestellungen in diesen Währungen getätigt wurden.

SELECT sales.SalesOrderHeader.AccountNumber, sales.SalesOrderHeader.OrderDate, sales.CurrencyRate.ToCurrencyCode, sales.CurrencyRate.AverageRateFROM sales.SalesOrderHeaderFULL OUTER JOIN sales.CurrencyRate ON sales.CurrencyRate.CurrencyRateID = sales.SalesOrderHeader.CurrencyRateID

Hier ist ein Teil der Ergebnisse, der zeigt, wo einige Verkäufe mit einer Währung übereinstimmen und einige nicht., Der Grund, warum es Verkäufe gibt, die nicht übereinstimmen, ist, dass dies Verkäufe in USD sind.

Weiter unten in den Ergebnissen sehen Sie Währungen ohne übereinstimmende Verkäufe. Dies spiegelt die Tatsache wider, dass in diesen Währungen keine Verkäufe getätigt wurden.

Hinweis: Ich war überrascht, USD gelistet zu sehen, siehe Zeile 42463, da ich denke, dass ein Großteil des Umsatzes in dieser Währung sein würde. Mein Gedanke ist, dass der SalesOrderHeader-Wert für CurrencyRateID für alle USD-Transaktionen auf Null gesetzt wurde, anstatt den Wechselkurs für diese Transaktion zu verehren., Ich denke, das ist inkonsistent und nicht so, wie ich es tun würde, aber es ist nicht meine Datenbank…

Fortgeschrittenes Beispiel

Bisher haben wir uns die drei Arten von äußeren Joins angesehen, aber einige fortgeschrittenere Konzepte wie das Verbinden mehrerer Tabellen und die Verwendung von mehr als einer Bedingung in unseren Join-Klauseln wurden nicht untersucht.

Wir haben diese Konzepte behandelt, als wir Inner Joins erforschten, also sollte das, was ich Ihnen zeigen werde, nicht zu neu sein, aber ich denke, es ist immer noch sinnvoll zu überprüfen, da in einigen Fällen das Mischen von Full Joins mit inner Joins unerwartete oder unbeabsichtigte Ergebnisse hervorrufen kann.,

Wenden wir uns dem Produktionsschema zu und untersuchen Produkte und Kategorien. Lassen Sie uns eine Liste aller Produktkategorien und der darin enthaltenen Produktmodelle erstellen.

Das Produkt hat eine Eins-zu-Viele-Beziehung zu ProductModel und ProductSubcategory. Da es zwischen diesen beiden Tabellen liegt, gibt es eine implizite viele zu viele Beziehung zwischen ProductModel und ProductSubcategory. Aus diesem Grund ist es ein guter Kandidat für äußere Joins, da es möglicherweise Produktmodelle ohne zugewiesene Produkte und ProductSubcategory-Einträge ohne Produkt gibt.,

Um diese Situation zu überwinden, erstellen wir einen äußeren Join für die ProductModel-und ProductCategory-Tabelle.

Hier ist die SQL

SELECT PC.Name AS Category, PSC.Name AS Subcategory, PM.Name AS Model, P.Name AS ProductFROM Production.Product AS PFULL OUTER JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelIDFULL OUTER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryIDINNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

Es gibt mehrere Elemente zu beachten:

• Ich habe Tabellenaliase verwendet, um die SQL besser lesbar zu machen.
• Es gibt mehr als eine vollständige äußere Join-Klausel.,
• Die ProductCategory-Tabelle ist auch Teil eines äußeren Joins

Ursprünglich hatte ich beim Schreiben der SQL für diese Abfrage einen inneren Join zwischen ProductSubcategory und ProductCategory, aber ich sah keine Nullwerte für nicht übereinstimmende Datensätze, die ich erwarten würde.

Sobald ich den Join in einen vollständigen äußeren Join geändert habe, habe ich die erwarteten Ergebnisse gesehen. Der Grund dafür ist subtil.

Nach Überprüfung der Daten habe ich bestätigt, dass allen Kategorien Unterkategorien zugewiesen sind., In Anbetracht dessen würden Sie denken, dass ein innerer Join funktionieren würde; Bedenken Sie jedoch, dass der Wert ProductSubcategoryID NULL ist, wenn die gesamte Anweisung ausgeführt und Zeilen zurückgegeben werden, wenn ein Produkt nicht mit einer Produktunterkategorie übereinstimmt.

Nullwerte sind per Definition nicht gleich, daher schlägt der innere Join fehl. Wenn diese Werte dann mit ProductCategory abgeglichen werden, werden sie daher nicht im Ergebnis enthalten, es sei denn, der Join zu ProductCategory ist ein äußerer Join.,

Tatsächlich muss der Join kein vollständiger äußerer Join sein, ein linker Join funktioniert genauso gut:

SELECT PC.Name AS Category,PSC.Name AS Subcategory,PM.Name AS Model,P.Name AS ProductFROM Production.Product AS PFULL OUTER JOINProduction.ProductModel AS PMON PM.ProductModelID = P.ProductModelIDFULL OUTER JOINProduction.ProductSubcategory AS PSCON PSC.ProductSubcategoryID = P.ProductSubcategoryIDLEFT OUTER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

Verwendet für Äußere Joins

Da äußere Joins nicht nur die übereinstimmenden Zeilen, sondern auch solche, die dies nicht tun, sind sie eine wirklich gute Möglichkeit, fehlende Einträge in Tabellen zu finden. Dies ist großartig, wenn Sie eine Diagnose in Ihrer Datenbank durchführen müssen, um festzustellen, ob Datenintegritätsprobleme vorliegen.

Angenommen, wir waren besorgt, dass wir einige ProductSubcategory-Einträge haben könnten, die nicht mit Kategorien übereinstimmen., Wir könnten testen, indem wir das folgende SQL

SELECT PSC.Name AS SubcategoryFROM Production.ProductCategory AS PSCLEFT OUTER JOIN Production.ProductSubcategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDWHERE PSC.ProductCategoryID is NULL

Der äußere Join gibt die nicht übereinstimmenden Zeilenwerte als Nullwerte zurück. Die where-Klausel filtert die Nicht-Null-Werte und lässt nur nicht übereinstimmende Unterkategorienamen übrig, die wir überprüfen können.

Äußere Joins können auch verwendet werden, um Fragen zu stellen wie:

“ Welche Verkäufer haben noch nie einen Verkauf getätigt?“

“ Welche Produkte sind einem Produktmodell nicht zugeordnet?“

“ Welche Abteilungen haben keine zugewiesenen Mitarbeiter?“

“ Listet alle Vertriebsgebiete auf, denen keine Vertriebsmitarbeiter zugewiesen sind.”

---