Cross Apply e Outer Apply: esempi d’uso in SQL

Cross Apply e Outer Apply sono due operatori che possono essere utilizzati all’interno della clausola FROM di una query T-SQL su SQL Server. Come per l’operatore Join, essi permettono di combinare i dati presenti in più tabelle o subquery, ma con un’importante possibilità in più: a destra dell’operatore posso inserire una subquery che dipende da una colonna definita a sinistra di esso.

Spieghiamolo meglio con un esempio:

SELECT fo.IdFornitore, 
   fo.Denominazione, 
   top3.IdFattura, 
   top3.DataFattura
FROM dbo.Fornitori AS fo
CROSS APPLY
 (SELECT TOP 3 
   f.IdFattura, f.DataFattura 
  FROM  dbo.Fatture AS f
  WHERE fo.IdFornitore = f.IdFornitore
  ORDER BY f.DataFattura DESC) AS top3;

Come potete vedere, la sottoquery a destra della CROSS APPLY dipende anche dalla colonna f.IdFornitore della tabella definita a sinistra. Questo ci permette di calcolare agevolmente “le tre fatture più recenti per ogni fornitore”, problema che non sarebbe affrontabile utilizzando esclusivamente le Join.

Differenze tra CROSS APPLY e OUTER APPLY

Tra Cross Apply e Outer Apply esistono differenze simili a quelle tra Inner Join e Left Join.  Riprendendo in considerazione la query precedente: cosa succede se un fornitore non ha nessuna fattura associata?

Se usiamo la Cross Apply, tale fornitore NON sarà visibile nell’output della query. Se invece scriviamo una query identica sostituendo Cross Apply con Outer Apply, anche i fornitori senza fatture associate saranno estratti. In questo caso, le colonne nella select proveniente dalla Subquery saranno valorizzate con dei NULL, analogamente a quanto succede in casi simili con la Left Join.

APPLY e viste parametriche

Cross Apply e Outer Apply possono essere utilizzate efficientemente per interrogare viste parametriche (dette più tecnicamente Inline Table Value Function). Creiamone una:

CREATE FUNCTION 
    dbo.SpedAnnue
   (@Fornitore int)
RETURNS TABLE
RETURN
 SELECT 
  YEAR(DataFattura) AS Anno,
  SUM(Spedizione) AS SpedizioniAnnue
 FROM  Fatture
 WHERE IdFornitore = @Fornitore
 GROUP BY YEAR(DataFattura);

A parte la sintassi di creazione più elaborata nella prima parte, le Inline Table Value Function seguono tutte le regole delle più usuali Viste, con in più la possibilità di aggiungere dei valori parametrici all’interno della query (ad esempio sopra abbiamo utilizzato la variabile @Fornitore). Anche in fase di chiamata, la sintassi da seguire è molto simile a quelle delle viste, se non per la necessità di aggiungere un parametro.

SELECT *
FROM   dbo.SpedAnnue(3);

Tramite la Cross Apply o l’Outer Apply, possiamo passare massivamente in input alla vista parametrica i valori presenti all’interno di una tabella o di una subquery. Possiamo scrivere ad esempio

SELECT 
  T.IdFornitore, 
  F.Anno,
  F.SpedizioniAnnue
FROM (SELECT * 
      FROM   dbo.Fornitori
      WHERE  Regione = 'Lombardia') AS T
CROSS APPLY dbo.SpedAnnue(T.IdFornitore) AS F
ORDER BY 
  T.IdFornitore, 
  F.Anno;

Anche in questo caso occorre fare attenzione alle differenze tra Cross Apply e Outer Apply. Esse riguardano quei fornitori per i quali la vista parametrica restituirebbe un output vuoto di zero righe. In particolare:

  • con la Cross Apply non visualizziamo in output i dati di questi fornitori;
  • con l’Outer Apply visualizziamo anche i fornitori per i quali la vista parametrica è vuota, riempendo con Null le colonne provenienti da essa.

Apply e performance

Osserviamo in primo luogo che quando lanciamo una vista parametrica con l’Apply, SQL Server non esegue più volte la query della vista, ma crea un piano d’esecuzione ad hoc con importanti vantaggi in termini di performance.

Confrontiamo ad esempio il piano d’esecuzione di una query in cui chiamiamo due volte la vista parametrica:

SELECT *
FROM dbo.SpedAnnue(1)
   UNION ALL
SELECT *
FROM dbo.SpedAnnue(2);

Osserviamo che la tabella delle fatture viene letta due volte e infine i risultati sono concatenati

piano d'esecuzione query con union all

Vediamo ora una query equivalente scritta utilizzando la Cross Apply:

SELECT b.*
FROM ( VALUES 
        (1),
        (2)
      ) AS a(IdFornitore)
CROSS APPLY dbo.SpedAnnue(a.IdFornitore) AS b;

Nel piano d’esecuzione vediamo una sola scansione della tabella fatture e un operatore hash join per combinare i dati con la tabella a sinistra.

piano d'esecuzione cross apply
Video Corso Data Analysis completo

Confronto tra Apply e Window function

Riprendiamo ora in considerazione il primo esempio di questo articolo: “estrarre le tre fatture più recenti per ogni fornitore”.

Questo stesso problema può essere trattato anche con le Window function di cui trovi un approfondimento in questo mio articolo. In generale non c’è una risposta definitiva a quale dei due costrutti sia più performante, in quanto essa può dipendere da svariati fattori come:

  • presenza di indici;
  • distribuzione dei dati;
  • esecuzione della query in parallelo.

Nel prossimo video trovi degli esempi a riguardo che confrontano Cross Apply e Window Function.

Continua a imparare

Torna in alto
Torna su