Back to articles list June 18, 2019 - 8 minutes read 24 Rules to the SQL Formatting Standard Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: SQL learn sql SQL basics 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. 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! Tags: SQL learn sql SQL basics You may also like How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more What's the Difference Between SQL and T-SQL? If you’re a SQL newbie and are confused by the difference between SQL and T-SQL, this article will help make things clear. Read more Microsoft SQL Server Pros and Cons In this entry, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from the perspective of a company and a data specialist. Read more Grouping Data in SQL Server Learn how to group data in SQL by using the GROUP BY clause. Includes sample data and SQL queries. Read more The Most Important SQL Queries for Beginners This guide discusses the 30 most important SQL queries for beginners. Let's take a look. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.