Simulare con astuzia un FULL OUTER JOIN in SQL Server

Datamaze
07.09.21 10:41 AM Comment(s)

Quando si utilizza un FULL (OUTER) JOIN fra due tabelle in SQL Server, qualora voleste valorizzare i NULL risultanti, è sufficiente usare ISNULL – o, ancor meglio, COALESCE – nel seguente modo:

SELECT
    COALESCE( A.[Codice], B.[Codice] ) AS [Codice]
  , COALESCE( A.[Colonna 1],  ) AS [Colonna 1]
  , COALESCE( B.[Colonna 2],  ) AS [Colonna 2]
FROM
    A
    FULL JOIN B ON ( A.[Codice] = B.[Codice] )

Nel dataset risultante non vi sarà un singolo record con valori NULL, né per la chiave [Codice], né per le altre due colonne. La gestione del FULL (OUTER) JOIN diventa però più complicata nel caso in cui abbiamo tre o più tabelle.


FULL JOIN

Proviamo a scrivere un FULL (OUTER) JOIN fra quattro tabelle, con una sola colonna in chiave. Sapendo di dover gestire dei potenziali NULL nella chiave di JOIN, probabilmente ci verrebbe da scrivere un comando siffatto:


SELECT
    COALESCE( A.[Codice], B.[Codice], C.[Codice], D.[Codice] ) AS [Codice]
  , COALESCE( A.[Colonna 1],  ) AS [Colonna 1] 
  , COALESCE( B.[Colonna 2],  ) AS [Colonna 2] 
  , COALESCE( C.[Colonna 3],  ) AS [Colonna 3] 
  , COALESCE( D.[Colonna 4],  ) AS [Colonna 4]
FROM 
    A
    FULL JOIN B ON ( B.[Codice] = A.[Codice] )
    FULL JOIN C ON ( C.[Codice] = COALESCE( B.[Codice], A.[Codice] ) )
    FULL JOIN D ON ( D.[Codice] = COALESCE( C.[Codice], B.[Codice], A.[Codice] ) )

Non elegantissimo, vero? Personalmente trovo che i COALESCE nei JOIN rendano il codice meno leggibile, e lo è ancora di meno qualora l’operazione fosse fra più di una colonna. Vediamo un esempio con tre colonne chiave in JOIN:


SELECT
    COALESCE( A.[Chiave 1], B.[Chiave 1], C.[Chiave 1], D.[Chiave 1] ) AS [Chiave 1]
  , COALESCE( A.[Chiave 2], B.[Chiave 2], C.[Chiave 2], D.[Chiave 2] ) AS [Chiave 2]
  , COALESCE( A.[Chiave 3], B.[Chiave 3], C.[Chiave 3], D.[Chiave 3] ) AS [Chiave 3]
  , COALESCE( A.[Colonna 1],  ) AS [Colonna 1] 
  , COALESCE( B.[Colonna 2],  ) AS [Colonna 2] 
  , COALESCE( C.[Colonna 3],  ) AS [Colonna 3] 
  , COALESCE( D.[Colonna 4],  ) AS [Colonna 4]
FROM 
    A
    FULL JOIN B ON
    (
            B.[Chiave 1] = A.[Chiave 1]
        AND B.[Chiave 2] = A.[Chiave 2]
        AND B.[Chiave 3] = A.[Chiave 3]
    )
    FULL JOIN C ON
    (
            C.[Chiave 1] = COALESCE( B.[Chiave 1], A.[Chiave 1] )
        AND C.[Chiave 2] = COALESCE( B.[Chiave 2], A.[Chiave 2] )
        AND C.[Chiave 3] = COALESCE( B.[Chiave 3], A.[Chiave 3] )
    )
    FULL JOIN D ON
    (
            D.[Chiave 1] = COALESCE( C.[Chiave 1], B.[Chiave 1], A.[Chiave 1] )
        AND D.[Chiave 2] = COALESCE( C.[Chiave 2], B.[Chiave 2], A.[Chiave 2] ) 
        AND D.[Chiave 3] = COALESCE( C.[Chiave 3], B.[Chiave 3], A.[Chiave 3] )
    )

Come se non bastasse, i meno confidenti con T-SQL potrebbero avere dei dubbi sulla correttezza del comando (che, vi assicuro, è corretto). Possiamo però non preoccuparcene: ci sono altre soluzioni per simulare l’esatto comportamento di un FULL (OUTER) JOIN fra più tabelle – per come lo immaginiamo noi – senza usare il comando FULL JOIN.


UNION + LEFT JOIN

