Remember the first time you saw a large SQL query written by somebody else? I do. Meaningless aliases, no comments, all sub-queries and no common-table-expressions. Almost zero readability. What’s worse is that I had to debug this query because it was resulting in a discrepancy. Where on earth do I start?
First - format the query using any one of the million tools available online. I use dpriver.
Second - in the words of my database class professor, “Read the query like the computer reads it.”
When executing a query, the database management system follows a specific order of execution -
In other words -
Trying to understand the SELECT clause before understanding which tables are involved in the query is, to the say the least, difficult. Ignore the SELECT and jump straight to the FROM clause. Once you understand this, half the job is done.
Note: When dealing with sub-queries, start with the innermost query and move gradually outwards.
Once you have a good understanding of the query, and have made the changes you need, be a good samaritan and rewrite parts of the query that you think can be made more readable. Specifically, I’m a huge advocate of using the WITH clause in place of sub-queries. They enforce a sense of “sequence” to the query and make it easier to think through the logic of the query.
Let’s say we wanted to identify the customers who have a lifetime revenue of more than $100, and then use this group as an input to our SQL query. Normally, we’d write it as a sub-query and join it in with the rest of the query. While this does the job, the query is much more readable if a WITH clause is used for this purpose. Before the start of the query, define the WITH table:
WITH cust_group AS
(SELECT customer_id, sum(order_total)
GROUP BY customer_id
This temporary table can be joined in to the query just like any other table. Note that this is computationally as efficient as the sub-query method, just a lot more readable.
Hopefully, these tips help in reducing the time and frustration when reading a query written by someone else, or even one that you wrote yourself a long time ago. I’d love to hear more tips to easily read SQL or to make SQL more readable. Please feel free to leave your thoughts in the comments below.