SQL is easy to understand and easy to use. It’s easy to get the right results from a query. However, it’s not that easy to read other people’s correctly working queries though. SQL doesn’t enforce a global formatting standard for writing queries (like PEP8 in Python). There are initiatives like SQL Style Guide but it doesn’t have a widespread following. Hence, It’s difficult to get people to write in a certain way. Moreover, most of the SQL IDEs also don’t support query beautification the same way.

Any fool can write code that a computer can understand. Good programmers write code that humans can understand — Martin Fowler

It then becomes a matter to be solved by being disciplined. How does one make sure that the SQL code they write is easily readable and understandable by others? There are a number of simple steps to follow. More than anything else, following a style guide makes everything so much simpler. Think about this — Following A Style Guide

Python, since inception, has had an inclination towards being simple and readable. The principle of indentation in itself solves for the readability problem quite a lot. SQL, since inception, hasn’t had any such standard which could have been enforced. Let’s circle back to that later. For now, see this example demonstrating how to indent correctly in Python using the PEP8 standard —

The most famous and useful style guide that I have come across is the SQL Style Guide by Simon Holywell. There’s no point in repeating exactly what's in this style guide but it is worth emphasizing that naming conventions, aliasing and indentation go a long way in making the code easier to read and understand. Here’s an excerpt from the style guide —

See, the style guide talks about the simplest of things. Every little effort towards taking care of those simplest of things makes your code cleaner.

Aliasing

SQL is about joins, tables, CTEs and so on. There are so many objects at a given time in a database. When writing a query, we do need to access a lot of them. Sometimes, too many of them, to be honest. If that happens a simple shorthand for accessing these database objects should be in place. Within the organization, that shorthand should be easily recognizable. For example, if there’s a table called delivery_packages, it should just be aliased as dp. As a rule of thumb, just take the first letter of the table name for every word in the snake case table name. The alias for order_feedback_comments should be ofc. Make sure that these tables aliases aren’t too short either. Make sure they make some sense. Check out this post.

Commenting Your SQL Code

If the query is a lot of lines, break it into CTEs or subqueries or create temporary intermediate tables. If you don’t want to do that, either way, make sure you comment your SQL code in a way that’s easy to read and understand and explains what you’re doing with the query in as few words as possible. Caveats, issues, performance related stuff — all else should be commented in detail.

If your code is easily readable, it is easily reusable

Without proper comments, reading through thousand line queries can be and should be difficult. Try not to make it difficult. Comment and comment well. Follow a style guide for that too.

Careful With Subqueries

Finally, as a general rule of thumb, if possible, keep the subqueries to the minimum — not just for performance reasons. Complex multi-level subqueries might get the job done quickly but are increasingly difficult to maintain. If your database supports CTEs, use them instead of using subqueries wherever you can. If the query becomes too complicated and has too many subqueries, create temp tables (possibly in-memory) — and go from there.

One difference between a smart programmer and a professional programmer is that the professional understands that clarity is king. Professionals use their powers for good and write code that others can understand — Robert C. Martin

Avoid the sloppiness. Write better SQL.

Major shoutout to Vicky Boykis for writing about this way back in 2015 and Simon Holywell for preparing the SQL Style Guide.