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.

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.

share|improve this question
5  
Think of SQL as a spreadsheet, like in Microsoft Excel, then try to figure out how to create a cell value that contains inner cells. It no longer works well as a spreadsheet. What you are looking for is a tree structure, but then you no longer have the benefits of a spreadsheet (i.e. you can't total a column in a tree). Tree structures don't make for very human readable reports. –  Mathew Foscarini Sep 13 '13 at 16:01
40  
SQL isn't bad at returning data, you're bad at querying for what you want. As a rule of thumb, if you think a widely used tool is buggy or broken for a common use case, the problem is you. –  Sean McSomething Sep 13 '13 at 17:51
8  
@SeanMcSomething So true that it hurts, I couldn't have said it better myself. –  WernerCD Sep 13 '13 at 18:35
4  
@SeanMcSomething: Unless that widely-used tool is C++ or PHP, in which case you're probably right. ;) –  Mason Wheeler Sep 13 '13 at 20:41
3  
What you are asking for is for the data to be returned as a nested data structure. Nested data structures are inherently non-relational which is why SQL DBMS's don't return them by default. As noted in the answer, you can get the same effect by requesting a series of rowsets, instead of trying get it all in one query. However, if you really want it to return a nested data structure, you can get that with the SELECT .. FOR XML option of SQL Server (I am sure that similar options exist for Oracle, etc.). –  RBarryYoung Sep 13 '13 at 22:09

8 Answers 8

up vote 5 down vote accepted

Deep down, in the guts of a relational database, its all rows and columns. That is the structure that a relational database is optimized to work with. Cursors work on individual rows at a time. Some operations create temporary tables (again, it needs to be rows and columns).

By working with only rows and returning only rows, the system is able to better deal with memory and network traffic.

As mentioned, this allows for certain optimizations to be done (indexes, joins, unions, etc...)

If one was to want a nested tree structure, this requires that one pulls all the data at once. Gone are the optimizations for the cursors on the database side. Likewise, the traffic over the network becomes one big burst that can take much longer than the slow trickle of row by row (this is something that is occasionally lost in today's web world).

Every language has arrays within it. These are easy things to work with and interface with. By using a very primitive structure, the driver between the database and program - no matter what language - can work in a common way. Once one starts adding trees, the structures in the language become more complex and more difficult to traverse.

It isn't that hard for a programing language to convert the rows returned into some other structure. Make it into a tree or a hash set or leave it as a list of rows that you can iterate over.

There is also history at work here. Transferring structured data was something ugly in the days of old. Look at the EDI format to get an idea of what you might be asking for. Trees also imply recursion - which some languages didn't support (the two most important languages of the old days didn't support recursion - recursion didn't enter Fortran until F90 and of the era COBOL didn't either).

And while the languages of today have support for recursion and more advanced data types, there isn't really a good reason to change things. They work, and they work well. The ones that are changing things are the nosql databases. You can store trees in documents in a document based one. LDAP (its actually oldish) is also a tree based system (though its probably not what you're after). Who knows, maybe the next thing in nosql databases will be one that returns back the query as a json object.

However, the 'old' relational databases... they're working with rows because thats what they're good at and everything can talk to them without trouble or translation.

(12) In protocol design, perfection has been reached not when there is nothing left to add, but when there is nothing left to take away.

From RFC 1925 - The Twelve Networking Truths

share|improve this answer
    
"If one was to want a nested tree structure, this requires that one pulls all the data at once. Gone are the optimizations for the cursors on the database side." -- That doesn't sound true. It would just have to maintain a couple cursors: one for the main table, and then one for each joined table. Depending on the interface, it might return one row and all joined tables in one chunk (partially streamed), or it can stream the subtrees (and perhaps not even query them) until you start iterating them. But yes, that's complicating things a lot. –  Mark Sep 27 '13 at 2:19
    
Every modern language should have some sort of tree class though, no? And wouldn't it be up to the driver to deal with that? I guess the SQL guys still need to design a common format (don't know much about that). The thing that gets me though is that I either have to send 1 query with joins, and get back and filter out the redundant data which each row (the user info, which only changes every Nth row), or issue 1 query (users), and loop over the results, then send two more queries (emails, phones) for each record to fetch the info I need. Either method seems wasteful. –  Mark Sep 27 '13 at 2:24

