As noted in , there is no agreed-upon standard for formatting SQL queries, and any guide for writing code is necessarily subjective. What is true objectively, however, is that your goal should be to communicate what your query is doing.
Formatting is important in order to accomplish this goal. It is not simply a cosmetic concern. Think about how difficult it would be to read text without punctuation, capitalization, paragraphs, indentation, etc. Less dramatically, a SQL query should be crafted so that its intention is clear; clarity should come at the cost of concision (except, of course, if this entails performance issues and the like).
For example, the following is a one-line solution to the first Leetcode database problem, LC 175. Combine Two Tables:
SELECT FirstName, LastName, City, State FROM Person LEFT JOIN Address USING(PersonId);
This is a perfectly legitimate solution, but could it be written more clearly? Of course:
LEFT JOIN Address A ON P.PersonId = A.PersonId;
Adopting your own formatting style will inevitably take some time, but try to be judicious and consistent in the habits you form and the practices you choose. I share below different rules I have adopted that have been heavily influenced by , my everyday development work, and my experience in solving all of the LeetCode database problems.
|Table aliases||Use table aliases that are abbreviations for the table name. It helps to define table aliases without the |
|Column aliases||Use |
|Dot notation||Use dot notation (i.e., the membership operator |
|Consistency||Be consistent in capitalization, in usage of underscores, indentation, etc.|
|Readability||Write the code to be understandable, so you and someone else can read it.|
|Table and column names||Always use only alphanumeric characters and underscores for table and column names. Other characters, such as spaces, require that the name be escaped when referenced. The escape characters, typically double quotes or square braces, make it hard to write and read queries.|
|Plurality of table names||Table names are usually in plural (this helps avoid the problem with reserved words) and reinforces the idea that tables contain multiple instances of the entity.|
|Singularity of primary key column names||The primary key is the singular of the table name followed by "Id." For example, |
|Foreign key and primary key naming consistency||When a foreign key column references another table's primary key column, use the exact same name for both columns, ensuring consistency and making it easy to see relationships between tables.|
|CamelCase||"CamelBack" case is used (upper case for each new word, lowercase for the rest). Hence, |
|Underscore usage||The underscore is used for grouping common columns together. For instance, in a |
|Reserved words||Refrain from using SQL reserved words. Databases have their own special words, but words like |
|Left alignment of high-level clauses||The high-level clauses defined by the SQL language are all aligned on the left. These are |
|Alignment within clauses||Within a clause, subsequent lines are aligned after and (usually) underneath the keyword, so the scope of each clause is visually obvious.|
|Alignment within subqueries||Subqueries follow similar rules, so all the main clauses of a subquery are indented, but still aligned on the left.|
|Alignment within ||Within the |
|Operator spacing||Operators generally have spaces around them.|
|Comma placement||Commas are at the end of a line, just as a human would place them.|
|Parentheses across multiple lines||A closing parenthesis, when on a subsequent line, is aligned under the opening parenthesis.|
As always, rules are made to be broken. The rules above are meant to provide freedom and clarity. They are not meant to be a straightjacket. I try to follow my own rules most of the time, but I occasionally make exceptions (especially when using
UNION ALL for some reason) or slip up (more often than I care to admit). Adopt what you find to be helpful. Leave the rest.
It is nice to have a generally agreed-upon set of rules for formatting SQL queries even if that set of rules only applies to yourself! Consistency helps even if it is just in your own work. Manually trying to enforce consistency is problematic at best though. An automated solution would be much cleaner and efficient. See the blog post on automated SQL query formatting for such a solution.