24 Rules to the SQL Formatting Standard

Writing queries to a database requires knowledge about SQL syntax, but this is not all you should know. Best practices for writing professional SQL code require good formatting skills. In this article I discuss why this is so important and what are the basic rules you should follow.

Why is it Worthwhile to Format SQL Code?

Beginner SQL programmers often don't pay much attention to formatting their code. If you think formatting is something that can be safely ignored, look at the code below:

SELECT id, FirstName, LASTNAME,c.nAme FROM people p LEFT JOIN cities AS c on c.id=p.cityid;

This SQL query above has been written without using any formatting rules. Now compare that with the formatted query below, which is the same code:

     SELECT p.PersonId,
            p.FirstName,
            p.LastName,
            c.Name
       FROM Person AS p 
  LEFT JOIN City AS c 
         ON p.CityId = c.CityId;

Do you see the difference? Which is more readable? Which query is easier to understand?

Of course it is obvious that the first query is not very easy to read. Aside from this, it is also a problem to make changes in this code fast. If you would like to compare this query to another similar query, it would not be an easy task. The second query is completely different, even though it is exactly the same code—it is easy to read, it would be easy to correct the code, and it would be straightforward to compare with other well-formatted code. Proper formatting of SQL code helps programmers avoid errors.

OK, now you understand why formatting SQL code might be a good idea. Now it's time to learn how to do it.

How to Format SQL Code

There are different ways to approach formatting code. Some SQL programmers have individual styles and preferences for formatting SQL queries. They have experience in programming and follow rules that are convenient for them. This is not bad if you are only working on your own projects, but what if you are working along with other co-workers? Working in a team would be problematic if every programmer were to write code using their own individual style. The code would represent a mixture of rules in one project. The solution would be to lay out a set of principles for the entire team. But then what if the code has to be read or corrected by people outside of the company? The best solution in general is to follow the SQL Formatting Standard. There is not one official document about it, but there are some generally agreed upon standards and good practices written by experts in SQL. In addition, there are many tools that help to format SQL code that are based on this standard. In this guide we discuss common and popular rules that are based on this standard.

Naming Objects

First I discuss general rules about naming database objects. These are the most common rules:

• Avoid the name of a table/column in the plural. It is better use employee instead of employees.
• If the name of the table or column must consist of more than one word, use an underscore to connect them, for example employee_city. Some professionals prefer to use what is called CamelCase style instead, for example EmployeeCity. The preferred style is different for different relational database systems.
• Check that the name is not already used as a keyword in SQL.
• If the name is the same as an SQL keyword, enclose the name within quotation marks.
• The name of an object in a database for a table or a column should be unique and not too long. Avoid special characters in the name like $, &, * , etc. (use only letters, numbers, and underscores).
• Use an underscore in a name only if necessary.
• Don't start the name with an underscore.
• Use comments only if necessary.
• Avoid abbreviations, but, if you do use them, be sure that they will be understood.
• Avoid giving the same name to both a table and a column.
• Use the same naming rules for aliases for columns or tables.
• Include the AS keyword for creating aliases, because this makes the code more readable.
• For the primary key column avoid the name id. A good idea is to combine id with the name of a table, for example: id_employee.

Alignment

Most experts recommend first writing keywords on a new line to the left and then the rest of the code to the right, like this:

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       c.Name
  FROM Person AS p 
  JOIN City AS c 
    ON p.CityId = c.CityId;

Indentation

The liberal use of newlines can really help readability of an SQL query. It is a good idea to use a new line for each separate query and to use a new line for each separate column after a comma. Similarly, it is a good idea to use spaces to surround the equals operator, to use spaces before or after apostrophes, and to use a space after a comma.

The sections below present more details about good practices in indentation in different types of SQL queries.

Commenting

Avoid writing too many comments in the code. Of course, there are cases where comments are necessary, but it is usually better to use multiple-line comments which are indicated by /* opening and */ closing characters. It is recommended to write this type of comment at the start of a new line, instead of starting on a line with code that is executed. The comment should be written above the relevant SQL code line, using the same indentation. For example:

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       /* Name column is the name of the city: */
       p.Name,
  FROM Person AS p 
 WHERE p.Name = 'New York';