It's returning exactly what you asked for: a single record set containing the Cartesian product defined by the joins. There are plenty of valid scenarios where that's exactly what you would want, so saying that SQL is giving a bad result (and thus implying that it would be better if you changed it) would actually screw a lot of queries up.

What you're experiencing is known as "Object/Relational Impedance Mismatch," the technical difficulties that arise from the fact that the object-oriented data model and the relational data model are fundamentally different in several ways. LINQ and other frameworks (known as ORMs, Object/Relational Mappers, not coincidentally,) don't magically "get around this;" they just issue different queries. It can be done in SQL too. Here's how I'd do it:

SELECT * FROM users user where [criteria here]

Iterate the list of users and make a list of IDs.

SELECT * from EMAILS where user_id in (list of IDs here)
SELECT * from PHONES where user_id in (list of IDs here)

And then you do the joining client-side. This is how LINQ and other frameworks do it. There's no real magic involved; just a layer of abstraction.

share|improve this answer
12  
+1 for "exactly what you asked for". Too often we jump to the conclusion that there is something wrong with the technology rather than the conclusion that we need to learn how to use the technology effectively. –  Matt Sep 13 '13 at 17:40
1  
Hibernate will retrieve the root entity and certain collections in a single query when the eager fetch mode is used for those collections; in that case it does the reduction of root entity properties in memory. Other ORMs can likely do the same. –  Mike Partridge Sep 13 '13 at 18:36
3  
Actually this is not to blame on the relational model. It copes very nicely with nested relations thank you. This is purely an implementation bug in early versions of SQL. I think more recent versions has added it though. –  John Nilsson Sep 13 '13 at 21:37
6  
Are you sure this is an example of object-relational impedance? Seems to me that the relational model perfectly matches the conceptual data model of the OP: each user is associated with a list of zero, one, or more email addresses. That model is also perfectly usable in an OO paradigm (aggregation: the user object has a collection of emails). The limitation is in the technique being used to query the database, which is an implementation detail. There are query techniques around which do return heirarchical data, e.g. heirarchical DataSets in .Net –  MarkJ Sep 13 '13 at 21:59
    
@MarkJ you should write that up as an answer. –  Mr.Mindor Sep 14 '13 at 2:35

You could use a built in function to concatenate the records together. In MySQL you can use the GROUP_CONCAT() function and in Oracle you can use the LISTAGG() function.

Here is a sample of what a query might look like in MySQL:

SELECT user.*, 
    (SELECT GROUP_CONCAT(DISTINCT emailAddy) FROM emails email WHERE email.user_id = user.id
    ) AS EmailAddresses,
    (SELECT GROUP_CONCAT(DISTINCT phoneNumber) FROM phones phone WHERE phone.user_id = user.id
    ) AS PhoneNumbers
FROM users user 

This would return something like

username    department       EmailAddresses                        PhoneNumbers
Tim_Burton  Human Resources  hr@m.com, tb@me.com, nunya@what.com   231-123-1234, 231-123-1235
share|improve this answer
    
This seems to be the closest solution (in SQL) to what the OP is attempting to do. He will potentially still have to do client side processing to break the EmailAddresses and PhoneNumbers results into lists. –  Mr.Mindor Sep 13 '13 at 20:32

The problem with this is that it's returning the user's name, DOB, favorite color, and all the other information stored

The problem is that you are not being selective enough. You asked for everything when you said

Select * from...

...and you got it (including DOB and favourite colours).

You probably should been a little more (ahem) ...selective, and said something like:

select users.name, emails.email_address, phones.home_phone, phones.bus_phone
from...

It's also possible you might see records that look like duplicates because a user might join to multiple email records, but the field that distinguishes these two is not in your Select statement, so you might want to say something like

select distinct users.name, emails.email_address, phones.home_phone, phones.bus_phone
from...

...over-and-over again for each record...

Also, I notice you're doing a LEFT JOIN. This will join all records on the left of the join (i.e. users) to all records on the right, or in other words:

A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

(http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join)

So another question is do you actually need a left join, or would an INNER JOIN have been sufficient? They are very different types of joins.

Wouldn't be nicer if it returned a single row for each user, and within that record there was a list of emails

If you actually want a single column within the result set to contain a list that is generated on-the-fly, that can be done but it varies depending on which database you're using. Oracle has the listagg function.


Ultimately, I think that your problem might be solved if you rewrite your query close to something like this:

