Last Updated on 7 September 2019 by Roberto De Pedrini
In those days, on an Italian newsgroup, it.comp.as400, came up an interesting discussion about LATERAL JOIN: a lot of people don’t even know this type of join, but it’s so powerful and let you simplify your SQL code.
I created some tables with data and some views so you can try by yourself: download source sql code from this link:
We have got a table with carmakers, one with model type and one with sales figures and data… we want to get out one row only for each carmaker with the most sold model: we could play with some OLAP functions (row_number and so on) or we can simply use LATERAL JOIN (Cross join or left join): with lateral join we can pass to a subquery data from the main query … take a look at the following code and pay attention to “A.idBrand=stat.idBrand” … without a lateral join it wouldn’t be possible this join from “different levels”
Example 1: CROSS JOIN LATERAL:
SELECT a.Brand, Country, Modell, Year, Quantity FROM Faq400.AutoBrands A cross JOIN LATERAL (SELECT * from faq400.AutoStats stat WHERE Year=2018 and A.idBrand=stat.IdBrand order by Quantity desc FETCH FIRST 1 ROW ONLY ) B ; Here the results: BRAND COUNTRY MODEL YAR QUANTITY 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
Example 2: LEFT JOIN LATERAL
Similar to example 1, if we use a LEFT JOIN LATERAL instead a CROSS JOIN LATERAL we can get each row from main table (Brands) and the most sold model from our stats. In this case pay attention to “on clause” at the end of the query … with cross join don’t need it!
SELECT a.Brand, Country, Model, YEAR, Quantity FROM Faq400.AutoBrands A left JOIN LATERAL (SELECT * from faq400.AutoStats stat WHERE year=2018 and A.idBrand=stat.IdBrand order by Quantity desc FETCH FIRST 1 ROW ONLY ) B on a.idBrand = b.IdBrand;
Example 3: CROSS JOIN LATERAL as a query simplifying tools
Another interesting use of LATERAL JOIN is as a Query Simplifying tools, see this code suggested by Stefano Tassi on it.comp.as400 newsgroup … we can simplify our query without repeating a formula … Let’s see this those two SQL statements, with and without LATERAL JOIN:
From a table with our invoice details we need only those rows with amount (taxable amount plus VAT) greater than 1.000 Euros, in descending order: In a normal SQL statement without Lateral we have something like this select TaxAmount, VAT, (TaxAmount * VAT) as Amount from InvoiceDetails where (TaxAmount * VAT) >10000 order by (TaxAmount * VAT) desc As you can se we repeat expression (TaxAmount * VAT) for 3 times ... but with a CROSS JOIN LATERAL... select TaxAmount, VAT, c.Amount from InvoiceDetails CROSS JOIN LATERAL (values(TaxAmount * VAT)) as c(Amount) where Amount >10000 order by Amount desc Isn't this nice?
Example 4: Cross Join Lateral to get only last 3 invoices each Customer-code from our Invoices Table
This is another example from Stefano Tassi on it.comp.as400 newsgroup
select a.CustCode , a.Customer , b.ItemCode from Customers a inner join lateral ( select * from Invoices b where a.CustCode=b.CustCode order by InvoiceDate desc fetch first 3 rows only ) b on a.CustCode=b.CustCode You can use "INNER JOIN TABLE" or "INNER JOIN LATERAL" ... it's the same!
Here are some interesting links: 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
Excellent, thanks