SQL
PRZYSTĄP

Wprowadzenie

Składnia

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

Uwagi

Podstawowe wyraźne sprzężenie wewnętrzne

Podstawowe sprzężenie (zwane także „łączeniem wewnętrznym”) odpytuje dane z dwóch tabel, a ich relacje są zdefiniowane w klauzuli join .

Poniższy przykład wybierze imiona pracowników (FName) z tabeli Pracownicy i nazwę działu, dla którego pracują (Nazwa) z tabeli Departments:

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

Zwróci to następujące dane z przykładowej bazy danych :

Dołączanie niejawne

Sprzężenia można również przeprowadzić poprzez liczne tabele w from klauzulą, oddzielone przecinkami , oraz określenia relacji między nimi w where punktu. Ta technika nazywa się join niejawnym (ponieważ tak naprawdę nie zawiera klauzuli join ).

Wszystkie RDBMS to obsługują, ale zwykle odradza się składnię. Powodem, dla którego stosowanie tej składni jest kiepskie, są:

• Możliwe jest uzyskanie przypadkowych sprzężeń krzyżowych, które następnie zwracają niepoprawne wyniki, zwłaszcza jeśli w zapytaniu jest wiele sprzężeń.
• Jeśli zamierzałeś połączyć krzyżowo, to nie jest jasne w składni (zamiast tego wypisz CROSS JOIN), a ktoś prawdopodobnie zmieni to podczas konserwacji.

Poniższy przykład wybierze imiona pracowników i nazwy działów, w których pracują:

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

Zwróci to następujące dane z przykładowej bazy danych :

Lewy zewnętrzny łącznik

Łączenie lewe zewnętrzne (znane również jako łączenie lewe lub łączenie zewnętrzne) to łączenie, które zapewnia reprezentację wszystkich wierszy z lewej tabeli; jeśli nie istnieje pasujący wiersz z właściwej tabeli, odpowiadające mu pola mają NULL .

Poniższy przykład wybierze wszystkie działy i imię pracowników, którzy pracują w tym dziale. Działy bez pracowników są nadal zwracane w wynikach, ale będą mieć NULL dla nazwiska pracownika:

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

Zwróci to następujące dane z przykładowej bazy danych :

Jak to działa?

W klauzuli FROM znajdują się dwie tabele:

i

Najpierw z dwóch tabel tworzony jest produkt kartezjański, który daje tabelę pośrednią.
Rekordy spełniające kryteria łączenia ( Departments.Id = Employees.DepartmentId ) są wyróżnione pogrubioną czcionką; są one przekazywane do następnego etapu zapytania.

Ponieważ jest to LEWE DOŁĄCZENIE ZEWNĘTRZNE, wszystkie rekordy są zwracane z LEWEJ strony złączenia (Działy), natomiast wszelkie rekordy po prawej stronie otrzymują NULL znacznik, jeśli nie spełniają kryteriów łączenia. W poniższej tabeli zwróci Tech z NULL

Na koniec każde wyrażenie użyte w klauzuli SELECT jest oceniane w celu zwrócenia naszej końcowej tabeli:

Self Join

Tabela może być połączona ze sobą, z różnymi wierszami dopasowanymi do siebie pod pewnymi warunkami. W tym przypadku użycia należy użyć aliasów, aby rozróżnić dwa wystąpienia tabeli.

W poniższym przykładzie dla każdego pracownika w przykładowej tabeli Pracownicy bazy danych zwracany jest rekord zawierający imię pracownika wraz z odpowiadającym mu imieniem menedżera pracownika. Ponieważ menedżerowie są również pracownikami, tabela jest łączona ze sobą:

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

To zapytanie zwróci następujące dane:

Jak to działa?

Oryginalna tabela zawiera następujące rekordy:

Pierwszym działaniem jest utworzenie iloczynu kartezjańskiego wszystkich rekordów w tabelach używanych w klauzuli FROM . W tym przypadku jest to tabela Pracownicy dwa razy, więc tabela pośrednia będzie wyglądać następująco (usunąłem wszystkie pola nieużywane w tym przykładzie):

Następną czynnością jest przechowywanie tylko rekordów spełniających kryteria JOIN , więc wszelkie rekordy, w których aliasowany e table ManagerId jest równy aliasowanemu Id m table:

Następnie każde wyrażenie użyte w klauzuli SELECT jest oceniane w celu zwrócenia tej tabeli:

Na koniec nazwy kolumn e.FName i m.FName są zastępowane ich m.FName nazwami kolumn przypisanymi przez operatora AS :

KRZYŻ DOŁĄCZ

