SQL “Lateral Join” … do you know this SQL join? (EN)

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"