1. 4
  1.  

  2. 1

    Generally, a lateral join is like a for-each loop, where you have total control over what’s produced for each left-hand-side element. This is useful for fetching the newest order of a customer, as the article describes.

    The alternative would be joining with a subquery where you do SELECT DISTINCT ON (user_id) user_id, created_at … ORDER BY user_id, created_at ASC LIMIT 1. But this alternative would have to scan the whole table. If you have an index on (user_id, created_at), a lateral join can utilize the index. Which is probably the most important thing about lateral joins - sometimes the purpose of a lateral join is to speed up execution of a query.

    I also tend to (ab)use lateral joins to replace long and repeated expressions with a single expression which is then reused, for example I would replace this:

    SELECT v.b, v.b * 4 / 1024, pg_size_pretty(v.b * 4)
    FROM (VALUES (1024::bigint), (2048), (4096)) v (b);
    

    with:

    SELECT v.b, s.b / 1024, pg_size_pretty(s.b)
    FROM (VALUES (1024::bigint), (2048), (4096)) v (b)
    CROSS JOIN LATERAL (SELECT v.b * 4 AS base) s (b);
    ┌──────┬──────────┬────────────────┐
    │  b   │ ?column? │ pg_size_pretty │
    ├──────┼──────────┼────────────────┤
    │ 1024 │        4 │ 4096 bytes     │
    │ 2048 │        8 │ 8192 bytes     │
    │ 4096 │       16 │ 16 kB          │
    └──────┴──────────┴────────────────┘
    

    so that the base formula “b * 4” can be replaced with s.b. Usefulness of this trick is not obvious in this example because “b * 4” is not very long.

    Similarly, I tend to simplify:

    SELECT (SELECT row_to_json(d) FROM (SELECT id, f) AS d)
    FROM (VALUES (1, 'a', 2.1), (2, 'b', 3.3), (1, 'c', 2.2)) data (id, t, f);
    

    to:

    SELECT row_to_json(d)
    FROM (VALUES (1, 'a', 2.1), (2, 'b', 3.3), (1, 'c', 2.2)) data (id, t, f)
    CROSS JOIN LATERAL (SELECT data.id, data.f) AS d;
    ┌──────────────────┐
    │   row_to_json    │
    ├──────────────────┤
    │ {"id":1,"f":2.1} │
    │ {"id":2,"f":3.3} │
    │ {"id":1,"f":2.2} │
    └──────────────────┘
    

    The trick becomes useless as soon as you need to use a window function.