SQL
ANSLUTA SIG

Introduktion

Syntax

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

Anmärkningar

Grundläggande uttrycklig inre koppling

En grundläggande sammanfogning (även kallad "inre sammanfogning") frågar data från två tabeller, där deras relation definieras i en join .

Följande exempel kommer att välja anställdas förnamn (FName) från tabellen Anställda och namnet på den avdelning som de arbetar för (Namn) från tabellen Avdelningar:

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

Detta skulle returnera följande från exempeldatabasen :

Implicit Gå med

Sammanfogningar kan också utföras genom att ha flera tabeller i from klausulen, separerade med komma , och definiera förhållandet mellan dem i where klausulen. Den här tekniken kallas en Implicit Join (eftersom den faktiskt inte innehåller en join klausul).

Alla RDBMS stöder det, men syntaxen rekommenderas vanligtvis. Anledningarna till att det är en dålig idé att använda denna syntax är:

• Det är möjligt att få oavsiktliga korsfogar som sedan ger felaktiga resultat, speciellt om du har många kopplingar i frågan.
• Om du tänkte ett korsförband, är det inte klart från syntaxen (skriv ut CROSS JOIN istället), och någon kommer förmodligen att ändra det under underhåll.

Följande exempel kommer att välja anställdas förnamn och namnet på de avdelningar de arbetar för:

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

Detta skulle returnera följande från exempeldatabasen :

Vänster yttre anslutning

En vänster yttre koppling (även känd som en vänster koppling eller yttre koppling) är en koppling som säkerställer att alla rader från vänsterbordet är representerade; om det inte finns någon matchande rad från den högra tabellen är motsvarande fält NULL .

Följande exempel kommer att välja alla avdelningar och förnamnet på anställda som arbetar i den avdelningen. Avdelningar utan anställda returneras fortfarande i resultaten, men har NULL för anställdens namn:

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

Detta skulle returnera följande från exempeldatabasen :

Så hur fungerar det här?

Det finns två tabeller i FROM-klausulen:

och

Först skapas en kartesisk produkt från de två tabellerna som ger en mellanliggande tabell.
Posterna som uppfyller anslutningskriterierna ( avdelningar.Id = Anställda.AvdelningId ) markeras med fet stil; dessa överförs till nästa steg i frågan.

Eftersom detta är ett VÄNSTERFÖRGÅRD kommer alla poster att returneras från vänster sida av sammanfogningen (avdelningar), medan alla poster på HÖGRE sidan ges en NULL-markör om de inte stämmer överens med kriterierna. I tabellen nedan kommer detta att returnera Tech med NULL

Slutligen utvärderas varje uttryck som används inom SELECT- klausulen för att returnera vår sista tabell:

Självmedlem

En tabell kan kopplas till sig själv, med olika rader som matchar varandra av något skick. I detta fall måste alias användas för att skilja de två förekomsten av tabellen.

I exemplet nedan, för varje anställd i tabellen Exempel på databasanställda , returneras en post som innehåller den anställdes förnamn tillsammans med motsvarande förnamn på den anställdes chef. Eftersom chefer också är anställda förenas tabellen med sig själv:

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

Denna fråga returnerar följande data:

Så hur fungerar det här?

Den ursprungliga tabellen innehåller dessa poster:

Den första åtgärden är att skapa en kartesisk produkt av alla poster i tabellerna som används i FROM- klausulen. I det här fallet är det tabellen Anställda två gånger, så mellanbordet kommer att se ut så här (jag har tagit bort alla fält som inte används i detta exempel):

Nästa åtgärd är att bara behålla poster som uppfyller JOIN- kriterierna, så alla poster där den aliasade e tabellen ManagerId lika med den aliasade m tabellen Id :

Därefter utvärderas varje uttryck som används i SELECT- klausulen för att returnera denna tabell:

Slutligen e.FName m.FName e.FName och m.FName med deras alias kolumnnamn, tilldelade AS- operatören:

KRÄSS GÅ MED

Cross join gör en kartesisk produkt av de två medlemmarna, en kartesisk produkt betyder att varje rad i ett bord kombineras med varje rad i det andra bordet i skarven. Till exempel, om TABLEA har 20 rader och TABLEB har 20 rader, skulle resultatet vara 20*20 = 400 utgångsrader.

Med hjälp av exempeldatabas

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

Som returnerar:

Vi rekommenderar att du skriver ett tydligt CROSS JOIN om du vill göra en kartesisk anslutning, för att markera att det är detta du vill.

Gå med på en undersökning

Att gå med i en subfråga används ofta när du vill hämta sammanlagda data från en tabell under / information och visa det tillsammans med poster från över- / huvudtabellen. Till exempel kanske du vill få ett antal barnposter, ett genomsnitt av en numerisk kolumn i underordnade poster, eller den övre eller nedre raden baserat på ett datum eller ett numeriskt fält. Det här exemplet använder alias, vilket kan diskuteras gör frågor lättare att läsa när du har flera tabeller involverade. Så här ser en ganska typisk undersökning ut. I det här fallet hämtar vi alla rader från föräldstabellen Inköpsorder och hämtar endast den första raden för varje förälderrekord i underordnatabellen InköpOrderLineItems.

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

KRÄSSA ANVÄNDA & SIDAN GÅ MED

En mycket intressant typ av JOIN är LATERAL JOIN (ny i PostgreSQL 9.3+),
vilket också kallas CROSS APPLY / OUTER APPLY i SQL-Server & Oracle.

Den grundläggande idén är att en tabellvärderad funktion (eller inline subquery) tillämpas för varje rad du går med.

