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, One 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.

Wouldn't it 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.

I know this can be done in a scripting language of your choice, but it requires that the SQL server either sends redundant data (example below) or that you to issue multiple queries like SELECT email FROM emails WHERE user_id IN (/* result of first query */).

Instead of having MySQL return something akin to this:

        "name": "John Smith",
        "dob": "1945-05-13",
        "fav_color": "red",
        "email": "[email protected]",
        "name": "John Smith",
        "dob": "1945-05-13",
        "fav_color": "red",
        "email": "[email protected]",
        "name": "Jane Doe",
        "dob": "1953-02-19",
        "fav_color": "green",
        "email": "[email protected]",

And then having to group on some unique identifier (which means I need to fetch that too!) client-side to reformat the result set how you want it, just return this:

        "name": "John Smith",
        "dob": "1945-05-13",
        "fav_color": "red",
        "emails": ["[email protected]", "[email protected]"]
        "name": "Jane Doe",
        "dob": "1953-02-19",
        "fav_color": "green",
        "emails": ["[email protected]"],

Alternatively, I can issue 3 queries: 1 for the users, 1 for the emails, and 1 for the phone numbers, but then the email and phone number result sets need to contain the user_id so that I can match them back up with the users I previously fetched. Again, redundant data and needless post-processing.

  • 6
    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.
    – Reactgular
    Sep 13, 2013 at 16:01
  • 55
    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. Sep 13, 2013 at 17:51
  • 12
    @SeanMcSomething So true that it hurts, I couldn't have said it better myself.
    – WernerCD
    Sep 13, 2013 at 18:35
  • 6
    This is a great questions. Answers that are saying "this is the way it is" are missing the point. Why is it not possible to return rows with embedded collections of rows? Sep 13, 2013 at 19:03
  • 8
    @SeanMcSomething: Unless that widely-used tool is C++ or PHP, in which case you're probably right. ;) Sep 13, 2013 at 20:41

10 Answers 10


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.

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

  • "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.
    – mpen
    Sep 27, 2013 at 2:19
  • 3
    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.
    – mpen
    Sep 27, 2013 at 2:24
  • @mpen, it's actually far less wasteful than you think. Whilst the client-side API must present the data conceptually as if it is a table, there is nothing to prevent the system just sending one physical copy of the user over the wire. And of course, the database itself often stores the data in normal form. Creating an explicit tree structure from the data once it is on the client, is algorithmically straightforward and involves no significant overhead.
    – Steve
    May 1, 2021 at 7:46

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.

  • 16
    +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, 2013 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. Sep 13, 2013 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. Sep 13, 2013 at 21:37
  • 8
    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, 2013 at 21:59
  • @MarkJ you should write that up as an answer.
    – Mr.Mindor
    Sep 14, 2013 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  [email protected], [email protected], [email protected]   231-123-1234, 231-123-1235
  • 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, 2013 at 20:32
  • 2
    What if the phone number has a "type", like "Cell", "Home", or "Work"? Furthermore, commas are technically allowed in email addresses (if they're quoted) -- how would I split it then?
    – mpen
    May 28, 2015 at 21:35

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

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

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


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
  • 1
    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/[email protected], phone1/[email protected].
    – mike30
    Sep 13, 2013 at 16:23
  • 6
    -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, 2013 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. Sep 16, 2013 at 13:54
  • 3
    You're missing the point of the question. In this hypothetical example, I want all the user data (name, dob, etc) and I want all his/her phone numbers. An inner join excludes users w/ no emails or no phones -- how does that help?
    – mpen
    May 25, 2015 at 15:53

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.


The concept of relational closure basically means that the result of any query is a relation which can be used in other queries as if it was a base table. This is an powerful concept because it makes queries composable.

If SQL allowed you to write queries which output nested data structures, you would break this principle. A nested data structure is not a relation, so you would need a new query language, or complex extensions to SQL, in order to query it further or to join it which other relations.

Basically you would build a hierarchical DBMS on top of a relational DBMS. It will be much more complex for a dubious benefit, and you lose the advantages of a consistently relational system.

I understand why it would sometimes be convenient to be able output hierarchically structured data from SQL, but the cost in the added complexity throughout the DBMS to support this is definitely not worth it.


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.

  • "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?
    – mpen
    Sep 26, 2013 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.
    – GHP
    Sep 26, 2013 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, 2013 at 5:03

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


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

Pls refer to the usage of STUFF function which groups multiple rows (phone numbers) of a column (contact) that can be extracted as a single cell of delimited values of a row (user).

Today we extensively using this but facing some high CPU and performance issues. XML data type is another option but is a design change not a query level one.

  • 5
    Please expand on how this solves the question. Rather than saying to "Pls refer to the usage of", provide an example of how this would achieve the question asked. It can also be helpful to quote 3rd party sources where it makes things clearer. Jul 17, 2018 at 14:08
  • 1
    Looks like STUFF is akin to splice. Not sure how that applies to my question.
    – mpen
    Jul 17, 2018 at 19:49

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