A Guide to SQL Query Formatting
SQL coding style isn’t just a matter of aesthetics. Well-structured and understandable code will make your work much easier, primarily because it’ll be understandable to both you and your colleagues. Imagine opening an old query a couple of months later. Will you be able to quickly figure out exactly what you wrote? In this article, we’ll look at how to write SQL so it’s clean, neat, and easy to read.
Naming objects
Let’s start with the basics for comfortable working with the database:
- Avoid plural names.
- To combine words, use underscores: employee_city or CamelCase style: EmployeeCity.
- Do not use special characters such as $, &, *, etc. in the name.
- Don’t start your name with an underscore.
- Don’t give the same name to both the table and the column.
- Avoid abbreviations.
- Include the AS keyword to create “aliases”.
- Avoid using “id” as the primary key name. It’s better to combine “id” with the table name, for example: “id_employee.”
SELECT statement
- It’s best to write query keywords (
SELECT,FROM,WHERE,ORDER BY,GROUP BYandHAVING) at the beginning of a new line. IfSELECTattributes follow, it’s best to separate them by placing each on a separate line with equal indentation. - Be sure to place commas at the end of a line, not at the beginning of the next one.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
PersonAS p;
WHERE operator
If a statement WHEREhas more than one condition, place each condition on a new line, starting with the conditional ANDor statements OR.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
Person AS p;
WHERE
p.Name = 'New York'
OR p.Name = 'Chicago';
JOIN operator
If you’re joining tables, write the operators INNER JOIN[ , ] LEFT JOINand so on on a new line. ONIt’s also best to move the operator to a new line.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
Person AS p;
JOIN
City AS c
ON p.CityId = c.CityId;
Commenting
It’s best to avoid writing too many comments in your code. If they are necessary, use multi-line comments: /* text */. The comment should be written above the corresponding line of code.
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';
Nested SQL query
If a query contains a subquery, it should be written on a new line and indented. For structure, CASEeach expression should WHENalso be placed ENDon a new line.
SELECT
p.FirstName
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 queries
Similar rules apply to queries that modify, insert, or delete data.
Write the keyword VALUESin input prompts at the beginning of a new line and wrap each tuple onto a new line with an indent:
INSERT INTO Car(id_car, name, year)
VALUES
(1, 'Audi', 2010),
(2, 'Skoda', 2015);
In the query, UPDATEthe keywords SETand WHERE, as in the query SELECT, are more convenient to wrap to a new line without indentation:
UPDATE
Car
SET
year = 2012
WHERE
Name = 'Audi';
Or in the request DELETE:
DELETE FROM
Car
WHERE
Name = 'Audi';
Conclusion
Writing SQL beautifully isn’t just a matter of aesthetics; it’s the key to success in database work. Maintaining a consistent style helps avoid chaos, speeds up work, and makes code accessible to everyone who encounters it. We hope you now have a clearer understanding of how to format SQL queries so that you can easily understand them six months from now.