Last Updated on 7 Settembre 2019 by Roberto De Pedrini
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"--- Roberto De Pedrini Faq400.com