How do I make complex SQL queries easier to write? [closed]

Question

I'm finding it very difficult to write complex SQL queries involving joins across many (at least 3-4) tables and involving several nested conditions. The queries I'm being asked to write are easily described by a few sentences, but can require a deceptive amount of code to complete. I'm finding myself often using temporary views to write these queries, which seem like a bit of a crutch. What tips can you provide that I can use to make these complex queries easier? More specifically, how do I break these queries down into the steps I need to use to actually write the SQL code?

Note that I'm the SQL I'm being asked to write is part of homework assignments for a database course, so I don't want software that will do the work for me. I want to actually understand the code I'm writing.

More technical details:

• The database is hosted on a PostgreSQL server running on the local machine.
• The database is very small: there are no more than seven tables and the largest table has less than about 50 rows.
• The SQL queries are being passed unchanged to the server, via LibreOffice Base.

Answer 1

I'm basing most of this on just trying to get the "right" answer, so you may discover there are some performance issues. No point in speeding up an incorrect query.

Understand the table relationships - Most will be one to many. Know the "many" table. Identify the fields required for your joins.

Think about LEFT join scenarios - Select all the employees and their paycheck from last month. What if they didn't get a paycheck last month?

Know the result set: 1) In a spreadsheet, manually enter at least one correct record for your query. 2) Write the query in a simple enough form to identify how many records should be returned. Use both of these to test your query to make sure joining a new table doesn't alter the result.

Break up your query into managable parts - You don't have to write it all at once. Complex queries can sometimes just be a collection of simple queries.

Beware of mixed levels of aggregation: If you have to put monthly, quarterly and year-to-date values in the same result set, you'll need to calculate them separately in queries grouped on different values.

Know when to UNION Sometimes it's easier to break up subgroups into their own select statements. If you have a table mixed with managers and other employees, and on each column you have to do Case statements based on membership in one of these groups, it may be easier to write a Manager query and union to an Employee query. Each one would contain their own logic. Having to include items from different tables in different rows is an obvious use.

Complex/Nested formulas - Try to consistently indent and don't be afraid to use multiple lines. "CASE WHEN CASE WHEN CASE WHEN" will drive you nuts. Take the time to think these through. Save the complex calcs for last. Get the correct records selected first. Then you attack complex formulas knowing you're working with the right values. Seeing the values used in the formulas will help you spot areas where you have to account for NULL values and where to handle the divide by zero error.

Test often as you add new tables to make sure you're still getting the desired result set and knowing which join or clause is the culprit.

Answer 2

1. Indentation would be the first thing to do, if you're not doing it already. Not only it's useful with even simple queries, but it is crucial when it comes to joins and queries a bit more complex than a select top 1 [ColumnName] from [TableName].

2. Once indented properly, nothing forbids to add comments inside the query itself, when appropriate. Don't overuse them: if the code is explicit enough, adding comments will just harm the clarity of code. But they are still welcome for the less explicit parts of the query.

   Note that longer queries (including queries with comments) would mean larger bandwidth usage between your application server and your database server. Also note that unless you are working on a Google-scale product with a huge amount of requests per second, requiring an exceptional performance and resources usage, the size added by the comments may not change anything for you in terms of performance.

3. Enforcing the same style over tables, columns, etc. helps the readability a lot too. When a legacy database has the tables PRODUCT, users, USERS_ObsoleteDONT_USE, PR_SHIPMENTS and HRhbYd_UU, somebody is doing something very wrong.

4. Enforcing the same style over queries is important too. For example if you are writing queries for Microsoft SQL Server and you decided to use [TableName] instead of TableName, stick with it. If you go to a new line after a select, don't do it in only half of your queries, but all of them.

5. Don't use *, unless there are strong reasons to do it (like in if exists(select * from [TableName] where ...) in Microsoft SQL Server). Not only * has a negative performance impact in some (if not most) databases, but it's also not helpful for the developer who uses your query. In the same way, a developer must access the values by name, never by index.

6. Finally, for selects, there is nothing wrong in providing a view. For anything else, stored procedures may be also used depending on the project and the people¹ you're working² with.

---

^{¹ Some people hate stored procedures. Others don't like them for several (perfectly valid, at least for them) reasons.}

^{² Your colleagues, the other students, your teacher, etc.}

Answer 3

A bit of a shot in the dark here, but if you are writing lots of temporary views perhaps you haven't realized yet that most places you could put a table in an SQL statement, that table can be replaced by a query.

So, rather than joining table A to temporary view B, you can join table A to the query that you have been using as temporary view B. For example:

    SELECT A.Col1, A.Col2, B.Col1,B.Col2
      FROM (SELECT RealTableZ.Col1, RealTableY.Col2, RealTableY.ID as ID
              FROM RealTableZ 
   LEFT OUTER JOIN RealTableY
                ON RealTableZ.ForeignKeyY=RealTableY.ID
             WHERE RealTableY.Col11>14
            ) As B
        INNER JOIN A
                ON A.ForeignKeyY=B.ID

This example is rather pointless, but should explain the syntax.

For views that aren't "special" (indexed, partitioned) this should result in the same query plan as if you used a view.

As far as making it easier to write, you can verify each piece to make sure you are getting what you expect before writing out the entire query.

My apologies if this is old hat to you already.

Answer 4

Instead of temporary views, use the WITH clause. This makes it much easier to break down large queries into more readable smaller parts.

Answer 5

1. Become more familiar with set theory if you aren't already. SQL is based in set theory and understanding more about sets will help you become more familiar with how SQL works.
2. Practice more SQl, if you are just learning SQL its going to take time to understand how to do everything, somethings just take time before you really understand them, Joins are a great example the more you use them the better you will get at it.
3. Make sure the tables you are querying are properly designed
4. Don't be afraid of using views on select queries, especially if you have a common set that needs to be refined multiple different ways

Answer 6

Like anything else, you want to break the problem down into manageable parts.

That really IS how you solve complex problems, by the way.

So: You want to check out the subquery to see that it really returns what you want before running an outer query on it. You want to try a minimal join of each table you're joining in so you can see that you're really thinking it through properly. Things like that. Hoping to type it all in and get out just exactly what you want in one whack is just unrealistic.

A SQL statement, once it reaches a certain level of complexity, is basically a little program in and of itself. It makes a big difference to really understand how the data is combined, selected, filtered, and output.