Curious how other developers feel about this. I come across some pretty nasty sql joins, as we all do I'm sure. I would prefer making more database calls for the sake of readability and simplicity rather than have a large SQL statement to maintain; about 3 - 4 joins. For the performance argument, I will give up a little performance for readability.

For example: make the first call, filter the data, and then query the second table, and so on

What is your preference?

  • 10
    3 - 4 joins is by no means a large SQL Statement. Let the RDBMS choose the join strategy and do the filtering. See Joins are for lazy people? Aug 29, 2011 at 17:36
  • 1
    General good practice: try to use the minimum number of SQL queries in your application. Less queries is always a good thing. If I were in your case, I would stick with the joins.Better performance, less time communicating with the database.
    – Jon Martin
    Aug 29, 2011 at 17:37
  • Depending who looks at which pieces of code, you may be able to improve client-side readability by defining views for long SELECT queries.
    – Andrew Lazarus
    Aug 29, 2011 at 18:15
  • Actually, I just threw 3 - 4 out there. Purely hypothetical. Just thinking if we should refactor queries into small steps, just like we should/do with methods. Aug 30, 2011 at 12:29

4 Answers 4


I often see facepalm-worthy code that fetches a result set and loops over it, executing another SQL query against a referenced table for each row. Why? "Because joins are bad."

For example (pseudocode):

for each user row {
    SELECT * FROM Orders WHERE Orders.user = $user;
    for each order row {
         SELECT * FROM LineItems WHERE LineItems.order = $order;
         for each lineitem row {
             SELECT * FROM Products WHERE Products.id = $lineitem;

If you design tables and indexes well, joins are an efficient way of filtering data within the RDBMS, and returning a small result set. Certainly more efficient than doing the equivalent work in application code.

  • Yeah, I hear ya. Inheriting an old code base can be frustrating and extremely difficult to wrap my head around sometimes. Looking for ways to make it more readable without refactoring too much. Very time consuming. Aug 29, 2011 at 19:49
  • 1
    ...and then in the SELECT * FROM Products, you get a truly silly WHERE condition that could just as well have been evaluated way back in the Users SELECT.
    – user
    Aug 30, 2011 at 8:50

In my experience, larger queries give SQL Server more opportunities to get it wrong. Using smaller queries can help SQL Server use a good query plan. For example, in your development environment, SQL Server might perform a huge query exactly in the order you wrote it. Later, when the code migrates to production, suddenly the data is much different. SQL Server might make a wrong assumption and choose to start the query in the middle, and suddenly you end up with crazy joins that create a billion temporary records that get spooled to tempdb.

Performance of complicated queries against huge databases varies. Sometimes, having a complicated query performs better, because it processes the data only once. Sometimes, separating steps is both faster and more readable, because you can ensure the results of the first query limit the data to a small set of rows before performing the rest of the steps.

I try to use separate queries for separate logical steps. I'll document each step with comments so I can more easily remember what it was trying to accomplish. For example:

  1. Do some pre-processing to figure out what the user wants
  2. Gather the data, filtering to the user's selection
  3. Fill in some date-based data from some pricing table
  4. Drop a few records that are not relevant because of some rare condition
  5. Fill in some fields for reporting purposes
  6. Return the results

If you are joining to multiple levels of sub-queries, as in Bill's example, then breaking apart the sub-queries into separate steps can improve performance.

Also note that your plan may incur more I/O as you create, populate, index, and select from temporary tables.

And finally, if it ain't broke-- don't fix it!

  • This is more a case of know your dbms. I don't know SQL Server, but if I found that with Oracle, I would say your statistics are off and you should focus your energy there.
    – jasonk
    May 13, 2012 at 22:51

If it's just 3-4 simple joins, I probably wouldn't fret it too much; that isn't much at all in any reasonably large database. I've seen SQL queries that approach that number of dozens of joins, with subjoins to subjoins - now that is when it starts getting unwieldy! Usually there is a good reason for almost each and every one of them when taken in isolation, but at some point it went from being reasonable to unreasonable to sheer madness with "just one more" condition or field needed. At some point you need to consider refactoring the query (when you find that in order to achieve reasonable performance, the join ordering becomes significant or query hints are needed, that's usually a big warning sign), and breaking it into multiple smaller queries, but unless those joins are truly horrendous, I don't think you are at that point just yet.

The single most important rule though is to actually try it. Look at the execution plan. Is the database server making the most efficient use possible of indexes, or can you perhaps add a filtered index on some table that will help performance without hurting too much in other places? Rewrite the query and compare the execution time, I/O requirement and execution plan. Does it actually help? Also do keep in mind that in any non-trivial query, by rewriting it (even if it seems "simple"), you run the risk of introducing a bug.


Pro tip : SQL queries can be commented to help understand how they work. The same tip applies with regular expressions.

If you think multiple queries are better than one big (well written) one, you should try it with a RDMS server which is not running on the same machine than your application server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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