SQL
JOIN

Invoering

Syntaxis

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

Opmerkingen

Fundamentele expliciete inner join

Een eenvoudige join (ook "inner join" genoemd) vraagt gegevens uit twee tabellen, waarvan de relatie is gedefinieerd in een join clausule.

In het volgende voorbeeld worden de voornamen van werknemers (FName) uit de tabel Werknemers en de naam van de afdeling waarvoor ze werken (Naam) uit de tabel Afdelingen geselecteerd:

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

Dit zou het volgende uit de voorbeelddatabase opleveren:

Impliciete deelname

Joins kan ook worden uitgevoerd doordat een aantal tabellen in het from clausule gescheiden door komma's , en het definiëren van de relatie tussen hen in where component. Deze techniek wordt een impliciete join genoemd (omdat deze geen join clausule bevat).

Alle RDBMS's ondersteunen het, maar de syntaxis wordt meestal afgeraden. De redenen waarom het een slecht idee is om deze syntaxis te gebruiken, zijn:

• Het is mogelijk om toevallige kruisverbindingen te krijgen die vervolgens onjuiste resultaten opleveren, vooral als u veel verbindingen in de query hebt.
• Als u een cross-join wilde, is dit niet duidelijk uit de syntaxis (schrijf in plaats daarvan CROSS JOIN) en zal iemand dit waarschijnlijk wijzigen tijdens onderhoud.

In het volgende voorbeeld worden de voornamen van de werknemers en de naam van de afdelingen waarvoor ze werken geselecteerd:

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

Dit zou het volgende uit de voorbeelddatabase opleveren:

Linker buitenaansluiting

Een linker buitenste join (ook bekend als een linker join of buitenste join) is een join die ervoor zorgt dat alle rijen uit de linkertabel worden weergegeven; als er geen overeenkomende rij uit de juiste tabel bestaat, zijn de bijbehorende velden NULL .

Het volgende voorbeeld selecteert alle afdelingen en de voornaam van werknemers die op die afdeling werken. Afdelingen zonder werknemers worden nog steeds weergegeven in de resultaten, maar hebben NULL voor de naam van de werknemer:

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

Dit zou het volgende uit de voorbeelddatabase opleveren:

Dus hoe werkt dit?

Er zijn twee tabellen in de clausule FROM:

en

Eerst wordt een Cartesiaans product gemaakt van de twee tabellen die een tussentabel geven.
De records die aan de join-criteria voldoen ( Departments.Id = Employees.DepartmentId ) zijn vetgedrukt; deze worden doorgegeven aan de volgende fase van de zoekopdracht.

Omdat dit een LINKER BUITENKANT is, worden alle records geretourneerd vanaf de LINKERKANT van de join (afdelingen), terwijl alle records aan de RECHTERKANT een NULL-markering krijgen als ze niet voldoen aan de join-criteria. In de onderstaande tabel retourneert dit Tech met NULL

Uiteindelijk wordt elke uitdrukking die wordt gebruikt in de SELECT- component geëvalueerd om onze definitieve tabel te retourneren:

Zelf meedoen

Een tabel kan met zichzelf worden verbonden, waarbij verschillende rijen door een bepaalde voorwaarde met elkaar overeenkomen. In dit geval moeten aliassen worden gebruikt om de twee exemplaren van de tabel te onderscheiden.

In het onderstaande voorbeeld wordt voor elke werknemer in de voorbeelddatabase Werknemers tabel een record geretourneerd met de voornaam van de werknemer samen met de overeenkomstige voornaam van de manager van de werknemer. Omdat managers ook werknemers zijn, is de tabel met zichzelf verbonden:

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

Deze query retourneert de volgende gegevens:

Dus hoe werkt dit?

De originele tabel bevat deze records:

De eerste actie is om een Cartesiaans product te maken van alle records in de tabellen die worden gebruikt in de clausule FROM . In dit geval is het twee keer de tabel Werknemers, dus de tussentabel ziet er zo uit (ik heb alle velden verwijderd die niet in dit voorbeeld zijn gebruikt):

De volgende actie is om alleen de records te bewaren die voldoen aan de JOIN- criteria, dus alle records waarvan de alias e tabel ManagerId gelijk is aan de alias m tabel Id :

Vervolgens wordt elke expressie die wordt gebruikt in de SELECT- component geëvalueerd om deze tabel te retourneren:

