SQL
ADERIRE

introduzione

Sintassi

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

Osservazioni

Giuntura interna esplicita di base

Un join di base (chiamato anche "inner join") esegue query sui dati da due tabelle, con la loro relazione definita in una clausola join .

L'esempio seguente selezionerà i nomi dei dipendenti (FName) dalla tabella Impiegati e il nome del reparto per cui lavorano (Nome) dalla tabella Departments:

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

Ciò restituirebbe il seguente dal database di esempio :

Unirsi implicitamente

Join possono anche essere eseguite da avere più tabelle nel from clausola separati da virgole , e definisce il rapporto tra loro in where clausola. Questa tecnica è chiamata un'adesione implicita (poiché in realtà non contiene una clausola join ).

Tutti gli RDBMS lo supportano, ma di solito la sintassi viene sconsigliata. I motivi per cui è una cattiva idea usare questa sintassi sono:

• È possibile ottenere crossover accidentali che restituiscono risultati errati, soprattutto se si hanno molti join nella query.
• Se intendevi un cross join, allora non è chiaro dalla sintassi (scrivi invece CROSS JOIN), e qualcuno potrebbe cambiarlo durante la manutenzione.

Nell'esempio seguente verranno selezionati i nomi dei dipendenti e il nome dei reparti per cui lavorano:

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

Ciò restituirebbe il seguente dal database di esempio :

Left Outer Join

Un Join esterno sinistro (noto anche come Join sinistro o Join esterno) è un Join che assicura che tutte le righe della tabella sinistra siano rappresentate; se non esiste una riga corrispondente dalla tabella di destra, i suoi campi corrispondenti sono NULL .

L'esempio seguente selezionerà tutti i reparti e il primo nome dei dipendenti che lavorano in quel dipartimento. I reparti senza dipendenti sono ancora restituiti nei risultati, ma avranno NULL per il nome del dipendente:

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

Ciò restituirebbe il seguente dal database di esempio :

Quindi come funziona?

Ci sono due tabelle nella clausola FROM:

e

Innanzitutto un prodotto cartesiano viene creato dalle due tabelle dando una tabella intermedia.
I record che soddisfano i criteri di join ( Departments.Id = Employees.DepartmentId ) sono evidenziati in grassetto; questi sono passati alla fase successiva della query.

Poiché questo è un GIÙ ESTERNO SINISTRO, tutti i record vengono restituiti dal lato SINISTRA del join (Dipartimenti), mentre a tutti i record sul lato DESTRO viene assegnato un indicatore NULL se non corrispondono ai criteri di join. Nella tabella seguente, questo restituirà Tech con NULL

Infine, ogni espressione utilizzata all'interno della clausola SELECT viene valutata per restituire il nostro tavolo finale:

Self Join

Una tabella può essere unita a se stessa, con righe diverse che si corrispondono a seconda delle condizioni. In questo caso d'uso, è necessario utilizzare alias per distinguere le due occorrenze della tabella.

Nell'esempio seguente, per ciascun Dipendente nella tabella Dipendenti del database di esempio , viene restituito un record contenente il nome del dipendente insieme al nome corrispondente corrispondente del manager del dipendente. Poiché i manager sono anche dipendenti, la tabella è unita a se stessa:

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

Questa query restituirà i seguenti dati:

Quindi come funziona?

La tabella originale contiene questi record:

La prima azione consiste nel creare un prodotto cartesiano di tutti i record nelle tabelle utilizzate nella clausola FROM . In questo caso è la tabella Dipendenti due volte, quindi la tabella intermedia sarà simile a questa (ho rimosso tutti i campi non utilizzati in questo esempio):

L'azione successiva è quella di mantenere solo i record che soddisfano i criteri di unire, in modo che qualsiasi record in cui il alias e tavolo ManagerId uguale al alias m tavolo Id :

Quindi, ciascuna espressione utilizzata all'interno della clausola SELECT viene valutata per restituire questa tabella:

Infine, i nomi delle colonne e.FName e m.FName sono sostituiti dai loro nomi di colonne di alias, assegnati con l'operatore AS :

CROSS JOIN

Cross join fa un prodotto cartesiano dei due membri, un prodotto cartesiano significa che ogni riga di una tabella è combinata con ciascuna riga della seconda tabella nel join. Ad esempio, se TABLEA ha 20 righe e TABLEB ha 20 righe, il risultato sarà 20*20 = 400 righe di output.

Utilizzo del database di esempio

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

Che restituisce:

Si consiglia di scrivere un CROSS JOIN esplicito se si vuole fare un join cartesiano, per evidenziare che questo è ciò che si desidera.

Partecipare a una sottoquery

Unire una sottoquery viene spesso utilizzato quando si desidera ottenere dati aggregati da una tabella figlio / dettagli e visualizzarli insieme ai record dalla tabella padre / intestazione. Ad esempio, è possibile che si desideri ottenere un conteggio dei record figlio, una media di alcune colonne numeriche nei record figlio o la riga superiore o inferiore in base a una data o un campo numerico. In questo esempio vengono utilizzati alias, il che rende più facile leggere le query quando sono coinvolte più tabelle. Ecco come appare un join di subquery piuttosto tipico. In questo caso, recuperiamo tutte le righe dalla tabella padre Ordini d'acquisto e recuperiamo solo la prima riga per ogni record padre della tabella figlio 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 APPLY & LATERAL JOIN

Un tipo molto interessante di JOIN è il LATERAL JOIN (nuovo in PostgreSQL 9.3+),
che è anche conosciuto come APPLICAZIONE CROSS / APPLICAZIONE ESTERNA in SQL-Server e Oracle.

L'idea di base è che una funzione valutata a livello di tabella (o subquery inline) venga applicata per ogni riga che aggiungi.

Ciò consente, ad esempio, di unire solo la prima voce corrispondente in un'altra tabella.
La differenza tra un join normale e uno laterale risiede nel fatto che puoi utilizzare una colonna che hai precedentemente aggiunto alla sottoquery che hai "CROSS APPLY".

Sintassi:

PostgreSQL 9.3+

sinistra | giusto | inner JOIN LATERAL

Server SQL:

CROCE | DOMANDA ESTERNA

INNER JOIN LATERAL è uguale a CROSS APPLY
e LEFT JOIN LATERAL è lo stesso di OUTER APPLY

Esempio di utilizzo (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 

E per 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

Un tipo di JOIN meno conosciuto è FULL JOIN.
(Nota: FULL JOIN non è supportato da MySQL come da 2016)

Un FULL OUTER JOIN restituisce tutte le righe dalla tabella di sinistra e tutte le righe dalla tabella di destra.

Se nella tabella a sinistra ci sono delle righe che non hanno corrispondenze nella tabella di destra, o se ci sono delle righe nella tabella di destra che non hanno corrispondenze nella tabella di sinistra, anche quelle righe saranno elencate.

Esempio 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Esempio 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 

Si noti che se si utilizzano eliminazioni software, sarà necessario controllare nuovamente lo stato di eliminazione software nella clausola WHERE (poiché FULL JOIN si comporta in modo simile a UNION);
È facile trascurare questo piccolo fatto, dal momento che hai inserito AP_SoftDeleteStatus = 1 nella clausola join.

Inoltre, se stai facendo un FULL JOIN, di solito devi consentire NULL nella clausola WHERE; dimenticarsi di consentire a NULL su un valore avrà gli stessi effetti di un join INNER, che è qualcosa che non vuoi se stai facendo un FULL JOIN.

Esempio:

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) 

JOIN ricorsivi

I join ricorsivi vengono spesso utilizzati per ottenere dati padre-figlio. In SQL, sono implementati con espressioni di tabella comuni ricorsive, ad esempio:

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;