Łączenie krzyżowe tworzy iloczyn kartezjański dwóch członków, iloczyn kartezjański oznacza, że każdy rząd jednego stołu jest łączony z każdym rzędem drugiego stołu w złączeniu. Na przykład, jeśli TABLEA ma 20 wierszy, a TABLEB ma 20 wierszy, wynikiem będzie 20*20 = 400 wierszy wyjściowych.

Przy użyciu przykładowej bazy danych

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

Które zwraca:

Zaleca się napisanie wyraźnego DOŁĄCZANIA KRZYŻOWEGO, jeśli chcesz wykonać połączenie kartezjańskie, aby podkreślić, że właśnie tego chcesz.

Dołączanie do podzapytania

Dołączenie do podzapytania jest często używane, gdy chcesz uzyskać agregowane dane z tabeli potomnej / tabeli szczegółów i wyświetlić ją wraz z rekordami z tabeli nadrzędnej / nagłówka. Na przykład możesz chcieć uzyskać liczbę rekordów podrzędnych, średnią z niektórych kolumn numerycznych w rekordach podrzędnych lub górny lub dolny wiersz na podstawie daty lub pola liczbowego. W tym przykładzie użyto aliasów, co do których można argumentować, że zapytania są łatwiejsze do odczytania, gdy w grę wchodzi wiele tabel. Oto jak wygląda dość typowe połączenie podzapytania. W tym przypadku pobieramy wszystkie wiersze z tabeli nadrzędnej Zamówienia zakupu i pobieramy tylko pierwszy wiersz dla każdego rekordu nadrzędnego tabeli podrzędnej 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

KRZYŻ ZASTOSUJ I DOŁĄCZ BOCZNE

Bardzo interesującym rodzajem JOIN jest LATERAL JOIN (nowość w PostgreSQL 9.3+),
który jest również znany jako CROSS APPLY / OUTER APPLY w SQL-Server i Oracle.

Podstawową ideą jest to, że funkcja wartościowana w tabeli (lub wbudowane podzapytanie) jest stosowana do każdego przyłączanego wiersza.

Umożliwia to na przykład dołączenie tylko pierwszego pasującego wpisu w innej tabeli.
Różnica między łączeniem normalnym a bocznym polega na tym, że możesz użyć kolumny, do której wcześniej dołączyłeś w podzapytaniu , które „ZASTOSUJ”.

Składnia:

PostgreSQL 9.3+

lewo | prawo | wewnętrzna DOŁĄCZ DO PÓŹNIEJ

Serwer SQL:

KRZYŻ | ZASTOSOWANIE ZEWNĘTRZNE

INNER JOIN LATERAL jest tym samym co CROSS APPLY
a LEFT JOIN LATERAL jest taka sama jak OUTER APPLY

Przykładowe użycie (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 

I dla 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 

PEŁNE DOŁĄCZENIE

Jednym z mniej znanych rodzajów JOIN jest FULL JOIN.
(Uwaga: FULL JOIN nie jest obsługiwany przez MySQL według 2016)

FULL OUTER JOIN zwraca wszystkie wiersze z lewej tabeli i wszystkie wiersze z prawej tabeli.

Jeśli w lewej tabeli znajdują się wiersze, które nie mają dopasowań w prawej tabeli, lub jeśli w prawej tabeli są wiersze, które nie mają dopasowań w lewej tabeli, wówczas również zostaną wyświetlone te wiersze.

Przykład 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Przykład 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 

Zauważ, że jeśli używasz miękkiego usuwania, będziesz musiał ponownie sprawdzić stan miękkiego usuwania w klauzuli WHERE (ponieważ PEŁNE DOŁĄCZENIE zachowuje się trochę jak UNIA);
Łatwo przeoczyć ten mały fakt, ponieważ umieściłeś AP_SoftDeleteStatus = 1 w klauzuli złączenia.

Ponadto, jeśli wykonujesz PEŁNE DOŁĄCZENIE, zwykle musisz zezwolić na NULL w klauzuli WHERE; zapominanie o dopuszczeniu wartości NULL dla wartości będzie miało takie same efekty jak złączenie WEWNĘTRZNE, co jest czymś, czego nie chcesz, jeśli wykonujesz PEŁNE DOŁĄCZENIE.

Przykład:

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) 

ŁĄCZENIA rekurencyjne

Sprzężenia rekurencyjne są często używane do uzyskania danych rodzic-dziecko. W SQL są one implementowane za pomocą wspólnych rekurencyjnych wyrażeń tabelowych , na przykład:

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;

Różnice między połączeniami wewnętrznymi / zewnętrznymi

