Skip to main content

One-pager of Pro Oracle SQL Development book, part III - Write Elegant SQL


Last week I have bought this book: Pro Oracle SQL Development: Best Practices for Writing Advanced Queries by Jon Heller. So far I did read all chapters from part III - Write Elegant SQL with Patterns and Styles. I recently published two articles about similar topic as APEX Blueprint, so I wanted to see how my world view align with the author.

Here are some excellent quotes from the book, specifically chapters 11. Stop Coding and Start Writing and 13. Write Beautiful SQL Statements.

We must spend extra time upfront to make our code easier for developers later. Making our code readable encourages other people to use our code and will even help our future selves.

It’s good to program with a sense of idealism and beauty, but we should try to keep the dogmatism out of it. After a while we should all evolve a sense of when code “just looks right.” Eventually we’ll know that when our code looks right, it will run right. But sometimes we need to ignore our feelings and stop insisting one style is always best. For example, if we’re modifying existing code, we should imitate the existing style. It’s better to be successful than to be right.

I recommend using enough comments so that someone can read our programs using only the comments. ...we do need to spend extra effort to distill the truths of our program and present those simple truths to readers who have not seen our code before.

Some people say that comments are an apology. Even if that statement is true, when we’re programming we have a lot to apologize for. ...we may have constraints preventing us from using the best approach. In case those constraints change in the future, it helps to explain why we’re doing something bad. It’s better to have a developer find bugs or faulty assumptions through our comments than to have someone else discover the problem in production.

It’s important to choose good names for our databases, schemas, schema objects, functions, procedures, variables, aliases, inline views, and all other programming objects. Choosing good names isn’t just about creating aesthetically pleasing code. We need to create logical, memorable chunks, to help make the most of people’s short-term memory.

Whatever we do, we should spend more than the bare-minimum amount of time to give everything a proper name. We should strive for good names, but be pragmatic about it. Our names can become tiny contracts, guaranteeing us we’re using the values correctly.

If you’re still not convinced of the importance of good names, a few humbling code reviews can be convincing. The best cure for the curse of knowledge is to watch someone else look at our code and wonder out loud, “what does this mean?”

Whitespace is one of the limited number of tools available to help us write better code.

Failing fast is the most important technology concept in recent history. It is laughable that we ever believed we could build something right on the first try. We need to tolerate, and even encourage failures, to more rapidly create something that works. The most important parts of failing fast are software engineering ideas like automated testing, agile, building a minimum viable product, proper attitudes, openness, etc.

We often write bad code even though we know it’s bad. We know we shouldn’t hard-code that value, or call that dangerous function, or depend on that unproven assumption. But we write bad code anyway because we don’t have the time to make our programs perfect. When we have to compromise quality, we instinctively want to hide what we’re doing. We need to fight that instinct and do the exact opposite: make our bad decisions obvious to anyone reading our code.

If we don’t know what we’re doing, we should say so in the comments. If we’re falsely confident, programmers who know better than us might hesitate to change our code. By making our ignorance clear, we’re inviting others to help us.

Ultimately, the only way to be good at writing something is to frequently practice. Just doing our daily work isn’t enough to master a skill. To become good at writing SQL, we must deliberately practice it.

Creating elegant SQL statements requires more than rote knowledge of SQL syntax. We need to think about our true audience – other human beings. Writing good SQL requires efficient processes and technical knowledge. Writing great SQL requires extra effort to tell a story in a limited format.

We should use comments to explain ourselves, use carefully selected names, use whitespace to emphasize important code, and make our code honest to make our bugs obvious.

We just need to ensure that our programming style is not driven by a simple desire to make our code compile. Our programming style must be driven by a desire to be understood by others.

The most important thing about programming styles is to have one.

We’re not just trying to make our SQL look visually pleasing. The true purpose of a programming style is to reduce the complexity and improve the readability of our code. After we internalize the goals of simplicity and readability, our opinions of what constitutes beautiful SQL should reflect an objective goal of better code.

We should not become dependent on automatic code formatters, beautifiers, or pretty printers. While I appreciate the soulless efficiency of automation, we still need to maintain the ability to artisanally craft code. Automatic code formatters work well for most programming languages, but none of them consistently work well for Oracle SQL. A code formatter that works 99% of the time is not be good enough. SQL code formatters are most likely to fail when our SQL statements become large and use advanced features – which is exactly when the code formatting is most important.

Even if we have coding standards, someone may have a good reason for breaking the standard.