Use of Lateral Subqueries in PostgreSQL
Lateral subqueries are subqueries appearing in FROM can be preceded by the keyword LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)
In simple words, Lateral keyword allows passing columns values from the previous join to your subquery.
Note: This new feature available in Postgres version 9.3 and above.
For Example :
Let’s take an example to suppose you are joining two table (table1 and table2) which returns 10 rows after joining and you want to add 1 column which holds YTD value. for this new column, you have written a subquery. let’s say 100 rows returned by subquery for the year 2015.
Table Name
employee = employee_id, company_id, emp_name
company = company_id, companyname
emp_sal = employee_id, month,year, salary
Subquery without Lateral Keyword.
select employee.emp_name, c.companyname ,subquery.ytdvalue, subquery.year
from employee e join company c on e.company_id = c.company_id
join
( select sum(salary) as ytdvalue ,year, e.employee_id
from employee e1 join emp_sal es on e1.employee_id = es.employee_id
group by e1.employee_id, year
) subquery on subquery.employee_id = e.employee_id
where
e.employee_id in (1,2,3,4,5,6,7,8,9,10)
Explaination: Above subquery runs 100 times for each individual id and then join with e.employee_id and returns 10 rows as final output.
Subquery with Lateral Keyword
select employee.emp_name, c.companyname ,lateralsubquery.ytdvalue, lateralsubquery.year
from employee e join company c on e.company_id = c.company_id
join lateral
( select sum(salary) as ytdvalue,year, e.employee_id
from employee e1 join emp_sal es on e1.employee_id = es.employee_id
where
e1.employee_id = e.employee_id
group by e1.employee_id, year
) lateralsubquery on lateralsubquery.employee_id = e.employee_id
where
e.employee_id in (1,2,3,4,5,6,7,8,9,10)
Explaination: Above lateral subquery runs 10 times for each individual id because we are passing e.employee_id into subquery.
For more information visit
https://www.postgresql.org/docs/9.3/static/queries-table-expressions.html
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Thanks and Regards
Fahad Anjum