SQL Best Practices – How to type code cleanly and perfectly organized

n this post (which is a perfect companion to our SQL tutorials), we will pay attention to coding style. There are many ways you can write your code, but there are only a few considered professional. Complying with coding style rules is crucial. When you advance in programming, not only in SQL but in any language, you will never work on your own. Even at the very start of your career in data science, you will always work in a team. Even if you are a freelancer working online, there will always be someone who will read your code at some point. Then, you’ll find out there is one notion that will not be giving you a break – the notion of clean code. Clean code is code that is focused and understandable, which means it must be readable, logical, and changeable. Remember – good code is not the one computers understand; it is the one humans can understand. Often a program can be created in many ways, and code, in general, can be organized in several ways. Good practice implies you will choose the version that will be easiest to read and understand and will be the one that does not hinder your colleagues from updating it when necessary. They will likely work on top of your code, so it is best if they don’t lose time figuring out ideas beneath complex lines of code. That’s why the assumption is that, at your workplace, you will always type code cleanly – as simple as possible, perfectly organized, maintaining a steady logical flow.

Good Style

Now, let’s focus on another aspect of coding in good style. When assigning names to variables or SQL objects, always choose shorter, meaningful names, conveying specific information. By ‘meaningful’, we mean names that are pronounceable, where one word per concept has been picked. For instance, that is the reason we chose “purchase number”, not “customer purchase unique number” in the “Sales” table. On that account, you need not be in a rush to choose names, since they must reflect as much of the object’s functionality as possible. Names will constitute more than 80% of your code, so it matters which ones you work with! It is often discussed whether capital or small letters must be used when coding. The truth is – it depends on your style or on the style of the company you are working for. Most often, professionals will capitalize the SQL keywords and will write objects’ names in a different way. When a name comprises more than one word, such as “purchase number”, words are usually either separated by an underscore or attached to each other, and each word starts with a capital letter. Both approaches are encountered in professional coding. One thing is sure, though – you can’t leave a blank space between words. MySQL will show an error message if you try to do that.

Code Readability

The third facet we will focus on in this post is the readability of your code. On one hand, this regards the horizontal and vertical organization of code, on the other, the color with which words are displayed. Technically, any SQL query can be written on a single line. However, many queries are too long and will become difficult to read if we do that. What needs to be done in such cases is to organize the code, not just horizontally, but also vertically. Depending on their meaning in a query and on the way we want them to be read, words can be written in different colors. SQL keywords are written in blue, objects’ names in black, numbers in orange, and so on.

Maintain Your Code

As we move on in the post, you will develop an impeccable organization of the code you write. For now, remember there are three main ways to maintain your code well: 1. Professionals use ad-hoc software that re-organizes code and colors different words consistently. In a more dynamic coding environment, time will be a factor, and unification of coding style will be a top priority. It is impossible to have 50 programmers in your company, all writing in the same style. It is unprofessional to merge code written in the same language but in a different style. So, when completed, pieces of working code go through the check of such a software, and your boss will have a pile of code all written in the same style. 2. Use the relevant analogical tool provided in Workbench. This little brush beautifies your code. The shortcut key combination to apply this function to the query where your cursor is located is Ctrl and B. You see? Awesome! 3. If you’d prefer to clean your code differently, you should intervene manually and adjust it as you like.

Exercise

In this simple line, we will create a test table with two columns – “Numbers” and “Words”. We’ve written the code on one line with small letters. Notice how MySQL changed the color of keywords, data types, and numbers. Now, we will not be dealing with ad-hoc software, as we are focusing on Workbench. After having placed the cursor somewhere in this query, you can press the little brush icon to reformat the script. To do the same operation faster, remember the Ctrl and B shortcut. Wow! It worked. Keywords are in capital letters, and the data for each column of the table starts on a new line. Great!

Indentation Style

Imagine you have a further preference regarding the horizontal organization of this code and the alignment of all data types. Using the tab button from your keyboard, you can set INT and VARCHAR to begin at the same horizontal point. According to some users, this trick further improves code’s readability. The technical term is called indentation. We say the column names and their data types were indented to the right.

Comments

Another aspect of maintaining good coding style is using comments. These are lines of text that Workbench will not run as code; they convey a message to someone who reads our code. Technically, in MySQL, you can start a comment by typing a forward slash and a star and close it reciprocally with a star and a forward slash. This approach is used mainly for large comments. For one-line comments, you can use either two dashes or one or more hash signs. In addition, to differentiate them from mere code, MySQL Workbench marks all comments in grey.

Execute Code

Finally, I would like to elaborate on the lightning symbol, which helps you execute your code. Let’s start with the fact that your code is separated into blocks, as marked by the semi-colon separator. Ok. So, if you place your cursor on one block and then press the lightning icon or the Ctrl, Shift, Enter combination, SQL will run the selected and all remaining queries. That’s why, if I click on FROM and execute the code this way, I will see the output from the SELECT statement and will drop the test table. If you would like to run just the statement under the cursor, you must press the adjacent icon, where a lightning and a cursor are depicted. The corresponding keyboard combination is Ctrl and Enter. I can promise you will not stop using this keyboard combination, as it allows you to see the output of a certain query quickly, without having to run the entire SQL code. Well… I hope you liked this post! It was written to make you aware of the notion of clean coding and coding in good style. We consider these tools essential for good professional coding, so we would be happy if you can sense you started building good coding style habits. And that’s the end of our SQL tutorials. I hope you’ve enjoyed it and, of course, congratulations! P.S. You can find additional insights into SQL in our explainer videos: Relational Database Essentials, Database vs Spreadsheet, and Basic Database Terminology. What’s more, if you’re considering a career in data science, and you want to be in-the-know of what to expect during a job interview, feel free to check out our article SQL Interview Questions. The article first appeared on: https://365datascience.com/sql-best-practices/