Lateral Join in SQL … questo sconosciuto

Prendo spunto da una discussione di questi giorni su it.comp.as400 per portare qualche esempio di utilizzo del “Lateral Join”, un Join assolutamente sottovalutato che spesso semplifica non di poco l’utilizzo di “sottoquery”:

Ho creato un paio di tabelle (Marche, modelli, venduto di auto) per simulare un caso di utilizzo delle funzioni LATERAL:  sorgenti degli script SQL che trovate in questo esempio potete scaricarli a questo link:

L’obiettivo è quello di estrarre tramite un join solo il modello più venduto per ogni marca partendo da 3 tabelle: Marche, Modelli, Venduto … potremmo utilizzare probabilmente le funzioni OLAP giocando sul Row_number … ma col un CROSS JOIN LATERAL possiamo fare riferimento nella sottoquery ad un valore della query principale…

Nel seguente statement SQL è possibile vederne un possibile utilizzo: da una statistica venduto delle automobili … elencare solo il primo modello per ogni marca, quindi, partendo dalla tabella delle marche, mettiamo in CROSS JOIN LATERAL la tabella del venduto agganciando per marca … da notare nella sottoquery “A.idMarca = stat.Marca” cosa che non sarebbe possibile senza un JOIN LATERAL!

Esempio 1 di CROSS JOIN LATERAL:

SELECT a.Marca, Paese, Modello, Anno, Venduto FROM Faq400.AutoMarche A 
cross JOIN LATERAL 
(SELECT * from faq400.AutoStatistica stat
WHERE Anno=2018 and A.idMarca=stat.IdMarca
order by Venduto desc
FETCH FIRST 1 ROW ONLY
) B ;

Risultato:
MARCA                          PAESE MODELLO ANNO VENDUTO
FCA Fiat Chrysler Automobilies IT Panda 2018 11201
FORD                           US Fiesta 2018 5388
Cytroen                        FR C3 2018 4829
Renault                        FR Clio 2018 3945
Toyota                         JP Yaris 2018 3725

Esempio 2: LEFT JOIN LATERAL

Uno statament SQL simile all’esempio 1 ma con il Left JOIN LATERAL … in questo caso vengono elencate anche le marche che non hanno modelli venduti nello statistico …

SELECT a.Marca, Paese, Modello, Anno, Venduto FROM Faq400.AutoMarche A left JOIN
 LATERAL (SELECT * from faq400.AutoStatistica stat
 WHERE Anno=2018 and A.idMarca=stat.IdMarca
 order by Venduto desc
 FETCH FIRST 1 ROW ONLY
 ) B
on a.idmarca = b.IdMarca;

Esempio 3: Uso di CROSS JOIN LATERAL per semplificare una query

Un altro uso del Lateral Join per semplificare le query lo si può vedere in questo esempio preso proprio dalla discussione it.comp.as400 su suggerimento del grande Stefano Tassi:

Voglio tutti i record con iva ( = imponibile * aliquota) >10000,
ordinati per iva discendente:

la select tradizionale sarebbe:

select impon, aliq, (impon * aliq) as iva
from movimenti
where (impon *aliq) >10000
order by (impon * aliq) desc

nella quale l'espressione (impon * aliq) compare TRE volte.
Con CROSS JOIN LATERAL si potrebbe scrivere:

select impon, aliq, c.iva
from movimenti
CROSS JOIN LATERAL (values(impon * aliq)) as c(iva)
where iva >10000
order by iva desc

e l'espressione di sopra compare una volta sola.

Esempio 4: Usare Cross Join Lateral per estrarre gli ultimi 3 movimenti

Ecco un altro esempio sempre riportato da Stefano Tassi nel gruppo it.comp.as400, estrarre gli ultimi 3 movimenti di ogni articolo

Vorrei estrarre, per ogni cliente gli ultimi tre movimenti di magazzino.

select a.clie , a.name , b.part
from clienti a
inner join lateral (
   select * from movimenti b
       where a.clie=b.clie
       order by data_mod desc
       fetch first 3 rows only ) b
on a.clie=b.clie

Usare la keyword Lateral equivale ad usare la keyword Table <inner join table>

Puoi trovare altri esempi di LATERAL JOIN ai seguenti link:
Nick Litten - "Converting RGP to Set Based SQL"
IT Jungle - "Usining lateral correlation to define expressions in DB2 for i"