This topic describes the basic guidelines and detailed specifications of SQL coding.

SQL coding guidelines

Note the following guidelines during SQL coding:
  • Make sure that the code is comprehensive.
  • Make sure that code lines are clear, neat, well-organized, and structured.
  • Consider the optimal execution speed during SQL coding.
  • Provide comments whenever necessary to enhance the readability of your code.
  • The guidelines impose non-mandatory constraints on the coding behavior of developers. In practice, understandable deviations are allowed when developers obey general rules.
  • Use lowercase letters for all keywords and reserved words. Keywords and reserved words include SELECT, FROM, WHERE, AND, OR, UNION, INSERT, DELETE, GROUP, HAVING, and COUNT.
  • In addition to keywords and reserved words, other code such as field names and table aliases must be in lowercase.
  • A unit of indentation contains four spaces. All indentations must be the integral multiple of an indentation unit. The code is aligned according to its hierarchy.
  • The SELECT * statement is prohibited. The column name must be specified for all statements.
  • Matching opening and closing parentheses must be placed in the same column.

SQL coding specifications

Note the following specifications during SQL coding:

  • Code header

    The code header contains information such as the subject, description, author, and date. Reserve a line for change log and a title line so that later users can add change records. Note that each line can contain up to 80 characters. The following template is for your reference.

    -- MaxCompute(ODPS) SQL
    -- ** Subject: Transaction
    -- ** Description: Transaction refund analysis
    -- ** Author: Youma
    -- ** Created on: 20170616 
    -- ** Change log:
    -- ** Modified on  Modified by  Content
    -- yyyymmdd  name  comment 
    -- 20170831  Wuma  Add a comment on the biz_type=1234 transaction 
  • Field arrangement
    • Use a line for each field that is selected for the SELECT statement.
    • Reserve one unit of indentation between the word SELECT and the first selected field.
    • Start each of the other field names in a new line with two units of indentation and a comma (,).
    • Place the comma (,) between two fields right before the second field.
    • Place the AS statement in the same line as its corresponding field and keep AS statements of multiple fields in the same column.AS statement
  • Clause arrangement for an INSERT statement

    Arrange the clauses of an INSERT statement in the same line.

  • Clause arrangement for a SELECT statement
    The clauses such as FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, and UNION in a SELECT statement must be arranged according to the following requirements:
    • Use a line for each clause.
    • Make sure that the clauses are left aligned with the SELECT statement.
    • Reserve two units of indentation between the first word of a clause and its content.
    • Keep the logical operators such as AND and OR in a WHERE clause left aligned with the word WHERE.
    • If the length of a clause name exceeds two units of indentation such as ORDER BY and GROUP BY, add a space between the clause name and its content.Over two units of indentations
  • Spacing before and after operators
    Reserve one space before and after each arithmetic operator and logical operator. Keep all the operators in the same line unless the length of the code exceeds 80 characters.Operator
  • Arrangement for a SELECT CASE statement
    The SELECT CASE statement is used to evaluate the value of a variable. Note the following specifications on SELECT CASE statements:
    • Write the WHEN clause one unit of indentation after the CASE statement in the same line.
    • Use a line for each WHEN clause. Wrap a line if the clause is too long.
    • The CASE statement must contain the ELSE clause. The ELSE clause must be aligned with the WHEN clause.ELSE clause
  • Nested query
    Nested queries are often used in implementing the extract, transform, and load (ETL) process of data warehouse systems. The following figure shows an example of arranging nested queries.Subqueries
  • Table alias
    • Once an alias is defined for a table in a SELECT statement, you must use the alias whenever you reference the table in the statement. Specify an alias for each table in the SELECT statement.
    • We recommend that you define the table aliases by using letters in alphabetical order.
    • In the nested query, levels 1 to 4 of SQL statements are named part, segment, unit, and detail respectively, which are abbreviated as P, S, U, and D. You can also use a, b, c, and d to represent levels 1 to 4.
      To differentiate multiple clauses at the same level, add numbers such as 1, 2, 3, and 4 after the letter that represents the level.Alias
  • SQL comments
    • Add a comment for each SQL statement.
    • Use a separate line for the comment of each SQL statement and place the comment in front of the SQL statement.
    • Place the comment of a field right after the field.
    • Add comments to clauses that are difficult to understand.
    • Add a comment to important code.
    • If a statement is long, we recommend that you add comments based on the purposes of each segment.
    • The description for a constant or variable is required. The comment on the valid value range is optional.