When building an SQL query that involves multiple tables, there is always a constant debate about joining the tables or using subqueries.

daily.dev

Using Postgres' JSON capabilities, we can create queries that will make you feel like you're using MongoDB. The inner select creates a result set that stores the newest 3 comments for every post in the main query. The aggregate function turns N rows into a real JSON array.

SQL Join vs Subquery: The Game Changer

<p>Definitely an interesting approach that on the surface looks compelling. I’d love to see benchmarks in large datasets (5m rows+) in a high-throughput environment pitting this approach vs joins (which would return separate rows for each comment) or running two separate queries.
There’s also the piece of this that requires custom mapping type implementation in major ORMs (ex: Doctrine, Eloquent) to take advantage or RDBMS specific functionality. Fortunately, that piece isn’t as painful as it used to be. Regardless, this is great food for thought!</p>


<p>This is truly an endless debate… I usually use JOINs but sometimes subqueries make more sense. Let me know what you think 🙌</p>
