SQL Best Practices: How to Type Code Cleanly and Perfectly Organized

Join over 2 million students who advanced their careers with 365 Data Science. Learn from instructors who have worked at Meta, Spotify, Google, IKEA, Netflix, and Coca-Cola and master Python, SQL, Excel, machine learning, data analysis, AI fundamentals, and more.

Start for Free
Martin Ganchev Martin Ganchev 10 May 2024 14 min read

In 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.

We've also developed a variety of flashcard decks to assist you in your SQL learning journey. These cover the basics of databases and this essential query language. Check them out here: Foundations of Databases and SQL Flashcards and SQL Data Types, Relationships, Keys, and Constraints Flashcards.

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.

Assigning Names, sql best practices

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.

Capitalize the SQL keywords, sql best practices

Objects’ Names, sql best practices

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.

Separate words in a name, sql best practices

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.

Create table, sql best practices

companies, sql best practices

255, sql best practices

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!

Reformat the SQL script

create table

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”.

Numbers 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.

ctrl + B

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.

Indentation

The technical term is called indentation. We say the column names and their data types were indented to the right.

Code 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.

Comments 1

Execute Code

Finally, I would like to elaborate on the lightning symbol, which helps you execute your code.

Execute your code

Let’s start with the fact that your code is separated into blocks, as marked by the semi-colon separator. Ok.

Code Blocks

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.

ctrl shift enter

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.

use sales

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.

ctrl 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 tutorials (Relational Database Essentials, Database vs Spreadsheet, and Basic Database Terminology) and SQL course. 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. and our SQL for Data Science Interviews course.

Ready to take the first step towards a career in data science?

Check out the complete Data Science Program today. We also offer a free preview version of the Data Science Program. You’ll receive 12 hours of beginner to advanced content for free. It’s a great way to see if the program is right for you.  

Martin Ganchev

Martin Ganchev

Instructor at 365 Data Science

Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.

Top