For example, say I want to fetch a User and all of his phone numbers and email addresses. The phone numbers and emails are stored in separate tables, 1-user to many phones/emails. I can do this quite easily:
SELECT * FROM users user LEFT JOIN emails email ON email.user_id=user.id LEFT JOIN phones phone ON phone.user_id=user.id
The problem* with this is that it's returning the user's name, DOB, favorite color, and all the other information stored in the user table over-and-over again for each record (users*emails*phones records), presumably eating up bandwidth and slowing down the results (unless I'm mistaken on this?).
Wouldn't be nicer if it returned a single row for each user, and within that record there was a list of emails and a list of phones? It would make the data much easier to work with too.
I know you can get results like this using LINQ or perhaps other frameworks, but it seems to be a weakness in the underlying design of relational databases.
We could get around this by using NoSQL, but shouldn't there be some middle ground?
Am I missing something? Why doesn't this exist?
* Yes, it's designed this way. I get it. I'm wondering why there isn't an alternative that is easier to work with. SQL could keep doing what it's doing but then they could add a keyword or two to do a little bit of post-processing that returns the data in a nested format instead of a cartesian product.