- 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.