Differenze tra join interni / esterni

SQL ha vari tipi di join per specificare se le righe (non) corrispondenti sono incluse nel risultato: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN e FULL OUTER JOIN (le parole chiave INNER e OUTER sono facoltative). La figura seguente evidenzia le differenze tra questi tipi di join: l'area blu rappresenta i risultati restituiti dal join e l'area bianca rappresenta i risultati che il join non restituirà.

Presentazione incrociata di SQL ( riferimento ):

Di seguito sono riportati esempi di questa risposta.

Ad esempio ci sono due tabelle come di seguito:

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

Nota che (1,2) sono unici per A, (3,4) sono comuni e (5,6) sono unici per B.

Join interno

Un join interno che utilizza una delle query equivalenti fornisce l'intersezione delle due tabelle, ovvero le due righe che hanno in comune:

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

Giuntura esterna sinistra

Un join esterno sinistro darà tutte le righe in A, più eventuali righe comuni in B:

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

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

Giuntura esterna destra

Allo stesso modo, un join esterno destro darà tutte le righe in B, più tutte le righe comuni in A:

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

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

Full outer join

Un join esterno completo ti darà l'unione di A e B, cioè tutte le righe in A e tutte le righe in B. Se qualcosa in A non ha un dato corrispondente in B, allora la parte B è nullo, e 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

TERMINOLOGIA: Interno, Esterno, Semi, Anti ...

Diciamo che abbiamo due tabelle (A e B) e alcune delle loro righe corrispondono (relative alla condizione JOIN data, qualunque essa sia nel caso particolare):

Possiamo utilizzare vari tipi di join per includere o escludere righe corrispondenti o non corrispondenti da entrambi i lati e denominare correttamente il join selezionando i termini corrispondenti dal diagramma precedente.

Gli esempi seguenti utilizzano i seguenti dati di test:

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

Join interno

Combina le righe sinistra e destra che corrispondono.

SELECT * FROM A JOIN B ON X = Y;

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

Left Outer Join

A volte abbreviato in "left join". Combina le righe sinistra e destra che corrispondono e include le righe non corrispondenti a sinistra.

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

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

Giusto outer join

A volte abbreviato in "right join". Combina le righe sinistra e destra che corrispondono, e include le righe a destra non corrispondenti.

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

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

Full Outer Join

A volte abbreviato in "full join". Unione di giuntura esterna sinistra e destra.

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

Include le righe a sinistra che corrispondono alle righe a destra.

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

X
-----
Lisa
Marco
Phil

Right Semi Join

Include le righe giuste che corrispondono alle righe a sinistra.

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

Y
-----
Lisa
Marco
Phil

Come puoi vedere, non esiste una sintassi IN dedicata per la semina sinistra e destra - otteniamo l'effetto semplicemente cambiando le posizioni della tabella all'interno del testo SQL.

Sinistra Anti Semi Join

Include le righe a sinistra che non corrispondono alle righe giuste.

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

X
----
Amy
John

ATTENZIONE: fai attenzione se stai usando NOT IN su una colonna NULABILE! Maggiori dettagli qui .

Giusto Anti Semi Join

Include le righe giuste che non corrispondono alle righe a sinistra.

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

Y
-------
Tim
Vincent

Come puoi vedere, non esiste una sintassi NOT IN dedicata per l'anti semi join sinistro e destro - otteniamo l'effetto semplicemente cambiando le posizioni della tabella all'interno del testo SQL.

Cross Join

Un prodotto cartesiano di tutti lasciato con tutte le righe giuste.

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 equivale a un join interno con condizione join che corrisponde sempre, quindi la query seguente avrebbe restituito lo stesso risultato:

SELECT * FROM A JOIN B ON 1 = 1;

Self-Join

Questo semplicemente indica una tabella che si unisce a se stessa. Un self-join può essere uno dei tipi di join discussi sopra. Ad esempio, questo è un self-join interno:

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