Lateral Joins

Written 2020-11-23 — Updated 2023-02-10

SQL
Technique in SQL for having a subquery in a join that can reference variables from the outer query.
Most useful when aggregating over things in the subquery to produce an array of results, without needing to group by which is especially inconvenient when aggregating multiple independent joins.

This example from Zapatos gets a single associated author and multiple associated tags for each book.

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb ("books".*) || jsonb_build_object($1::text, "ljoin_0".result, $2::text, "ljoin_1".result) AS result
  FROM "books"
  LEFT JOIN LATERAL (
    SELECT to_jsonb ("authors".*) AS result
    FROM "authors"
    WHERE ("id" = "books"."authorId")
    LIMIT $3) AS "ljoin_0" ON true
  LEFT JOIN LATERAL (
    SELECT coalesce(jsonb_agg(result), '[]') AS result
    FROM (
      SELECT to_jsonb ("tags".*) AS result
      FROM "tags"
      WHERE ("bookId" = "books"."id")) AS "sq_tags") AS "ljoin_1" ON true
)
AS "sq_books"