LATERAL JOIN
Lateral join is a very incredible feature in Postgres (Postgres 9.3+) . The LATERAL key word can precede a sub-SELECT FROM item. It allows the sub-SELECT to point to the columns of FROM items that appear before it in the FROM list. We can say it’s used for cross-referencing. It basically allows sub queries in the right-hand-side of the join to reference columns defined in the left-hand-side of the join.
It can be explained from a simple example :
Suppose there is a business scenario where you want to just check whether a set of people belong in one subclass or not. And accordingly we have to do the manipulations in the outer query.
Select * from
(Select empid , case when x.flag = ‘1’then ‘YES’ else ‘NO’ end as check from getempdata (‘04/03/2017’))base —– getempdata is the function
left join lateral
(select case when pbe.benefitsubclass in (‘6Z’,’6Y’) then ‘1’ end as flag , pbe.personid , pbe.benefitsubclass,pbe.monthlyamount as amt1, pbe.monthlyemployeramount as amt2
from person_bene_election pbe
where base.empid = pbe.personid
and current_timestamp between pbe.createts and pbe.endts
and to_date(’04/03/2017′, ‘mm/dd/yyyy’) between pbe.effectivedate and pbe.enddate
and pbe.benefitelection in (‘E’,’T’)
and pbe.benefitsubclass in (‘6Z’)
)x on x.personid = base.empid
Left join lateral allows us to use the empid from the function in left side of the lateral join to be used for reference in the right side.
Also with the left join lateral returns multiple rows , and also the flag column used in select of lateral join is used in the main FROM CLAUSE.
In a gist, lateral allows you to perform a sub-query in the FROM clause and reference column values from other records in the query. It is similar to a correlated subquery in that it can reference values from another query.
It has some advantages over the the correlated subquery , it can return multiple rows and can be used in the From clause.
Best Open Source Business Intelligence Software Helical Insight is Here
