Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jul 7 '11 at 2:59

This question came from our site for professional and enthusiast programmers.

1  
not all orms work that way. hibernate/nhibernate allows for joins to be specified, and can eager load entire object trees from a single query. –  nathan gonzalez Jul 6 '11 at 21:07
1  
also, while an interesting point of discussion, i'm not sure this is really answerable without having a meeting with the ansi sql guys –  nathan gonzalez Jul 6 '11 at 21:09
    
@nathan: Yeah, not all. I've been using Sequel which lets you choose which approach you prefer for a given query (docs), but they still encourage the multiple-query approach (for performance reasons, I suppose). –  PreciousBodilyFluids Jul 6 '11 at 21:14
5  
Because a RDBMS is designed to store and retrieve sets - it's not intended to return data for display. Think of it like MVC - why would it try to implement the view at the cost of making the model slower or more difficult to use? RDBMS offer benefits that NoSQL databases can't (and visa versa) - if you're using it because it's the right tool to solve your problem, you wouldn't ask it to return data ready for display. –  Todd R Jul 6 '11 at 21:28
9  
I don't understand the close votes. This is a legitimate and very interesting question. –  Stefano Borini Jul 6 '11 at 21:53

11 Answers 11

up vote 35 down vote accepted

C. J. Date goes into detail about this in Chapter 7 and Appendix B of SQL and Relational Theory. You're right, there's nothing in relational theory that prohibits an attribute's data type from being a relation itself, as long as it's the same relation type on every row. Your example would qualify.

But Date says structures like this are "usually--but not invariably--contraindicated" (i.e. a Bad Idea) because hierarchies of relations are asymmetric. For example, a transformation from nested structure to a familiar "flat" structure cannot always be reversed to recreate the nesting.