Ten slotte worden kolomnamen e.FName en m.FName vervangen door hun alias kolomnamen, toegewezen aan de AS- operator:

CROSS JOIN

Cross-join doet een Cartesiaans product van de twee leden. Een Cartesiaans product betekent dat elke rij van één tabel wordt gecombineerd met elke rij van de tweede tabel in de join. Als TABLEA bijvoorbeeld 20 rijen heeft en TABLEB 20 rijen, is het resultaat 20*20 = 400 uitvoerrijen.

Met behulp van een voorbeelddatabase

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

Welke retourneert:

Het wordt aanbevolen om een expliciete CROSS JOIN te schrijven als je een cartesiaanse join wilt doen, om te benadrukken dat dit is wat je wilt.

Deelnemen aan een subquery

Deelnemen aan een subquery wordt vaak gebruikt wanneer u verzamelde gegevens uit een onderliggende / detailtabel wilt ophalen en deze samen met records uit de bovenliggende / koptabel wilt weergeven. U wilt bijvoorbeeld een telling van onderliggende records, een gemiddelde van een of andere numerieke kolom in onderliggende records, of de bovenste of onderste rij op basis van een datum of numeriek veld. Dit voorbeeld maakt gebruik van aliassen, waardoor betwistbare vragen gemakkelijker te lezen zijn als er meerdere tabellen bij betrokken zijn. Dit is hoe een vrij typische subquery-join eruit ziet. In dit geval halen we alle rijen uit de inkooporders van de bovenliggende tabel en halen we alleen de eerste rij op voor elk bovenliggend record van de onderliggende tabel PurchaseOrderLineItems.

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 TOEPASSEN & LATERALE JOIN

Een zeer interessant type JOIN is de LATERAL JOIN (nieuw in PostgreSQL 9.3+),
die ook bekend staat als CROSS APPLY / OUTER APPLY in SQL-Server & Oracle.

Het basisidee is dat een functie met tabelwaarde (of inline subquery) wordt toegepast op elke rij waaraan u deelneemt.

Dit maakt het mogelijk om bijvoorbeeld alleen het eerste overeenkomende item in een andere tabel toe te voegen.
Het verschil tussen een normale en een laterale join ligt in het feit dat u een kolom kunt gebruiken die u eerder hebt toegevoegd in de subquery die u "CROSS APPLY" gebruikt.

Syntaxis:

PostgreSQL 9.3+

links | rechts | innerlijke JOIN LATERAL

SQL-Server:

CROSS | BUITEN TOEPASSING

INNER JOIN LATERAL is hetzelfde als CROSS APPLY
en LEFT JOIN LATERAL is hetzelfde als OUTER APPLY

Voorbeeld gebruik (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 

En voor 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 

VOLLEDIG MEE

Een type JOIN dat minder bekend is, is de FULL JOIN.
(Opmerking: FULL JOIN wordt niet ondersteund door MySQL vanaf 2016)

Een VOLLEDIGE BUITENUITSLUITING retourneert alle rijen uit de linker tabel en alle rijen uit de rechter tabel.

Als er rijen in de linker tabel zijn die geen overeenkomsten hebben in de rechter tabel, of als er rijen in de rechter tabel zijn die geen overeenkomsten hebben in de linker tabel, dan zullen die rijen ook worden vermeld.

Voorbeeld 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Voorbeeld 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 

Merk op dat als je soft-deletes gebruikt, je de soft-delete status opnieuw moet controleren in de WHERE-clausule (omdat FULL JOIN zich een beetje als een UNION gedraagt);
Het is gemakkelijk om dit kleine feit over het hoofd te zien, omdat u AP_SoftDeleteStatus = 1 in de join-clausule plaatst.

Als je een VOLLEDIGE JOIN doet, moet je meestal NULL toestaan in de WHERE-clausule; vergeten om NULL toe te staan op een waarde heeft hetzelfde effect als een INNER join, wat je niet wilt als je een FULL JOIN doet.

Voorbeeld:

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) 

Recursieve JOINs

Recursieve joins worden vaak gebruikt om ouder-kind-gegevens te verkrijgen. In SQL worden ze geïmplementeerd met recursieve gemeenschappelijke tabelexpressies , bijvoorbeeld:

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;

Verschillen tussen binnenste / buitenste naden