Detta gör det möjligt att till exempel bara gå med i den första matchande posten i en annan tabell.
Skillnaden mellan en normal och en lateral sammanfogning ligger i det faktum att du kan använda en kolumn som du tidigare anslutit dig till i den undersökning som du "CROSS APPLY".

Syntax:

PostgreSQL 9.3+

vänster | rätt | inner JOIN LATERAL

SQL-Server:

KROSS | YTRE ANVÄNDNING

INNER JOIN LATERAL är samma som CROSS APPLY
och LEFT JOIN LATERAL är samma som OUTER APPLY

Exempel på användning (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 

Och 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 

FULLT GÅ MED

En typ av JOIN som är mindre känd är FULL JOIN.
(Obs: FULL JOIN stöds inte av MySQL per 2016)

A FULL OUTER JOIN returnerar alla rader från det vänstra bordet och alla rader från det högra bordet.

Om det finns rader i den vänstra tabellen som inte har matchningar i den högra tabellen, eller om det finns rader i den högra tabellen som inte har matchningar i den vänstra tabellen, kommer de raderna också att listas.

Exempel 1:

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Exempel 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 

Observera att om du använder soft-deletes måste du kontrollera statusen för soft-delete igen i WHERE-klausulen (eftersom FULL JOIN uppför sig som en UNION);
Det är lätt att förbise detta lilla faktum, eftersom du sätter AP_SoftDeleteStatus = 1 i kopplingsklausulen.

Om du gör en FULL JOIN måste du också tillåta NULL i WHERE-klausulen; att glömma att tillåta NULL på ett värde kommer att ha samma effekter som en INNER-anslutning, vilket är något du inte vill ha om du gör ett FULL JOIN.

Exempel:

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) 

Rekursiva JOINs

Rekursiva förbindelser används ofta för att få information om föräldrar och barn. I SQL implementeras de med rekursiva vanliga tabelluttryck , till exempel:

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;

Skillnader mellan inre / yttre förbindelser

SQL har olika sammanfogningstyper för att specificera om (icke-) matchande rader ingår i resultatet: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN och FULL OUTER JOIN ( INNER och OUTER är valfria). Figuren nedan understryker skillnaderna mellan dessa typer av sammanfogningar: det blå området representerar de resultat som returneras av sammanfogningen, och det vita området representerar de resultat som sammanfogningen inte kommer att returnera.

Cross Join SQL Bildpresentation ( referens ):

Nedan följer exempel från detta svar.

Till exempel finns det två tabeller enligt nedan:

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

Observera att (1,2) är unika för A, (3,4) är vanliga och (5,6) är unika för B.

Inre koppling

En inre koppling med någon av motsvarande frågor ger skärningspunkten mellan de två tabellerna, dvs. de två raderna de har gemensamt:

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

Vänster yttre sammanfogning

En vänster ytterkoppling ger alla rader i A, plus alla vanliga rader i B:

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

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

Höger yttre koppling

På liknande sätt ger en höger yttre koppling alla rader i B, plus alla vanliga rader i A:

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

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

Hel yttre koppling

En full yttre förbindning ger dig föreningen mellan A och B, dvs alla raderna i A och alla raderna i B. Om något i A inte har ett motsvarande datum i B, är B-delen noll, och 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

JOIN Terminology: Inner, Yuter, Semi, Anti ...

Låt oss säga att vi har två tabeller (A och B) och några av deras rader matchar (relativt det givna JOIN-tillståndet, oavsett vad det kan vara i det specifika fallet):

Vi kan använda olika sammanfogningstyper för att inkludera eller utesluta matchande eller icke-matchande rader från endera sidan och korrekt namnge kopplingen genom att välja motsvarande termer från diagrammet ovan.

Exemplen nedan använder följande testdata:

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

Inre koppling

Kombinerar vänster och höger rader som matchar.

SELECT * FROM A JOIN B ON X = Y;

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

Vänster yttre anslutning

Ibland förkortat till "vänster gå". Kombinerar vänster- och högerrader som matchar och inkluderar icke-matchande vänstra rader.

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

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

Right Yuter Join

Ibland förkortat till "högerkoppling". Kombinerar vänster- och högerrader som matchar och inkluderar högerrader som inte matchar.

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

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

Full Yuter Join

Ibland förkortat till "full join". Förening av vänster och höger yttre sammanfogning.

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

Vänster Semi Join

Inkluderar vänstra rader som matchar högerrader.

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

X
-----
Lisa
Marco
Phil

Right Semi Join

Inkluderar högerrader som matchar vänstra rader.

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

Y
-----
Lisa
Marco
Phil

Som ni ser finns det ingen dedicerad IN-syntax för vänster kontra höger semi-join - vi uppnår effekten helt enkelt genom att byta tabellpositioner inom SQL-text.

Vänster Anti Semi Join

Inkluderar vänstra rader som inte matchar högerrader.

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

X
----
Amy
John

VARNING: Var försiktig om du råkar använda INTE i en NULL-kapabel kolumn! Mer information här .

Right Anti Semi Join

Inkluderar högerrader som inte matchar vänstra rader.

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

Y
-------
Tim
Vincent

Som ni ser finns det ingen dedicerad INTE-syntax för vänster kontra höger anti semi-join - vi uppnår effekten helt enkelt genom att byta tabellpositioner inom SQL-text.

Cross Join

En kartesisk produkt från alla vänster med alla högra rader.

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

Tvärkoppling motsvarar ett inre skarv med kopplingsvillkor som alltid matchar, så följande fråga skulle ha returnerat samma resultat:

SELECT * FROM A JOIN B ON 1 = 1;

Själv Gå

Detta anger helt enkelt en tabell som går med sig själv. En självanslutning kan vara vilken som helst av de sammanslagningstyper som diskuteras ovan. Till exempel är detta en inre självförening:

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