Heute mal ein kleiner Beitrag zum Thema SQL Join

Zuerst mal erstellen wir zwei Tabellen und fügen ein paar Daten zum testen ein.

CREATE TABLE [dbo].[TableLeft]([Key] [varchar](50) NULL,
                               [LeftValue] [varchar](50) NULL)

CREATE TABLE [dbo].[TableRight]([Key] [varchar](50) NULL,
                                [RightValue] [varchar](50) NULL)

INSERT [dbo].[TableLeft] ([Key], [LeftValue]) VALUES (N'3', N'LeftValue_1')
INSERT [dbo].[TableLeft] ([Key], [LeftValue]) VALUES (N'1', N'LeftValue_2')
INSERT [dbo].[TableRight] ([Key], [RightValue]) VALUES (N'1', N'RightValue_1')
INSERT [dbo].[TableRight] ([Key], [RightValue]) VALUES (N'2', N'RightValue_2')

Kartesisches Produkt


Von einem kartesisches Produkt spricht man wenn alle Zeilen der einen Tabelle mit allen
Zeilen der anderen Tabelle verbunden werden, auch bei einem „Self-Join“.
Das kartesische Produkt zweier Mengen ist die Menge aller geordneten Paare von Elementen der beiden Mengen.

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l,
     [dbo].[TableRight] r

The result looks like this:

INNER JOIN


Ein INNER JOIN ist die häufigste Join-Operation in Anwendungen und kann als Standard-join-Typ betrachtet werden.

INNER JOIN erstellt eine neue Ergebnistabelle durch die Kombination von Spaltenwerten der beiden Tabellen (A und B)
auf der Basis des Join-Prädikat. Die Abfrage vergleicht jede Zeile von A mit jeder Reihe von B, um alle Paare von Zeilen,
die das Join-Prädikat erfüllen zu finden. Wenn das Join-Prädikat erfüllt ist, Spaltenwerte werden für jedes passende Paar
der Zeilen von A und B in eine Ergebniszeile kombiniert.

Das Ergebnis der Verbindung kann als das Ergebnis der ersten Einnahme des kartesischen Produkts definiert werden
(oder Cross-Join) aller Datensätze in den Tabellen (die Kombination jedes Datensatz in Tabelle A mit jedem Datensatz in Tabelle B)
-dann alle passenden Datensätze des Verknüpfungsprädikat.

Tatsächliche SQL-Implementierungen verwenden normalerweise andere Ansätze wie ein Hash-Join oder einem Sort-Merge-Join,
wo möglich, da die Berechnung des kartesischen Produkts sehr ineffizient ist.

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l,
     [dbo].[TableRight] r
WHERE l.[Key] = r.[Key]

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
INNER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]

SELECT l.*
FROM [dbo].[TableLeft] l
WHERE l.[Key] = (SELECT [Key]
                 FROM [dbo].[TableRight]
                 WHERE [Key] = l.[Key])

Das Ergebnis sieht dann so aus:

LEFT OUTER JOIN


Das Ergebnis eines LEFT OUTER JOIN (oder einfach LEFT JOIN) von Tabelle A und B enthält immer alle Datensätze
der „linken“ Tabelle (A), auch wenn in der „rechten“ Tabelle kein passender Eintrag gefunden wurde.
Das bedeutet, dass wenn die ON-Klausel 0 (Null) übereinstimmende Datensätze in B findet,
der Join immer noch eine Zeile als Ergebnis zurückgibt, aber mit NULL in jeder Spalte von B.
Dies bedeutet, dass ein LEFT OUTER JOIN alle Werte aus der „linken“ Tabelle sowie übereinstimmende Werte
aus der „rechten“ Tabelle (oder NULL wenn keine Übereinstimmung fegunden wurde) zurück gibt.

Wenn die „rechte“ Tabelle eine Zeile und die „linke“ Tabelle mehr als eine Zeile zurückgibt, werden die Werte
der „rechten“ Tabelle für jede einzelne Zeile der „linken“ Tabelle wiederholt.
Beispielsweise ermöglicht es uns die Abteilung eines Mitarbeiters zu finden, aber dennoch die Mitarbeiter anzuzeigen,
selbst wenn sie nicht in dieser Abteilung arbeiten (im Gegensatz zum obigen INNER JOIN Beispiel,
in dem nicht zugeordnete Mitarbeiter aus dem Ergebnis ausgeschlossen werden).

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
LEFT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]

Das Ergebnis sieht dann so aus:


Hier ein falscher LEFT OUTER JOIN, das Ergebnis ist ein INNER JOIN

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
LEFT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE Right(r.[RightValue], 1) = '1'

Das Ergebnis sieht dann so aus:


Hier wie der richtige LEFT OUTER JOIN aussehen müsste.
Der Schlüssel ist „OR … is null“ bei jeder WHERE Klausel gegen die rechte Tabelle hinzuzufügen

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
LEFT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE (Right(r.[RightValue], 1) = '1'
       OR Right(r.[RightValue], 1) is null)

