Formatting

DDC Style Guide: SQL

Guidelines for SQL statements in .sql files and embedded in Java annotations.

Application

Our style guide does not fully specify the allowed or required formatting of SQL code across multiple lines. In particular, as noted below, single-line statements are permitted in some contexts; in such cases, the conventions for new lines and indentation are moot. Nonetheless, the rules specified do apply, along with the naming and casing stated in previous pages, with any conditions as stated. Beyond that, consistency is critical: Apply the same formatting rules throughout a project.

Code formatting tools

  1. In this bootcamp, the most important guideline to follow for SQL code formatting—apart from following the naming and casing rules, of course—is simple: Use a formatting tool! This might be the IntelliJ Code/Format Code command, or any of a number of other SQL code formatting tools, including those listed in the SQL section of Formatting tools reference section of this document.

    Some of these tools (including the one provided by IntelliJ) are dialect-specific: the appropriate dialect of SQL must be configured in order to format the code properly. In most cases, formatting works much better if the SQL code is syntactically correct for the chosen dialect; for the IntelliJ SQL formatter, this is required.

Single-line statements

In some contexts, even a reasonably complex SQL statement may be written in a single line. In the single-line form of a statement, the rules for newlines and indentation (below) don’t apply.

  1. A SQL statement used as the value argument of a Room or Spring Data @Query annotation may be written in a single line, directly in the annotation. However, if the statement has a multi-table/view FROM clause, consider writing it in multiple lines (concatenated as needed) as a static final String field, and then referencing that constant in the value argument of the @Query annotation.

  2. A subquery (correlated or not) within another statement may be written in a single line, in parentheses. However, you are encouraged to split the subquery into multiple lines, indented as appropriate—especially if the subquery involves multiple tables, views, or subqueries.

New lines

  1. The following keywords (and keyword combinations) must start a new line (with the exceptions noted for single-line statements, above):

    • ADD
    • ALTER
    • CREATE
    • DECLARE
    • DELETE
    • DROP
    • ELSE
    • END, when used to close a block that starts with BEGIN.
    • FROM
    • GROUP BY
    • HAVING
    • IF
    • INSERT
    • [JOIN_TYPE]JOIN , where JOIN_TYPE (if present) is one of INNER, LEFT, RIGHT, FULL OUTER, CROSS, NATURAL, etc.
    • MODIFY
    • ORDER BY
    • SELECT
    • SET
    • TRUNCATE
    • UPDATE
    • VALUES
    • WHERE

  2. The second, and every subsequent, column expression in a SELECT column list must start a new line. The first column expression should start a new line.

  3. When multiple tables are includes in the FROM clause of a SELECT statement, and the JOIN keyword is not used to declare join conditions, each table name after the first must start a new line. (Before the adoption of the JOIN keyword, this form was commonly used with a join condition expressed in the WHERE clause; avoid this practice.)

  4. Every column, table constraint, or primary key definition in a CREATE TABLE statement must start a new line. Column-level constraints, and the PRIMARY KEY modifier on a column definition, should be on the same line as the column definition.

  5. The second, and every subsequent, column assignment following SET in an UPDATE statement must start a new line. The first column assignment should start a new line.

  6. The closing brace of a CREATE TABLE statement must start a new line.

  7. The opening brace of a CREATE TABLE statement should not (but is permitted to) start a new line.

  8. In the Boolean expression of the WHERE or HAVING clause, the AND, OR, and NOT conjunction operators should start a new line.

  9. Between CASE and the matching END, each WHEN and ELSE should start a new line. If this is done, the END must start a new line.

Indentation

In most contexts, indentation refers to the spacing between the absolute start of a line and the first non-white-space character, with the implicit understanding that items indented to the same level are aligned along the left edge of the text. However, this is not always the case in SQL: Some style guides and formatting tools use indentation to produce a consist alignment of the right edge of keywords (or the first words in keyword phrases) Either of these indentation approaches is acceptable; favor consistency over convenience.

Regardless of your left vs. right alignment preference, follow these rules:

  1. Indentation must use space characters instead of tabs.

  2. The keywords starting the clauses of a SQL statement (e.g., FROM, WHERE, etc. in a SELECT statement) must be indented to the same level as the statement itself.

    As stated above, this does not necessarily mean that the clause and statement keywords are vertically aligned at the start (left-most character) of the keyword. Many SQL style guides and formatting tools dictate a right alignment of keywords, creating a river—a vertical column of whitespace after the keywords. So the following 2 forms are both acceptable.

    Left-aligned:

     SELECT
     ...
 FROM
     ...
 WHERE
     ...
 GROUP BY
     ...
 HAVING
     ...
 ORDER BY
     ...

    Right-aligned

     SELECT
     ...
   FROM
     ...
  WHERE
     ...
  GROUP BY
     ...
 HAVING
     ...
  ORDER BY
     ...

    Note that in the second form, the end of each clause keyword (or the first word in a keyword phrase) is aligned. This is not required, but it is a fairly common practice in SQL formatting, and is allowed.

  3. When a new line is added to a clause of a SQL statement—e.g., for the second and subsequent columns in the column list of a SELECT statement, or a JOIN in the FROM clause of a SELECT statement—the new line must be indented further to the right of the indent level of the clause itself.

    Here’s an example SELECT statement with a JOIN, demonstrating this rule:

     SELECT
     a.article_id,
     a.title,
     up.name
 FROM
     article AS a
     JOIN user_profile AS up
         ON up.user_id = a.author_id;

  4. The column, table constraint, and primary key definitions of a CREATE TABLE statement are contained within must be indented within the curly braces of the statement.

  5. For BEGIN…END blocks, the END keyword must be indented to the same level as the matching BEGIN.

  6. For CASE…END blocks that extend over multiple lines, the END keyword must be indented to the same level as the matching CASE.

  7. All of the WHEN and ELSE keywords in a multiline CASE statement must be indented to the same level, and to the right of the enclosing CASE and END.

  8. All statements contained within BEGIN and END must be indented to the same level, and to the right of the BEGIN and END.

Vertical whitespace

  1. When a SQL file contains multiple statements, every semicolon (;) statement terminator must be followed by at least (and preferably exactly) one 1 blank line.

  2. If the number of blank lines used between any 2 statements is greater than 1, the vertical spacing between all pairs of vertically adjacent statements in the file must be predictable and consistent.

  3. If needed for legibility or clarity, lines of code within a statement may be broken up by vertical whitespace; however there must not be more than 1 blank line separating groups of 1 or more non-blank lines each.