Posted on by By Nikhilesh, in Business Intelligence, Databases, ETL | 0


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


A Business Intelligence Framework

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments