Lateral Joins

Written
  • 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 write about tech thoughts, interesting things I've read, and project updates each Thursday.

You can check out a recent issue, or enter your email below to subscribe.