Queries, constraints, and updates are more complex, harder to write, and harder for the RDBMS to support if you allow relation-valued attributes (RVA's).

It also muddies database design principles, because the best hierarchy of relations isn't so clear. Should we design a relation of Suppliers with a nested RVA for parts supplied by a given Supplier? Or a relation of Parts with a nested RVA for suppliers who supply a given Part? Or store both, to make it easy to run different types of queries?

This is the same dilemma that results from the hierarchical database and the document-oriented database models. Eventually, the complexity and cost of accessing nested data structures drives designers to store data redundantly for easier lookup by different queries. The relational model discourages redundancy, so RVA's can work against the goals of relational modeling.

From what I understand (I have not used them), Rel and Dataphor are RDBMS projects that support relation-valued attributes.


Re comment from @dportas:

Structured types are part of SQL-99, and Oracle supports these. But they don't store multiple tuples in the nested table per row of the base table. The common example is an "address" attribute which appears to be a single column of the base table, but has further sub-columns for street, city, postal code, etc.

Nested tables are also supported by Oracle, and these do allow multiple tuples per row of the base table. But I am not aware that this is part of standard SQL. And keep in mind the conclusion of one blog: "I'll never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!"

share|improve this answer
1  
+1 Great answer –  Vitor Jul 7 '11 at 3:29
1  
I wouldn't want to actually store one relation inside another - they'd be in separate tables and denormalized as usual. I'm just asking why this sort of embedding of results isn't allowed in queries, when it seems more intuitive to me than the join model. –  PreciousBodilyFluids Jul 7 '11 at 16:15
    
Result sets and tables are of a kind. Date calls them relations and relvars respectively (by analogy, 42 is an integer, whereas a variable x can have the value of integer 42). The same operations apply to relations and relvars, so their structure needs to be compatible. –  Bill Karwin Jul 7 '11 at 19:17
1  
Standard SQL does support nested tables. They are called "structured types". Oracle is one DBMS that has this feature. –  sqlvogel Jul 8 '11 at 10:45
    
Isn't it slighty absurd to argue that to avoid data duplication, you must write your query in a flat, data-duplicating manner? –  Eamon Nerbonne May 19 '13 at 7:59

I am not able to answer with a proper, argumented answer, so feel free to downvote me into oblivion if I am wrong (but please correct me so we can learn something new). I think that the reason is that relational databases are centered on the relational model, which in turn is based on something I know nothing about called "first order logic". What you may ask probably does not conceptually fit in the mathematical/logical framework relational databases are built upon. Moreover, what you ask is generally solved easily by graph databases, giving more hints that it's the underlying conceptualization of the database that conflicts with what you want to achieve.

share|improve this answer

I'm sorry I'm not sure I understand your issue exactly.

In MSSQL you can just execute 2 SQL Statements.

SELECT id, content
FROM posts
WHERE id = 7

SELECT * FROM comments WHERE post_id = 7

And it will return your 2 result sets simultaneously.

share|improve this answer
    
The person asking the question is saying that this is less efficient because it results in two round-trips to the database, and we usually try to minimize round trips because of the overhead. He wants to do one round trip and get both tables back. –  Scott Whitlock Jul 7 '11 at 12:01
    
But it will be one round trip. stackoverflow.com/questions/2336362/… –  Biff MaGriff Jul 7 '11 at 16:06

In my opinion it is mostly because of SQL and way how aggregate queries are performed - aggregate functions and grouping are executed on large 2-dimensional rowsets to return results. That's the way it's been since the beggining and it is very fast (most NoSQL solutions are quite slow with aggregation and rely on denormalized schema instead of complex queries)

Of course PostgreSQL have some features from object-oriented database. According to this mails (message) you can achieve what you need by creating custom aggregate.

Personally I'm using frameworks like Doctrine ORM (PHP) which do the aggregation application-side and support features like lazy-loading to increase performance.

share|improve this answer

RDBMs are based on theory and they stick to the theory. This allows for some nice consistency, and mathmatically proven reliability.

Because the model is simple and again based on theory it makes it easy for people to make optimization and many implementations. This is unlike NoSQL where everybody does it slightly different.

There have been attempts in the past to make hierarchal databases but IIRC (cant seem to google it) there have been issues (cycles and equality come to mind).

share|improve this answer

Some of the earliest database systems were based upon the Hierarchical Database model. This represented data in a tree like structure with parent and children, much like you are suggesting here. HDMS were largely superseded by databases built upon the relational model. The major reasons for this were that RDBMS could model "many to many" relationships which were difficult for hierarchical databases and that RDBMS could easily perform queries that were not part of the original design whereas HDBMS constrained you to query through paths specified at design time.

There are still some examples of hierarchical database systems in the wild, particularly the windows registry and LDAP.

Extensive coverage of this subject is available in the following article

share|improve this answer

I suppose that your question really is centered at the fact that while databases are based on a solid logic and set theroretic basis and they do a very good job storing, manipulating and retrieving data in (2-dimensional) sets while ensuring referential integrity, concurrency and many other things, they don't provide an (additional) feature of sending (and receiving) data in what one could call object-oriented format or hierarchical format.

Then you claim that "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 don't see anything inefficient in sending 2 queries and receiving 2 batches of results with:

--- Query-1-posts
SELECT id, content 
FROM posts
WHERE id = 7


--- Query-2-comments
SELECT * 
FROM comments 
WHERE post_id = 7

I'd argue that is (almost) the most efficient way (almost, as you don't really need the posts.id and not all columns from comments.* )

As Todd pointed in his comment, you shouldn't ask the database to return data ready for display. It's the application's job to do that. You can write (one or a few) queries to get the results you need for every display operation so there is no unnecessary duplication in the data sent over the wire (or the memory bus) from the db to the application.

I can't speak about ORMs really but perhaps some of them can do part of this job for us.

Similar techniques can be used in the delivery of data between a web server and a client. Other techniques (like caching) are used so the database (or the web or other server) is not overloaded with duplicate requests.

My guess is that standards, like SQL, is best if they stay specialized in one area and not try to cover all areas of a field.

On the other hand, the commitee that sets the SQL standard may well think otherwise in the future and provide standarization for such an additional feature. But it's not something that can be designed in one night.

share|improve this answer
1  
I meant inefficient in the sense that my application has to incur the overhead and delay of two database calls instead of just one. Aside from that, isn't doing a join also just returning data in a format that's ready for display? Or using a database view? You could obviate them as well by simply running more small queries and stitching them together in your app, if you wanted to, but they're still useful tools. I don't think what I'm proposing is significantly different from a join, aside from being easier to use and more performant. –  PreciousBodilyFluids Jul 6 '11 at 23:46
    
