Databases are incredibly smart when it comes to fetching related data, a single select is indeed better than splitting queries and doing multiple roundtrips.

The problem however is in how results are returned over the wire. Duplicating rows is needless, but seems to be still the standard.

My experience suggests that they _can_ be good, but this particular pattern they can be remarkably bad at. Source: I keep having to optimise this pattern.