Why don't relational databases support returning information in a nested format?

Question

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.

Answer 1

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!"

Answer 2

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

Answer 3

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.

Answer 4

I know at least SQL Server 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.

Answer 5

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.

Answer 6

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

Answer 7

PostgreSQL does support a variety of structured data types, including Arrays and JSON. Using SQL or one of the embedded procedural languages, you can build values with arbitrarily complex structure and return them to your application. You can also create tables with columns of any of the structured types, though you should carefully consider whether you're unnecessarily denormalizing your design.

Answer 8

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.

Answer 9

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:

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

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

Answer 10

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).

Answer 11

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.

Answer 12

In my opinion it is mostly because of SQL and the way 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 beginning 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 has 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.