PostgreSQL Lateral Joins

Example from this this webpage:
https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df 


The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

Lateral joins are like running an iteration over the joined subquery.

id | user_id | created_at ----+---------+---------------------------- 1 | 1 | 2017-06-20 04:35:03.582895 2 | 2 | 2017-06-20 04:35:07.564973 3 | 3 | 2017-06-20 04:35:10.986712 4 | 1 | 2017-06-20 04:58:10.137503 5 | 3 | 2017-06-20 04:58:17.905277 6 | 3 | 2017-06-20 04:58:25.289122

SELECT user_id, first_order_time, next_order_time, id FROM
  (SELECT user_id, min(created_at) AS first_order_time 
       FROM orders GROUP BY user_id) o1
  LEFT JOIN LATERAL
  (SELECT id, created_at AS next_order_time
   FROM orders
   WHERE user_id = o1.user_id AND created_at > o1.first_order_time
   ORDER BY created_at ASC LIMIT 1)
   o2 ON true;

 user_id |      first_order_time      |      next_order_time       | id
---------+----------------------------+----------------------------+----
       1 | 2017-06-20 04:35:03.582895 | 2017-06-20 04:58:10.137503 |  4
       3 | 2017-06-20 04:35:10.986712 | 2017-06-20 04:58:17.905277 |  5
       2 | 2017-06-20 04:35:07.564973 |                            |

Source: https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df