1. 12
    Lateral Joins Instead of Window Functions databases germanvelasco.com
    tbonesteaks avatar via tbonesteaks 1 year ago |
    Archive.org Archive.today Ghostarchive
    | 3 comments
  1. 2
    koreth avatar koreth edited 1 year ago | link

    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
      pillepalle avatar pillepalle 1 year ago | link

      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
        sjamaan avatar sjamaan 1 year ago | link

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