1. 12
  1. 2

    Lateral joins are useful and could stand to be more widely known, so I appreciate this kind of article. But the example doesn’t really need them and can be expressed more concisely without them. On PostgreSQL (which the article specifically targets), you could use DISTINCT ON:

    SELECT DISTINCT ON (users.id) users.email, events.created_at AS latest_log_in
    FROM users
    JOIN events
    ON events.user_id = users.id
    WHERE events.name = 'log_in'
    ORDER BY users.id, events.created_at DESC;
    

    Or using standard SQL, if email addresses are unique and required, you could do

    SELECT users.email, MAX(events.created_at) AS latest_log_in
    FROM users
    JOIN events
    ON events.user_id = users.id
    WHERE events.name = 'log_in'
    GROUP BY users.email;
    
    1. 2

      Since the author writes “If only Postgres had a for_each function…”, why not put it as a subquery in the select list? Something like:

      SELECT users.id, users.email, (
          SELECT created_at
          FROM events
          WHERE events.user_id = users.id
              AND events.name = "log_in"
          ORDER BY created_at DESC
          LIMIT 1
      ) AS latest_log_in
      FROM users;
      

      It works since the subquery returns at most one scalar value. This solution does not need LATERAL joins and can be used, e.g., in a UNION, where you cannot use DISTINCT ON directly due to the ORDER BY.

      1. 1

        Came here to say exactly the same thing regarding DISTINCT ON. The second example is a nice observation too.