Anche se non sembra poi così più corto da scrivere, questo metodo ci risparmia i COALESCE nei JOIN. L’aspetto negativo di questo comando è che la creazione del dataset DistinctList potrebbe richiedere del tempo, in quanto UNION rimuove tutti i duplicati (esattamente come se si eseguisse un DISTINCT a posteriori). Inoltre, abbiamo sempre dei LEFT JOIN che ci allontanano dal desiderio di avere un codice il più performante possibile.


SELECT
    DistinctList.[Chiave 1]
  , DistinctList.[Chiave 2]
  , DistinctList.[Chiave 3]
  , COALESCE( A.[Colonna 1],  ) AS [Colonna 1] 
  , COALESCE( B.[Colonna 2],  ) AS [Colonna 2] 
  , COALESCE( C.[Colonna 3],  ) AS [Colonna 3] 
  , COALESCE( D.[Colonna 4],  ) AS [Colonna 4]
FROM
    (
        SELECT [Chiave 1], [Chiave 2], [Chiave 3] FROM A UNION
        SELECT [Chiave 1], [Chiave 2], [Chiave 3] FROM B UNION
        SELECT [Chiave 1], [Chiave 2], [Chiave 3] FROM C UNION
        SELECT [Chiave 1], [Chiave 2], [Chiave 3] FROM D
    
    ) AS DistinctList
    LEFT JOIN A ON
    (
            DistinctList.[Chiave 1] = A.[Chiave 1] 
        AND DistinctList.[Chiave 2] = A.[Chiave 2] 
        AND DistinctList.[Chiave 3] = A.[Chiave 3]
    ) 
    LEFT JOIN B ON
    (
            DistinctList.[Chiave 1] = B.[Chiave 1] 
        AND DistinctList.[Chiave 2] = B.[Chiave 2] 
        AND DistinctList.[Chiave 3] = B.[Chiave 3]
    ) 
    LEFT JOIN C ON
    (
            DistinctList.[Chiave 1] = C.[Chiave 1] 
        AND DistinctList.[Chiave 2] = C.[Chiave 2] 
        AND DistinctList.[Chiave 3] = C.[Chiave 3]
    ) 
    LEFT JOIN D ON
    (
            DistinctList.[Chiave 1] = D.[Chiave 1] 
        AND DistinctList.[Chiave 2] = D.[Chiave 2] 
        AND DistinctList.[Chiave 3] = D.[Chiave 3]
    )
;

Si può fare meglio?


UNION ALL + GROUP BY

Anche questo secondo metodo può non sembrare particolarmente conciso, ma fra tutti è quello che preferisco in assoluto. È il più facile da commentare, manutenere ed estendere con logiche personalizzate per ciascun dataset. Inoltre, ha il non banale pregio di non contenere alcun tipo di OUTER JOIN, né di eseguire alcuna operazione di DISTINCT.


WITH
CTE_Append AS
(
    SELECT
        [Chiave 1], [Chiave 2], [Chiave 3]
      , [Colonna 1], NULL AS [Colonna 2], NULL AS [Colonna 3], NULL AS [Colonna 4] 
    FROM
        A
    UNION ALL
    SELECT
        [Chiave 1], [Chiave 2], [Chiave 3]
      , NULL AS [Colonna 1], [Colonna 2], NULL AS [Colonna 3], NULL AS [Colonna 4] 
    FROM
        B
    UNION ALL
    SELECT
        [Chiave 1], [Chiave 2], [Chiave 3]
      , NULL AS [Colonna 1], NULL AS [Colonna 2], [Colonna 3], NULL AS [Colonna 4] 
    FROM
        C
    UNION ALL
    SELECT
        [Chiave 1], [Chiave 2], [Chiave 3]
      , NULL AS [Colonna 1], NULL AS [Colonna 2], NULL AS [Colonna 3], [Colonna 4] 
    FROM
        D
)
SELECT
    [Chiave 1], [Chiave 2], [Chiave 3]
  , MAX( [Colonna 1] ) AS [Colonna 1] 
  , MAX( [Colonna 2] ) AS [Colonna 2] 
  , MAX( [Colonna 3] ) AS [Colonna 3] 
  , MAX( [Colonna 4] ) AS [Colonna 4] 
FROM
    CTE_Append
GROUP BY
    [Chiave 1], [Chiave 2], [Chiave 3]
;

Se le colonne non chiave fossero tutte numeriche, e il nostro obiettivo fosse quello di prendere quattro tabelle distinte con chiave comune e farle collassare, tramite FULL JOIN, in una unica che riporti l’aggregato (spesso, la somma) delle quattro colonne numeriche (operazione che, per la mia personale esperienza, è abbastanza comune), basterebbe semplicemente sostituire i MAX con SUM. Inoltre, si possono gestire i NULL dove si preferisce, nella CTE o nella SELECT finale.


di Thomas Tolio, pubblicato il 7 settembre 2021