Monday, September 9, 2013

SQL Server Said, PostgreSQL Said. APPLY and LATERAL

SQL Server CROSS APPLY:
select m.*, elder.*
from Member m
cross apply
(
    select top 1 ElderBirthDate = x.BirthDate
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate
Equivalent on PostgreSQL:
select m.*, elder.*
from Member m
join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
 limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate
Note, a JOIN LATERAL(explicitly INNER JOIN LATERAL) with a condition of always true, is essentially a cross join. We can rewrite the PostgreSQL code above as follows:
select m.*, elder.*
from Member m
cross join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder -- we don't need to put any condition here anymore since this is a cross join
order by m.BirthDate
SQL Server OUTER APPLY:
select m.*, elder.*
from Member m
outer apply
(
    select top 1 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate
Equivalent on PostgreSQL:
select m.*, elder.*
from Member m
left join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate
Related: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html


Happy Coding! ツ

No comments:

Post a Comment