SQL
BEITRETEN

Einführung

Syntax

• [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } ] JOIN

Bemerkungen

Grundlegende explizite innere Verknüpfung

Ein Basis-Join (auch "Inner Join" genannt) fragt Daten aus zwei Tabellen ab, deren Beziehung in einer join Klausel definiert ist.

Im folgenden Beispiel werden die Vornamen der Mitarbeiter (FName) aus der Tabelle Employees und der Name der Abteilung, für die sie arbeiten (Name), aus der Tabelle Departments ausgewählt:

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

Dies würde Folgendes aus der Beispieldatenbank zurückgeben :

Implizite Verknüpfung

Joins können auch ausgeführt werden, indem mehrere Tabellen in der from Klausel mit Kommas getrennt , und die Beziehung zwischen ihnen in der where Klausel definiert wird. Diese Technik wird als impliziter Join bezeichnet (da sie eigentlich keine join Klausel enthält).

Alle RDBMSs unterstützen dies, von der Syntax wird jedoch normalerweise abgeraten. Die Gründe, warum es eine schlechte Idee ist, diese Syntax zu verwenden, sind:

• Es ist möglich, versehentliche Cross-Joins zu erhalten, die dann falsche Ergebnisse liefern, insbesondere wenn Sie viele Joins in der Abfrage haben.
• Wenn Sie eine Kreuzverknüpfung beabsichtigen, ist dies aus der Syntax nicht ersichtlich (schreiben Sie stattdessen CROSS JOIN aus), und jemand wird sie wahrscheinlich während der Wartung ändern.

Im folgenden Beispiel werden die Vornamen der Mitarbeiter und der Name der Abteilungen ausgewählt, für die sie tätig sind:

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

Dies würde Folgendes aus der Beispieldatenbank zurückgeben :

Linke äußere Verbindung

Ein Left Outer Join (auch als Left Join oder Outer Join bezeichnet) ist ein Join, der sicherstellt, dass alle Zeilen der linken Tabelle dargestellt werden. Wenn keine übereinstimmende Zeile aus der rechten Tabelle vorhanden ist, sind die entsprechenden Felder NULL .

Im folgenden Beispiel werden alle Abteilungen und der Vorname der Mitarbeiter ausgewählt, die in dieser Abteilung arbeiten. Abteilungen ohne Mitarbeiter werden zwar in den Ergebnissen zurückgegeben, haben jedoch NULL für den Mitarbeiternamen:

SELECT          Departments.Name, Employees.FName
FROM            Departments 
LEFT OUTER JOIN Employees 
ON              Departments.Id = Employees.DepartmentId

Dies würde Folgendes aus der Beispieldatenbank zurückgeben :

Wie funktioniert das?

Die FROM-Klausel enthält zwei Tabellen:

und

Zunächst wird ein kartesisches Produkt aus den beiden Tabellen erstellt, die eine Zwischentabelle enthalten.
Die Datensätze, die die Join-Kriterien erfüllen ( Departments.Id = Employees.DepartmentId ), sind fett hervorgehoben. Diese werden an die nächste Stufe der Abfrage übergeben.

Da dies ein LEFT-OUTER-JOIN ist, werden alle Datensätze von der LEFT-Seite des Joins (Abteilungen) zurückgegeben, während alle Datensätze auf der RECHTEN Seite eine NULL-Markierung erhalten, wenn sie nicht den Join-Kriterien entsprechen. In der folgenden Tabelle wird Tech mit NULL

Schließlich wird jeder Ausdruck, der in der SELECT- Klausel verwendet wird, ausgewertet, um unsere letzte Tabelle zurückzugeben:

Selbst beitreten

Eine Tabelle kann mit sich selbst verbunden sein, wobei verschiedene Zeilen durch eine Bedingung miteinander übereinstimmen. In diesem Anwendungsfall müssen Aliase verwendet werden, um die beiden Vorkommen der Tabelle zu unterscheiden.

Im folgenden Beispiel wird für jeden Mitarbeiter in der Tabelle Employees der Beispieldatenbank ein Datensatz zurückgegeben, der den Vornamen des Mitarbeiters sowie den entsprechenden Vornamen des Vorgesetzten des Mitarbeiters enthält. Da Manager auch Angestellte sind, ist der Tisch mit sich selbst verbunden:

SELECT 
    e.FName AS "Employee", 
    m.FName AS "Manager"