select distinct users.name, users.id, emails.email_address, phones.phone_number
from users
  inner join emails on users.user_id = emails.user_id
  inner join phones on users.user_id = phones.user_id
share|improve this answer
    
using * is discouraged but not the crux of his problem. Even if he selects 0 user columns he may still experience a duplication effect since both Phones and Emails have a 1-many relation to Users. Distinct would not prevent a phone number from appearing twice ala phone1/name@hotmail.com, phone1/name@google.com. –  mike30 Sep 13 '13 at 16:23
4  
-1: "your problem might be solved" says that you don't know what effect would the change from left join to inner join. In this case, this won't reduce the "repetitions" the user is complaining about; it would simply omit those users that lack a phone or email. hardly any improvement. also, when interpreting the "all records on the left to all records on the right" skips the ON criteria, which prunes all the 'wrong' relations inherent in the Cartesian product but keeps all the repeated fields. –  Javier Sep 13 '13 at 21:23
    
@Javier: Yes, which is why I also said do you actually need a left join, or would an INNER JOIN have been sufficient? * OP's description of the problem makes it *sound as though they were expecting the result of an inner join. Of course, without any sample data or a description of what they really wanted, it's hard to say. I made the suggestion because I've actually seen people (ones I work with) do this: choose the wrong join and then complain when they don't understand the results they get. Having seen it, I thought it might have happened here. –  FrustratedWithFormsDesigner Sep 16 '13 at 13:54
    
Guys...I've been using SQL for years. I'm fairly proficient. This was a hypothetical example, and I don't have any actual problem. This is a design question about why SQL didn't add some more options for returning data in a different, nested format. If it were a real problem, I'd have posted on Stackoverflow. –  Mark Sep 26 '13 at 0:25

Queries always produce a rectangular (un-jagged) tabular set of data. There are no nested sub-sets within a set. In the world of sets everything is a pure un-nested rectangle.

You can think of a join as putting 2 sets side-by-side. The "on" condition is how the records in each set are matched up. If a user has 3 phone numbers, then you'll see a 3-time duplication in the user info. A rectangular un-jagged set must be produced by the query. It's simply the nature of joining sets with a 1-to-many relationship.

To get what you want, you must use a separate query like Mason Wheeler described.

select * from Phones where user_id=344;

The result of this query is still a rectanglar un-jagged set. As is everything in the world of sets.

share|improve this answer

You have to decide where the bottlenecks exist. The bandwidth between your database and application is usually pretty fast. There's no reason most databases couldn't return 3 separate datasets within one call and no joins. Then you get to join it all together in your app if you want.

Otherwise, you want the database to put this dataset together and then remove all the repeated values in each row that are the result of the joins and not necessarily the rows themselves having duplicate data like two people with the same name or phone number. Seems like a lot of over-head to save on bandwidth. You would be better off focusing on returning less data with better filtering and removing the columns you don't need. Because Select * is never used in production-well that depends.

share|improve this answer
    
"There's no reason most databases couldn't return 3 separate datasets within one call and no joins" -- How do you get it to return 3 separate datasets with one call? I thought you had to send 3 different queries, which introduces latency between each one? –  Mark Sep 26 '13 at 0:28
    
A stored procedure could be called in 1 transaction, and then return as many datasets as you wanted. Maybe a "SelectUserWithEmailsPhones" sproc is needed. –  Graham Sep 26 '13 at 12:47
1  
@Mark: you can send (in sql server at least) more than one command at as part of the same batch. cmdText="select * from b; select * from a; select * from c" and then use that as the command text for the sqlcommand. –  jmoreno Nov 1 '13 at 5:03

You are missing something. If you want to denormalize your data, you have to do it yourself.

;with toList as (
    select  *, Stuff(( select ',' + (phone.phoneType + ':' + phone.PhoneNumber) 
                    from phones phone
                    where phone.user_id = user.user_id
                    for xml path('')
                  ), 1,1,'') as phoneNumbers
from users user
)
select *
from toList
share|improve this answer

Very simply, don't join your data if you want distinct results for a user query and a phone number query, otherwise as others have pointed out the "Set" or data will contain extra fields for every row.

Issue 2 distinct queries instead of one with a join.

In the stored procedure or inline parameterized sql craft 2 queries and return the results of both back. Most database and languages support multiple result sets.

For example, SQL Server and C# accomplish functionality this by using IDataReader.NextResult().

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.