SQL heeft verschillende join-typen om aan te geven of (niet-) overeenkomende rijen in het resultaat worden opgenomen: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN en FULL OUTER JOIN (de sleutelwoorden INNER en OUTER zijn optioneel). De onderstaande afbeelding onderstreept de verschillen tussen deze typen joins: het blauwe gebied vertegenwoordigt de resultaten die door de join zijn geretourneerd en het witte gebied vertegenwoordigt de resultaten die de join niet zal retourneren.

Cross Join SQL picturale presentatie ( referentie ):

Hieronder staan voorbeelden uit dit antwoord.

Er zijn bijvoorbeeld twee tabellen zoals hieronder:

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

Merk op dat (1,2) uniek zijn voor A, (3,4) gemeenschappelijk zijn en (5,6) uniek zijn voor B.

Innerlijke join

Een inner join met een van de equivalente query's geeft het snijpunt van de twee tabellen, dwz de twee rijen die ze gemeenschappelijk hebben:

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

Linker buitenvoeg

Een linkse buitenste join geeft alle rijen in A, plus alle gemeenschappelijke rijen in B:

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

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

Rechter buiten join

Op dezelfde manier geeft een rechter buitenste join alle rijen in B, plus alle gemeenschappelijke rijen in A:

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

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

Volledige buitenste voeg

Een volledige buitenste join geeft je de unie van A en B, dat wil zeggen alle rijen in A en alle rijen in B. Als iets in A geen overeenkomstige datum in B heeft, is het B-gedeelte nul en vice versa.

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

WORD LID VAN Terminologie: Inner, Outer, Semi, Anti ...

Laten we zeggen dat we twee tabellen (A en B) hebben en dat sommige van hun rijen overeenkomen (ten opzichte van de gegeven JOIN-voorwaarde, wat het ook is in het specifieke geval):

We kunnen verschillende join-typen gebruiken om overeenkomende of niet-overeenkomende rijen aan beide zijden op te nemen of uit te sluiten, en de join correct te benoemen door de overeenkomstige termen in het bovenstaande diagram te kiezen.

De onderstaande voorbeelden gebruiken de volgende testgegevens:

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');

Innerlijke join

Combineert linker- en rechterrijen die overeenkomen.

SELECT * FROM A JOIN B ON X = Y;

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

Linker buitenaansluiting

Soms afgekort tot "left join". Combineert linker- en rechterrijen die overeenkomen en omvat niet-overeenkomende linkerrijen.

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

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

Right Outer Join

Soms afgekort tot "right join". Combineert linker- en rechterrijen die overeenkomen en omvat niet-overeenkomende rechterrijen.

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

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

Volledige deelname aan de buitenwereld

Soms afgekort tot "full join". Unie van linker en rechter buitenste join.

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

Links Semi Join

Bevat linkerrijen die overeenkomen met rechterrijen.

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

X
-----
Lisa
Marco
Phil

Rechts Semi meedoen

Bevat rechterrijen die overeenkomen met linkerrijen.

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

Y
-----
Lisa
Marco
Phil

Zoals u kunt zien, is er geen speciale IN-syntaxis voor semi-links naar rechts versus rechts - we bereiken het effect door eenvoudigweg de tabelposities in SQL-tekst te wijzigen.

Links Anti Semi Join

Bevat linkerrijen die niet overeenkomen met rechterrijen.

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

X
----
Amy
John

WAARSCHUWING: wees voorzichtig als u NIET IN op een NULL-kolom gebruikt! Meer details hier .

Rechts Anti Semi Join

Bevat rechterrijen die niet overeenkomen met linkerrijen.

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

Y
-------
Tim
Vincent

Zoals u kunt zien, is er geen speciale NOT IN-syntaxis voor links versus rechts anti semi-join - we bereiken het effect door eenvoudigweg de tabelposities in SQL-tekst te wijzigen.

Cross Join

Een Cartesiaans product van links met alle juiste rijen.

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 is gelijk aan een voorwaarde voor inner join met join die altijd overeenkomt, dus de volgende query zou hetzelfde resultaat hebben opgeleverd:

SELECT * FROM A JOIN B ON 1 = 1;

Self-Join

Dit geeft eenvoudig een tabel aan die met zichzelf samengaat. Een self-join kan elk van de hierboven besproken join-typen zijn. Dit is bijvoorbeeld een innerlijke zelf-join:

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