In SQL code it is also possible to add one line comments. This type of comment is indicated by a double hyphen (--) at the beginning of the comment text. All text after these characters is treated as a comment.

SELECT -- we have to delete this column p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name
  FROM Person AS p;

SELECT Queries

In this type of query SELECT is the first word in the command. If there are many columns after SELECT, it is better to separate them by placing each on a separate line. Each new line should be indented. Make sure to place commas at the end of the line and not at the beginning of the line.

SELECT p.PersonId,
       p.FirstName,  
       p.LastName,
       c.Name
  FROM Person AS p;

For the keywords FROM, WHERE, ORDER BY, GROUP BY, and HAVING, write each on a new line without indentation.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name,
  FROM Person AS p 
 WHERE p.Name = 'New York';

If the WHERE statement has more than one condition, separate each condition by a new line that is indented, and use a new indented line with the AND or OR conditional operators within the WHERE statement.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name
  FROM Person AS p 
 WHERE p.Name = 'New York' 
    OR p.Name = 'Chicago';

JOIN Statements

If you join tables, use new lines for the operators INNER JOIN, LEFT JOIN, etc. For the ON operator, write a new indented line within the JOIN statement. If, however, there is more than one condition, use a new indented line before the AND or OR conditional operator.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       c.Name
  FROM Person AS p 
  JOIN City AS c 
    ON p.CityId = c.CityId;

A Long and Nested SQL Query

Long queries sometimes contain subqueries. In this situation the subquery should be on a new indented line.

For the CASE structure place each WHEN and END on a new line.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       CASE 
         WHEN p.Age < 18 THEN 'below 18'
         WHEN p.Age >= 18 THEN '18 or more'
       END AS Age
  FROM Person AS p;

Other Types of SQL Queries

There are similar rules for queries that modify, insert, or delete data.

Use indent for VALUES in insert queries:

INSERT INTO Car(id_car, name, year) VALUES
  (1, 'Audi', 2010) ; 

In the case where you insert more rows in one query, write every row as a new line with indentation:

INSERT INTO Car(id_car, name, year) VALUES
  (1, 'Audi', 2010) ,
  (2, 'Skoda', 2015) ; 

In a similar way, in an UPDATE query use SET and WHERE as in a SELECT statement, with a new line without indentation:

UPDATE Car
SET year = 2012
WHERE Name = 'Audi';

or in a DELETE query:

DELETE FROM Car
WHERE Name = 'Audi'; 

How Bad Formatting of SQL Code Leads to Problems

One example of how poor formatting leads to problems can be seen in the query below, in which commas are placed at the beginning of each line:

SELECT /* we have to delete this column */ p.PersonId
     , p.FirstName
     , p.LastName
     , p.Name
  FROM Person AS p 
 WHERE p.Name = 'New York';

This might make sense at first, but if you comment out the first column in order to remove it, then the query would return an error.

Another error can occur if you don't use indentation and new lines. For example:

Select person.id_person, person.name, person.age, person.description, person.city from person  where person.age>20 and person.city = ( select name from city where id_city>20)

In this poorly formatted code it would be very easy by mistake to delete the WHERE clause in the subquery when you intended to delete the WHERE clause in the main query.

Many problems will be easy to find if the query is properly formatted, especially in a long query with hundreds of lines of code. Now the question here is how you can easily format the code. For this, an online SQL formatter can be a handy option. All you need to do is just paste the entire code, hit the “format” button and the tool will automatically format it.

Summary

Ignoring the SQL formatting standard can cause significant problems when you are collaborating with other programmers. Proper formatting helps your SQL code be easier to read and helps prevent errors when making changes to your code. In this article I presented some of the rules recommended by experts which help you in writing clearer code. Writing beautiful SQL code is a good working habit valued by employers. Your code indicates your level of professionalism and shows that you take a modern, serious approach to work. Rise to the challenge and become a more professional programmer!