Suppose you want to obtain those employees who work for departments with more than 5 persons. This is not the best query to solve this data question, but I think is a good example to show what is allowed by the LATERAL join:

select emp_id, last_name
from employee e
join (select dep_id, count(*) dept_population from department where dep_id = e.dep_id group by 1) d 
     on d.dep_id = e.dep_id 
where d.dept_population  > 5;

The previous query fails because in the subquery after the join clause we can’t refer to anyrefer any table outside the subquery. In our example the reference to e.dep_id is invalid. In the following query this issue is fixed by using a lateral join instead of a join.

select emp_id, last_name
from employee e
join lateral (select dep_id, count(*) dept_population from department where dep_id = e.dep_id group by 1) d 
     on d.dep_id = e.dep_id 
where d.dept_population  > 5;