Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key 'post_id'.

Then I want to run what will probably be my most common query, which is to retrieve a post and all of its comments. Being rather new to relational databases, the approach that appears most obvious to me is to write a query that would look something like:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Which would give me the id and content of the post that I want, along with all the relevant comment rows packaged neatly in an array (a nested representation like you'd use in JSON). Of course, SQL and relational databases don't work like this, and the closest they can get is to do a join between 'posts' and 'comments' that will return a lot of unnecessary duplication of data (with the same post information repeated in every row), which means processing time is spent both on the database to put it all together and on my ORM to parse and undo it all.

Even if I instruct my ORM to eagerly load the post's comments, the best it'll do is to dispatch one query for the post, and then a second query to retrieve all of the comments, and then put them together client-side, which is also inefficient.

I understand that relational databases are proven technology (hell, they're older than I am), and that there's been a ton of research put into them over the decades, and I'm sure there's a really good reason why they (and the SQL standard) are designed to function the way they do, but I'm not sure why the approach I outlined above isn't possible. It seems to me to be the most simple and obvious way to implement one of the most basic relationships between records. Why don't relational databases offer something like this?

(Disclaimer: I mostly write webapps using Rails and NoSQL datastores, but recently I've been trying out Postgres, and I actually like it a lot. I don't mean to attack relational databases, I'm just perplexed.)

EDIT: Since a few people seem to be confused, I'm not asking how to optimize a Rails app, or how to hack my way around this problem in a particular database. I'm asking why the SQL standard works this way when it seems counterintuitive and wasteful to me. There must be some historical reason why the original designers of SQL wanted their results to look like this.

I also think that the people saying "just run two queries" are missing the point too - I may not know the post's id when I'm making a query. I may not have to eager load one relationship, but dozens. I used a concrete example not because it's a particular problem I'm trying to solve, but because it illustrates my point.

For the people below who say that it's not the database's job to return data ready for display, it seems to me that joins and database views perform basically the same functionality - they make the denormalized data in your db easier to put together and use (not necessarily display, but use). I think that what I proposed above is basically equivalent to a join, just simpler for the developer to parse and make use of.

Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key 'post_id'.

Then I want to run what will probably be my most common query, which is to retrieve a post and all of its comments. Being rather new to relational databases, the approach that appears most obvious to me is to write a query that would look something like:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Which would give me the id and content of the post that I want, along with all the relevant comment rows packaged neatly in an array (a nested representation like you'd use in JSON). Of course, SQL and relational databases don't work like this, and the closest they can get is to do a join between 'posts' and 'comments' that will return a lot of unnecessary duplication of data (with the same post information repeated in every row), which means processing time is spent both on the database to put it all together and on my ORM to parse and undo it all.

Even if I instruct my ORM to eagerly load the post's comments, the best it'll do is to dispatch one query for the post, and then a second query to retrieve all of the comments, and then put them together client-side, which is also inefficient.

I understand that relational databases are proven technology (hell, they're older than I am), and that there's been a ton of research put into them over the decades, and I'm sure there's a really good reason why they (and the SQL standard) are designed to function the way they do, but I'm not sure why the approach I outlined above isn't possible. It seems to me to be the most simple and obvious way to implement one of the most basic relationships between records. Why don't relational databases offer something like this?

(Disclaimer: I mostly write webapps using Rails and NoSQL datastores, but recently I've been trying out Postgres, and I actually like it a lot. I don't mean to attack relational databases, I'm just perplexed.)

I'm not asking how to optimize a Rails app, or how to hack my way around this problem in a particular database. I'm asking why the SQL standard works this way when it seems counterintuitive and wasteful to me. There must be some historical reason why the original designers of SQL wanted their results to look like this.

Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key 'post_id'.

Then I want to run what will probably be my most common query, which is to retrieve a post and all of its comments. Being rather new to relational databases, the approach that appears most obvious to me is to write a query that would look something like:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Which would give me the id and content of the post that I want, along with all the relevant comment rows packaged neatly in an array (a nested representation like you'd use in JSON). Of course, SQL and relational databases don't work like this, and the closest they can get is to do a join between 'posts' and 'comments' that will return a lot of unnecessary duplication of data (with the same post information repeated in every row), which means processing time is spent both on the database to put it all together and on my ORM to parse and undo it all.

Even if I instruct my ORM to eagerly load the post's comments, the best it'll do is to dispatch one query for the post, and then a second query to retrieve all of the comments, and then put them together client-side, which is also inefficient.

I understand that relational databases are proven technology (hell, they're older than I am), and that there's been a ton of research put into them over the decades, and I'm sure there's a really good reason why they (and the SQL standard) are designed to function the way they do, but I'm not sure why the approach I outlined above isn't possible. It seems to me to be the most simple and obvious way to implement one of the most basic relationships between records. Why don't relational databases offer something like this?

(Disclaimer: I mostly write webapps using Rails and NoSQL datastores, but recently I've been trying out Postgres, and I actually like it a lot. I don't mean to attack relational databases, I'm just perplexed.)

Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key 'post_id'.

Then I want to run what will probably be my most common query, which is to retrieve a post and all of its comments. Being rather new to relational databases, the approach that appears most obvious to me is to write a query that would look something like:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Which would give me the id and content of the post that I want, along with all the relevant comment rows packaged neatly in an array (a nested representation like you'd use in JSON). Of course, SQL and relational databases don't work like this, and the closest they can get is to do a join between 'posts' and 'comments' that will return a lot of unnecessary duplication of data (with the same post information repeated in every row), which means processing time is spent both on the database to put it all together and on my ORM to parse and undo it all.

Even if I instruct my ORM to eagerly load the post's comments, the best it'll do is to dispatch one query for the post, and then a second query to retrieve all of the comments, and then put them together client-side, which is also inefficient.

I understand that relational databases are proven technology (hell, they're older than I am), and that there's been a ton of research put into them over the decades, and I'm sure there's a really good reason why they (and the SQL standard) are designed to function the way they do, but I'm not sure why the approach I outlined above isn't possible. It seems to me to be the most simple and obvious way to implement one of the most basic relationships between records. Why don't relational databases offer something like this?

(Disclaimer: I mostly write webapps using Rails and NoSQL datastores, but recently I've been trying out Postgres, and I actually like it a lot. I don't mean to attack relational databases, I'm just perplexed.)

EDIT: Since a few people seem to be confused, I'm not asking how to optimize a Rails app, or how to hack my way around this problem in a particular database. I'm asking why the SQL standard works this way when it seems counterintuitive and wasteful to me. There must be some historical reason why the original designers of SQL wanted their results to look like this.

I also think that the people saying "just run two queries" are missing the point too - I may not know the post's id when I'm making a query. I may not have to eager load one relationship, but dozens. I used a concrete example not because it's a particular problem I'm trying to solve, but because it illustrates my point.

For the people below who say that it's not the database's job to return data ready for display, it seems to me that joins and database views perform basically the same functionality - they make the denormalized data in your db easier to put together and use (not necessarily display, but use). I think that what I proposed above is basically equivalent to a join, just simpler for the developer to parse and make use of.