SQL ma różne typy łączenia, aby określić, czy w wyniku zostaną uwzględnione (niepasujące) wiersze: INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN i FULL OUTER JOIN INNER OUTER słowa kluczowe INNER i OUTER są opcjonalne). Poniższy rysunek pokazuje różnice między tymi typami złączeń: niebieski obszar reprezentuje wyniki zwrócone przez złączenie, a biały obszar reprezentuje wyniki, których złączenie nie zwróci.

Cross Join SQL Pictorial Presentation ( odniesienie ):

Poniżej znajdują się przykłady z tej odpowiedzi.

Na przykład istnieją dwie tabele, jak poniżej:

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

Zauważ, że (1,2) są unikalne dla A, (3,4) są wspólne, a (5,6) są unikalne dla B.

Przyłączenie wewnętrzne

Połączenie wewnętrzne za pomocą jednego z równoważnych zapytań daje przecięcie dwóch tabel, tj. Dwóch wierszy, które mają one wspólne:

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

Lewe zewnętrzne połączenie

Lewe sprzężenie zewnętrzne da wszystkie rzędy w A plus wszystkie wspólne wiersze w B:

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

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

Prawe połączenie zewnętrzne

Podobnie, prawe łączenie zewnętrzne da wszystkie wiersze w B plus wszystkie wspólne wiersze w A:

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

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

Pełne połączenie zewnętrzne

Pełne połączenie zewnętrzne da ci połączenie A i B, tj. Wszystkie wiersze w A i wszystkie wiersze w B. Jeśli coś w A nie ma odpowiadającego układu odniesienia w B, to część B jest zerowa, a nawzajem.

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

DOŁĄCZ Terminologia: wewnętrzna, zewnętrzna, pół, anty ...

Powiedzmy, że mamy dwie tabele (A i B) i niektóre z ich wierszy są zgodne (względem podanego warunku JOIN, niezależnie od tego, co może być w danym przypadku):

Możemy użyć różnych typów złączeń, aby uwzględnić lub wykluczyć pasujące lub niepasujące wiersze z każdej strony i poprawnie nazwać złączenie, wybierając odpowiednie warunki z powyższego schematu.

Poniższe przykłady wykorzystują następujące dane testowe:

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

Przyłączenie wewnętrzne

Łączy pasujące rzędy lewy i prawy.

SELECT * FROM A JOIN B ON X = Y;

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

Lewy zewnętrzny łącznik

Czasami w skrócie „lewy dołącz”. Łączy lewe i prawe rzędy, które pasują, i obejmuje niepasujące lewe rzędy.

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

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

Right Outer Join

Czasami w skrócie „prawe połączenie”. Łączy lewe i prawe rzędy, które pasują, i obejmuje niepasujące prawe rzędy.

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

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

Pełne połączenie zewnętrzne

Czasami w skrócie „pełne dołączenie”. Połączenie lewego i prawego połączenia zewnętrznego.

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

Obejmuje lewe rzędy pasujące do prawych rzędów.

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

X
-----
Lisa
Marco
Phil

Right Semi Join

Obejmuje prawe rzędy pasujące do lewych rzędów.

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

Y
-----
Lisa
Marco
Phil

Jak widać, nie ma dedykowanej składni IN dla łączenia częściowego lewy kontra prawy - uzyskujemy efekt po prostu zmieniając pozycje tabeli w tekście SQL.

Left Anti Semi Join

Obejmuje lewe rzędy, które nie pasują do prawych rzędów.

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

X
----
Amy
John

OSTRZEŻENIE: Bądź ostrożny, jeśli używasz NOT IN w kolumnie z wartością NULL! Więcej informacji tutaj .

Right Anti Semi Join

Obejmuje prawe rzędy, które nie pasują do lewych rzędów.

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

Y
-------
Tim
Vincent

Jak widać, nie ma dedykowanej składni NOT IN dla lewy kontra prawy anty-semi-join - uzyskujemy efekt po prostu zmieniając pozycje tabeli w tekście SQL.

Cross Join

Kartezjański produkt wszystkich lewych ze wszystkimi prawymi rzędami.

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

Łączenie krzyżowe jest równoważne łączeniu wewnętrznemu z warunkiem łączenia, który zawsze jest zgodny, więc poniższe zapytanie zwróciłoby ten sam wynik:

SELECT * FROM A JOIN B ON 1 = 1;

Self-Join

To po prostu oznacza tabelę łączącą się ze sobą. Samozłączenie może być dowolnym z typów omówionych powyżej. Na przykład jest to wewnętrzne połączenie wewnętrzne:

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