FROM   
    Employees e
JOIN   
    Employees m 
    ON e.ManagerId = m.Id

Diese Abfrage gibt die folgenden Daten zurück:

Wie funktioniert das?

Die Originaltabelle enthält diese Datensätze:

Die erste Aktion besteht darin, ein kartesisches Produkt aus allen Datensätzen in den in der FROM- Klausel verwendeten Tabellen zu erstellen. In diesem Fall handelt es sich zweimal um die Employees-Tabelle, daher sieht die Zwischentabelle folgendermaßen aus (ich habe alle in diesem Beispiel nicht verwendeten Felder entfernt):

Die nächste Aktion ist es , nur die Aufzeichnungen, die die JOIN Kriterien erfüllen, so dass alle Datensätze , in denen die aliased e Tabelle ManagerId die aliased gleich m Tabelle Id :

Dann wird jeder in der SELECT- Klausel verwendete Ausdruck ausgewertet, um diese Tabelle zurückzugeben:

Schließlich werden die Spaltennamen e.FName und m.FName durch ihre Alias-Spaltennamen ersetzt, die dem AS- Operator zugeordnet sind:

CROSS JOIN

Cross Joining führt ein kartesisches Produkt der beiden Elemente aus. Ein kartesisches Produkt bedeutet, dass jede Zeile einer Tabelle mit jeder Zeile der zweiten Tabelle im Join kombiniert wird. Wenn TABLEA beispielsweise 20 Zeilen und TABLEB 20 Zeilen hat, wäre das Ergebnis 20*20 = 400 Ausgabezeilen.

Beispieldatenbank verwenden

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

Welche gibt zurück:

Es wird empfohlen, einen expliziten CROSS JOIN zu schreiben, wenn Sie einen kartesischen Join durchführen möchten, um hervorzuheben, dass dies der Fall ist.

An einer Unterabfrage teilnehmen

Das Verknüpfen einer Unterabfrage wird häufig verwendet, wenn Sie aggregierte Daten aus einer untergeordneten / Detailtabelle abrufen und diese zusammen mit Datensätzen aus der übergeordneten / Header-Tabelle anzeigen möchten. Sie möchten beispielsweise eine Anzahl von untergeordneten Datensätzen, einen Durchschnitt einer numerischen Spalte in untergeordneten Datensätzen oder die obere oder untere Zeile basierend auf einem Datums- oder numerischen Feld abrufen. In diesem Beispiel werden Aliase verwendet, die die Lesbarkeit von Abfragen erleichtern, wenn mehrere Tabellen beteiligt sind. So sieht eine recht typische Unterabfrage aus. In diesem Fall rufen wir alle Zeilen aus den Bestellungen der übergeordneten Tabelle und nur die erste Zeile für jeden übergeordneten Datensatz der untergeordneten Tabelle PurchaseOrderLineItems ab.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo, 
  item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN 
     (
       SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id 
       FROM PurchaseOrderLineItems l
       GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
     ) AS item ON item.PurchaseOrderId = po.Id

CROSS APPLY & LATERAL JOIN

Eine sehr interessante Art von JOIN ist der LATERAL JOIN (neu in PostgreSQL 9.3+).
Dies wird in SQL-Server & Oracle auch als CROSS APPLY / OUTER APPLY bezeichnet.

Die Grundidee ist, dass eine Tabellenwertfunktion (oder Inline-Unterabfrage) für jede Zeile angewendet wird, der Sie beitreten.

Dadurch ist es beispielsweise möglich, nur den ersten passenden Eintrag in einer anderen Tabelle zu verknüpfen.
Der Unterschied zwischen einem normalen und einem seitlichen Join besteht darin, dass Sie eine Spalte verwenden können, die Sie zuvor in der Unterabfrage "CROSS APPLY" hinzugefügt haben.

Syntax:

PostgreSQL 9.3 und höher

links | richtig | inneres VERBINDEN LATERAL

SQL Server:

CROSS | Äußere Anwendung

INNER JOIN LATERAL ist das gleiche wie CROSS APPLY
und LEFT JOIN LATERAL ist das Gleiche wie OUTER APPLY

Verwendungsbeispiel (PostgreSQL 9.3+):

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989


LEFT JOIN LATERAL 
(
    SELECT 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
   LIMIT 1 
) AS FirstOE 

Und für SQL-Server

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989

