SQL
applicare croce, applicare esterno

Domande di base su CROSS APPLY e ESTERNO APPLY

L'applicazione verrà utilizzata quando la tabella ha valore di funzione nell'espressione corretta.

creare una tabella dipartimento per contenere informazioni sui reparti. Quindi creare una tabella Employee che contiene informazioni sui dipendenti. Si noti che ogni dipendente appartiene a un reparto, pertanto la tabella Employee ha integrità referenziale con la tabella Department.

La prima query seleziona i dati dalla tabella del dipartimento e utilizza APPLICA CROSS per valutare la tabella Employee per ogni record della tabella Department. La seconda query unisce semplicemente la tabella Department con la tabella Employee e vengono prodotti tutti i record corrispondenti.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID

Se guardi i risultati che hanno prodotto, è lo stesso identico set di risultati; In che cosa differisce da un JOIN e come aiuta a scrivere query più efficienti.

La prima query in Script # 2 seleziona i dati dalla tabella Department e utilizza OUTER APPLY per valutare la tabella Employee per ogni record della tabella Department. Per quelle righe per le quali non esiste una corrispondenza nella tabella Employee, tali righe contengono valori NULL come si può vedere nel caso delle righe 5 e 6. La seconda query utilizza semplicemente un JOINT OUTER SINISTRO tra la tabella Dipartimento e la tabella Employee. Come previsto, la query restituisce tutte le righe dalla tabella Department; anche per quelle righe per le quali non vi è alcuna corrispondenza nella tabella Dipendente.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID
GO

Anche se le due query precedenti restituiscono le stesse informazioni, il piano di esecuzione sarà leggermente diverso. Ma a livello di costi non ci sarà molta differenza.

Ora arriva il momento di vedere dove è realmente richiesto l'operatore APPLY. In Script # 3, sto creando una funzione con valori di tabella che accetta DepartmentID come parametro e restituisce tutti i dipendenti che appartengono a questo reparto. La query successiva seleziona i dati dalla tabella di dipartimento e utilizza APPLICA CROSS per unirsi alla funzione che abbiamo creato. Passa il DepartmentID per ogni riga dall'espressione della tabella esterna (nel nostro caso la tabella Department) e valuta la funzione per ogni riga simile a una sottoquery correlata. La query successiva utilizza l'APPLICAZIONE ESTERNA al posto di APPLICAZIONI CROSS e quindi, a differenza di APPLICAZIONI CROSS, che restituisce solo dati correlati, l'APPLICAZIONE ESTERNA restituisce anche dati non correlati, posizionando NULL nelle colonne mancanti.

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
RETURNS TABLE
AS
  RETURN
  (
  SELECT
    *
  FROM Employee E
  WHERE E.DepartmentID = @DeptID
  )
GO
SELECT
  *
FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT
  *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

Quindi ora, se ti stai chiedendo, possiamo usare un semplice join al posto delle query sopra? Quindi la risposta è NO, se si sostituisce CROSS / OUTER APPLY nelle query sopra con INTERNO JOIN / LEFT OUTER JOIN, specificare la clausola ON (qualcosa come 1 = 1) ed eseguire la query, si otterrà "L'identificatore di più parti" D.DepartmentID "non può essere vincolato." errore. Questo perché con JOINs il contesto di esecuzione della query esterna è diverso dal contesto di esecuzione della funzione (o una tabella derivata) e non è possibile associare un valore / variabile dalla query esterna alla funzione come parametro. Quindi l'operatore APPLY è richiesto per tali query.