Lateral Joins

Written
  • 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"
      

Thanks for reading! If you have any questions or comments, please send me a note on Twitter.