-- CROSS APPLY -- = INNER JOIN 
OUTER APPLY    -- = LEFT JOIN 
(
    SELECT TOP 1 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
) AS FirstOE 

FULL JOIN

Ein weniger bekannter JOIN-Typ ist der FULL JOIN.
(Hinweis: FULL JOIN wird ab 2016 nicht von MySQL unterstützt.)

Ein FULL OUTER JOIN gibt alle Zeilen der linken Tabelle und alle Zeilen der rechten Tabelle zurück.

Wenn in der linken Tabelle Zeilen vorhanden sind, für die in der rechten Tabelle keine Übereinstimmungen vorhanden sind, oder wenn in der rechten Tabelle Zeilen vorhanden sind, die in der linken Tabelle keine Übereinstimmungen aufweisen, werden diese Zeilen ebenfalls aufgelistet.

Beispiel 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Beispiel 2

SELECT 
     COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear 
    ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value 
FROM T_Budget 

FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear 
      ON tYear.Year = T_Budget.Year 

Wenn Sie Soft-Deletes verwenden, müssen Sie den Soft-Delete-Status erneut in der WHERE-Klausel überprüfen (da FULL JOIN sich wie eine UNION verhält).
Es ist leicht, diese kleine Tatsache zu übersehen, da Sie AP_SoftDeleteStatus = 1 in die Join-Klausel einfügen.

Wenn Sie einen FULL JOIN ausführen, müssen Sie normalerweise NULL in der WHERE-Klausel zulassen. Wenn Sie vergessen, NULL für einen Wert zuzulassen, hat dies die gleichen Auswirkungen wie bei einem INNER-Join. Dies ist etwas, was Sie nicht möchten, wenn Sie einen FULL JOIN ausführen.

Beispiel:

SELECT 
     T_AccountPlan.AP_UID
    ,T_AccountPlan.AP_Code
    ,T_AccountPlan.AP_Lang_EN
    ,T_BudgetPositions.BUP_Budget
    ,T_BudgetPositions.BUP_UID 
    ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    

FULL JOIN T_AccountPlan
    ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID 
    AND T_AccountPlan.AP_SoftDeleteStatus = 1 

WHERE (1=1) 
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL) 
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL) 

Rekursive JOINs

Rekursive Joins werden häufig verwendet, um Eltern-Kind-Daten zu erhalten. In SQL werden sie mit rekursiven allgemeinen Tabellenausdrücken implementiert, zum Beispiel:

WITH RECURSIVE MyDescendants AS (
    SELECT Name
    FROM People
    WHERE Name = 'John Doe'

    UNION ALL

    SELECT People.Name
    FROM People
    JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

Unterschiede zwischen inneren und äußeren Verbindungen

SQL bietet verschiedene Join-Typen, um anzugeben, ob (nicht) übereinstimmende Zeilen im Ergebnis enthalten sind: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN und FULL OUTER JOIN (die Schlüsselwörter INNER und OUTER sind optional). Die folgende Abbildung unterstreicht die Unterschiede zwischen diesen Arten von Joins: Der blaue Bereich stellt die Ergebnisse dar, die vom Join zurückgegeben werden, und der weiße Bereich repräsentiert die Ergebnisse, die der Join nicht zurückgibt.

Cross Join-SQL-Bilddarstellung ( Referenz ):

Nachfolgend finden Sie Beispiele aus dieser Antwort.

Zum Beispiel gibt es zwei Tabellen wie folgt:

A    B
-    -
1    3
2    4
3    5
4    6

Beachten Sie, dass (1,2) für A eindeutig sind, (3,4) gemeinsam sind und (5,6) für B eindeutig sind.

Inner Join

Ein innerer Join, der eine der äquivalenten Abfragen verwendet, gibt den Schnittpunkt der beiden Tabellen an, dh die beiden Zeilen, die sie gemeinsam haben:

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Linke äußere Verbindung

Ein linker äußerer Join ergibt alle Zeilen in A und alle üblichen Zeilen in B:

select * from a LEFT OUTER JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Rechte äußere Verbindung

In ähnlicher Weise werden bei einem Right-Outer-Join alle Zeilen in B plus alle üblichen Zeilen in A angezeigt:

select * from a RIGHT OUTER JOIN b on a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Volle äußere Verbindung

Eine vollständige äußere Verknüpfung gibt Ihnen die Vereinigung von A und B, dh alle Zeilen in A und alle Zeilen in B. Wenn etwas in A kein entsprechendes Datum in B hat, ist der B-Teil null und und umgekehrt.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

JOIN Terminologie: Inner, Äußer, Halb, Anti ...

Nehmen wir an, wir haben zwei Tabellen (A und B), und einige ihrer Zeilen stimmen überein (relativ zu der angegebenen JOIN-Bedingung, was auch immer im Einzelfall ist):

Wir können verschiedene Verbindungstypen verwenden, um übereinstimmende oder nicht übereinstimmende Zeilen auf beiden Seiten ein- oder auszuschließen und den Join korrekt zu benennen, indem Sie die entsprechenden Begriffe aus dem obigen Diagramm auswählen.

Die folgenden Beispiele verwenden die folgenden Testdaten:

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);

CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);

INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');

INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

Inner Join

Kombiniert die übereinstimmenden linken und rechten Reihen.

SELECT * FROM A JOIN B ON X = Y;

X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

Linke äußere Verbindung

Manchmal abgekürzt als "Left Join". Kombiniert die übereinstimmenden linken und rechten Zeilen und enthält nicht übereinstimmende linke Zeilen.

SELECT * FROM A LEFT JOIN B ON X = Y;

X      Y
-----  -----
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil

Rechter äußerer Join

Manchmal abgekürzt als "Right Join". Kombiniert die übereinstimmenden linken und rechten Zeilen und enthält nicht übereinstimmende rechte Zeilen.

SELECT * FROM A RIGHT JOIN B ON X = Y;

X      Y
-----  -------
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Voller äußerer Join

Manchmal abgekürzt als "full join". Vereinigung der linken und rechten äußeren Verbindung.

SELECT * FROM A FULL JOIN B ON X = Y;

X      Y
-----  -------
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Left Semi Join

Schließt linke Reihen ein, die mit rechten Reihen übereinstimmen.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);

X
-----
Lisa
Marco
Phil

Right Semi Join

Enthält rechte Zeilen, die den linken Zeilen entsprechen.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);

Y
-----
Lisa
Marco
Phil

Wie Sie sehen, gibt es keine dedizierte IN-Syntax für Links- / Rechts-Semi-Join. Wir erzielen den Effekt, indem Sie einfach die Tabellenpositionen im SQL-Text ändern.

Linke Anti-Semi-Verbindung

Enthält linke Zeilen, die nicht mit rechten Zeilen übereinstimmen.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);

X
----
Amy
John

WARNUNG: Seien Sie vorsichtig, wenn Sie NOT IN in einer NULL-fähigen Spalte verwenden! Weitere Details hier .

Right Anti Semi Join

Enthält rechte Zeilen, die nicht mit den linken Zeilen übereinstimmen.

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);

Y
-------
Tim
Vincent

Wie Sie sehen, gibt es keine dedizierte NOT-IN-Syntax für einen Links-gegen-Rechts-Anti-Semi-Join - wir erzielen den Effekt einfach durch das Wechseln der Tabellenpositionen im SQL-Text.

Cross Join

Ein kartesisches Produkt mit allen rechten Reihen.

SELECT * FROM A CROSS JOIN B;

X      Y
-----  -------
Amy    Lisa
John   Lisa
Lisa   Lisa
Marco  Lisa
Phil   Lisa
Amy    Marco
John   Marco
Lisa   Marco
Marco  Marco
Phil   Marco
Amy    Phil
John   Phil
Lisa   Phil
Marco  Phil
Phil   Phil
Amy    Tim
John   Tim
Lisa   Tim
Marco  Tim
Phil   Tim
Amy    Vincent
John   Vincent
Lisa   Vincent
Marco  Vincent
Phil   Vincent

Cross Join ist gleichbedeutend mit einem inneren Join mit Join-Bedingung, die immer übereinstimmt. Daher hätte die folgende Abfrage dasselbe Ergebnis zurückgegeben:

SELECT * FROM A JOIN B ON 1 = 1;

Self-Join

Dies bezeichnet einfach eine Tabelle, die sich mit sich selbst verbindet. Ein Self-Join kann jeder der oben diskutierten Join-Typen sein. Dies ist zum Beispiel eine innere Selbstverbindung:

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);

X     X
----  -----
Amy   John
Amy   Lisa
Amy   Marco
John  Marco
Lisa  Marco
Phil  Marco
Amy   Phil