Das Ergebnis sieht dann so aus:

RIGHT OUTER JOIN


Das Ergebnis eines RIGHT OUTER JOIN (oder einfach RIGHT JOIN) von Tabelle A und B enthält immer alle Datensätze
der „rechten“ Tabelle (B), auch wenn in der „linken“ Tabelle kein passender Eintrag gefunden wurde.
Das bedeutet, dass wenn die ON-Klausel 0 (Null) übereinstimmende Datensätze in A findet,
der Join immer noch eine Zeile als Ergebnis zurückgibt, aber mit NULL in jeder Spalte von A.
Dies bedeutet, dass ein RIGHT OUTER JOIN alle Werte aus der „rechten“ Tabelle sowie übereinstimmende Werte
aus der „linken“ Tabelle (oder NULL wenn keine Übereinstimmung gefunden wurde) zurück gibt.

Wenn die „linke“ Tabelle eine Zeile und die „rechte“ Tabelle mehr als eine Zeile zurückgibt, werden die Werte
der „linken“ Tabelle für jede einzelne Zeile der „rechten“ Tabelle wiederholt.
Beispielsweise ermöglicht es uns die Abteilung eines Mitarbeiters zu finden, aber dennoch die Mitarbeiter anzuzeigen,
selbst wenn sie nicht in dieser Abteilung arbeiten (im Gegensatz zum obigen INNER JOIN Beispiel,
in dem nicht zugeordnete Mitarbeiter aus dem Ergebnis ausgeschlossen werden).

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
RIGHT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]

Das Ergebnis sieht dann so aus:


Hier ein falscher RIGHT OUTER JOIN, das Ergebnis ist ein INNER JOIN

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
RIGHT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE Right(l.[LeftValue], 1) = '2'

Das Ergebnis sieht dann so aus:


Hier wie der richtige RIGHT OUTER JOIN aussehen müsste.
Der Schlüssel ist „OR … is null“ bei jeder WHERE Klausel gegen die linke Tabelle hinzuzufügen

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
RIGHT OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE (Right(l.[LeftValue], 1) = '2'
       OR Right(l.[LeftValue], 1) is null)

Das Ergebnis sieht dann so aus:

FULL OUTER JOIN


Konzeptionell kombiniert ein FULL OUTER JOIN den Effekt der beiden linken und rechten Outer-Joins.
Wo Datensätze in den FULL OUTER verknüpften Tabellen nicht übereinstimmen, wird die Ergebnismenge NULL-Werte für jede Spalte
der Tabelle der eine passende Zeile fehlt anzeigen.
Für jene Datensätze die übereinstimmen wird eine einzelne Zeile in der Ergebnismenge produziert werden (Mit den Feldern beider Tabellen).
Beispielsweise ermöglicht es uns jeden Mitarbeiter der in einer Abteilung arbeitet und jede Abteilung die einen Mitarbeiter hat zu sehen,
jedoch auch jeden Mitarbeiter der in keiner Abteilung arbeitet sowie jede Abeilung ohne Mitarbeiter.

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
FULL OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]

Das Ergebnis sieht dann so aus:


Hier ein falscher FULL OUTER JOIN, das Ergebnis ist ein INNER JOIN

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
FULL OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE Right(l.[LeftValue], 1)  = '2'
AND   Right(r.[RightValue], 1) = '1'

Das Ergebnis sieht dann so aus:

Hier wie der richtige FULL OUTER JOIN aussehen müsste.
Der Schlüssel ist „OR … is null“ bei jeder WHERE Klausel gegen die linke und rechte Tabelle hinzuzufügen

SELECT l.*,
       r.*
FROM [dbo].[TableLeft] l
FULL OUTER JOIN [dbo].[TableRight] r
ON l.[Key] = r.[Key]
WHERE (Right(l.[LeftValue], 1) = '2'
       OR Right(l.[LeftValue], 1) is null)
OR    (Right(r.[RightValue], 1) = '1'
       OR Right(r.[RightValue], 1) is null)

Das Ergebnis sieht dann so aus:

CROSS JOIN

SQL CROSS JOIN returniert jede Zeile der ersten Tabelle kombiniert mit jeder Zeile der zweiten Tabelle.
Das bedeutet dass ein CROSS JOIN eigentlich ein katesisches Produkt ist.
Ein CROSS JOIN kann auf zwei Arten geschrieben werden: Unter Verwendung des JOIN Syntax oder durch Auflistung
der Tabellen in der FROM Klausel durch Komma getrennt ohne WHERE Klausel.

SELECT *
FROM [dbo].[TableLeft],
     [dbo].[TableRight]
     
SELECT *
FROM [dbo].[TableLeft]
CROSS JOIN [dbo].[TableRight]

Das Ergebnis sieht dann so aus:

Test Tabellen wieder löschen

DROP TABLE [dbo].[TableLeft]
DROP TABLE [dbo].[TableRight]