Code style

Often, SQL queries can be quite long and complicated. In this case, we should remember about code styling. Code styling is one of the most important things in software development. If a code has a good style it can always be easily understood and supported by other developers. Developers, who know how to write clean and readable code are always in demand. In this topic, we will talk about code styling in SQL, good practices in writing queries, and other things that every SQL developer should know.

Naming

First things first, we will talk about good practices in naming in SQL. In SQL we can give names to everything: tables, columns, aliases, functions, etc. Naming something we should remember a few rules:

1. Names should be meaningful. A column or a table name should reflect the content of this column or table. Here are the examples:

Bad style:

CREATE TABLE A (
    b VARCHAR(50)
);

Good style:

CREATE TABLE students (
    first_name VARCHAR(50)
);

2. Names should be readable. Names should be easy to read. They shouldn't be too long or too complicated. Also, remember to use underscore to enhance the readability of the names.

Bad style: ColumnThatStoresTheNameOftheStudent

Good style: first_name

3. Use specific names. You can view this as an addition to the first rule. Names should be precise and specific. Use synonyms of words instead of their plural, use words without multiple meanings, use well-known words so other people could understand them correctly.

Bad style: employees (the plural of the word may not be noticed and is easy to confuse with employers)

Good style: staff

Spaces and newlines

When SQL queries are getting longer, it is important to separate them using newlines and spaces to enhance the readability of the code. But you also need to know where to start a new line or use spaces.

You can and should use spaces to align table names, columns by their right margin:

SELECT 
    first_name, 
    last_name, 
    birth_year, 
    specialty
FROM 
    students
WHERE 
    first_name = 'John'
    AND last_name = 'Doe'
    AND birth_year = '1999'
GROUP 
    BY first_name

Note that the keywords like SELECT, FROM, etc. are left-aligned, while column names and various conditions are right-aligned. And, of course, you should not write a long query in one line.

Let's take a look at a bad styling example:

SELECT first_name, last_name, birth_year, speciality FROM students 
WHERE first_name = 'John' AND last_name = 'Doe' AND birth_year = '1999'

Don't you agree that this query is very hard to read and difficult to understand?

In addition, try to include spaces:

• before and after the equal sign (=)

• after commas (,).

Moreover, it is important to mention that the logical operators (AND, OR) should always be at the beginning of the line:

WHERE
    author = 'Stephen King'
    AND book_title = 'The Shining'

They shouldn't be at the end of the line:

WHERE
    author = 'Stephen King' AND
    book_title = 'The Shining'

It may look very insignificant, but it is also important and it makes your queries more readable.

Basic guidance

In this part of the topic, we will discuss some small basic styling advice in SQL.

1. Sometimes you can find SQL code where keywords are written in the lowercase: select * from books. We recommend writing them in uppercase to make it easier to navigate the lines of code: SELECT * FROM books.

2. Use multi-line comments when needed, do not use many single-line comments. And do not explain intuitive queries with comments.

Bad style:

-- Multi-line
-- and complicated
-- comment

Good style:

/* Multi-line
and complicated
comment */

3. Try to use only common abbreviations.

4. Make sure that the table name does not match the names of any of its columns.

5. Try not to use redundant prefixes like sp_ or tbl.

Conclusion

In this topic, we have discussed some necessary styling guides in SQL. You need to remember that you might not be the only one reading your beautiful code. Maybe, one day some developer will thank you for the readable and clean code that you wrote some time ago.