LATERAL

Giriş

PostgreSQL 9.3 yani 2013 yılından beri var. Açıklaması şöyle

The LATERAL keyword appears after a FROM clause in queries with multiple table references.

JOIN

Açıklaması şöyle

... in the case of table joins, the LATERAL keyword must appear after our JOINclause. The same position holds true for all other forms of table joins.

Örnek

Şu SQL hata verir

SELECT name, email, s.ip_addresses
FROM users u
LEFT JOIN (
 SELECT user_id, string_agg(ip_address, ',') "ip_addresses"
 FROM site_visits
 WHERE country in ('China')
 and user_id = u.id
 GROUP BY user_id
) s
ON u.id = s.user_id

Hata şöyle. 

ERROR:  invalid reference to FROM-clause entry for table "u" LINE 9:  and user_id = u.id                        

HINT:  There is an entry for table "u", but it cannot be referenced from this part of the query. SQL state: 42P01 Character: 184

Şöyle yaparız

SELECT name, email, s.ip_addresses
FROM users u
LEFT JOIN LATERAL (
 SELECT user_id, string_agg(ip_address, ',') "ip_addresses"
 FROM site_visits
 WHERE country in ('China')
 and user_id = u.id
 GROUP BY user_id
) s
ON u.id = s.user_id

Daha Okunaklı Sorgu İçin

Örnek

Şöyle yaparız

select u.*, last_visited 
from users u,
lateral(
 select concat(date, ' ', time) "last_visited" from site_visits 
 where user_id = u.id
 order by id desc 
 limit 1
) last_visited
where u.email = 'myuryichev2@guardian.co.uk'