Lateral Joins

Written 2020-11-23

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"

Thanks for reading! If you have any questions or comments, please send me a note on Twitter. And if you enjoyed this, I also have a newsletter where I send out interesting things I read and the occasional nature photo.