@Precious: There doesn't have to be any increased overhead for running multiple queries. Most databases allow you to submit multiple queries in a single batch and receive multiple result sets from a single query. –  Daniel Pryden Jul 7 '11 at 0:50
    
@PreciousBodilyFluids - the SQL snippet in ypercube's answer is a single query which would be sent in a single database call and return two result sets in a single response. –  Carson63000 Jul 7 '11 at 5:01

Some do support nesting (hierarchical).

If you wanted one query you could have one table that self references itself. Some RDMS support this concept. For example, with SQL Server one can user Common Table Expressions (CTEs) for a hierarchical query.

In your case the Posts would be at Level 0 and then all the comments would be at Level 1.

The other options are 2 queries or a Join with some extra information for every record returned (that others have mentioned).

Example of Hierarchical:

http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example

In the above link, EmpLevel show the level of the nesting (or hierarchy).

share|improve this answer
    
I can't find any documentation about sub-resultsets in SQL Server. Even when using a CTE. By resultset I mean rows of data with just enough strongly-typed columns. Can you add references to your answer? –  SandRock Jan 8 at 23:10
    
@SandRock - A database will send back a single result set back from a SQL Query. By identifying levels in the query itself you could create a hierarchical or nested result set that would haev to be processed. I think currently that is closest we are gonig to get to returning data that is nested. –  Jon Raynor Jan 9 at 16:51

I know at least SQLServer does support nested queries when you use FOR XML.

SELECT id, content, (SELECT * FROM comments WHERE post_id = posts.id FOR XML PATH('comments'), TYPE) AS comments
FROM posts
WHERE id = 7
FOR XML PATH('posts')

The problem here is not the lack of support from the RDBMS, but lack of support of nested tables in tables.

Beside, what stops you from using a inner join?

SELECT id, content, comments.*
FROM posts inner join comments on comments.post_id = posts.id
WHERE id = 7

You can actual look at the inner join as a nested table, only the content of the first 2 fields is repeated a could time. I wouldn't worry about performance of the join much, the only slow part in a query like this is the io from the database to the client. This will only be a issue when content contains a large amount of data. In that case i would suggest two queries, one with select id, content and one with a inner join and select posts.id, comments.*. This scales even with multiple post's, as you would still only use 2 queries.

share|improve this answer
    
The questions addresses this. Either you have to make two round trips (not optimal) or you have to return redundant data in the first two columns (also not optimal). He wants the optimal solution (not unrealistic in my opinion). –  Scott Whitlock Jul 7 '11 at 12:03
    
I know, but there is no suck thing as a optimal solution. The only thing I can argue is where the overhead would be minimal and where it depend on. If you want the optimal solution, benchmark and try different approaches. Even the XML solution might be slower depending on the specific situation, and I'm unfamiliar with NoSQL datastores so i can't say if it has something similar as for xml. –  Dorus Jul 7 '11 at 12:41

Actually Oracle supports what you want but you need to wrap the sub-query with "cursor" keyword. Results are fetched via open cursor. In Java, for example comments would show up as result sets. More on this see Oracle's documentation on "CURSOR Expression"

SELECT id, content, cursor(SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7
share|improve this answer

You have a specific need. It would be prefered to extract data out of a database in the format you want, so you can do with it what you want.

Some things databases don't do as well, but it's not an impossibility to build them to do it anyway. Leaving formating to other applications is the current recommendation, but doesn't justify why it can't be done.

The only arguement I have against your suggestion is being able to handle this result set in a "sql" way. It would be a bad idea to create a result in the database an not be able to work with it or manipulate it to some extent. Let's say I created a view built the way you suggest, how do I include it in another select statement? Databases like to take results and do things with them. How would I join it to another table? How would I compare your result set to another?

Then benefit of RDMS's is the flexibility of sql. The syntax for selecting data from a table is pretty close to a list of users or other objects in the system (At least that's the goal.). Not sure there is any point to doing something completely differet. They havne't even gotten them to the point of handling procedural code/cursors or BLOBS of data very efficiently.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.