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

Coding guidelines

Take note of the following SQL coding guidelines:
  • The code is comprehensive.
  • Code lines are clear, neat, well-organized, hierarchical, and structured.
  • Consider the optimal execution speed during SQL coding.
  • Provide comments whenever necessary in your code 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.
  • All SQL keywords and reserved words must be all uppercase or lowercase, such as select/SELECT, from/FROM, where/WHERE, and/AND, or/OR, union/UNION, insert/INSERT, delete/DELETE, group/GROUP, having/HAVING, and count/COUNT. Do not use mixed-case letters, such as Select or seLECT.
  • A unit of indentation contains four spaces. All indentations must be the integral multiple of an indentation unit. The code is aligned based on 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

Take note of the following SQL coding specifications:
  • Code header

    The code header contains information such as the subject, description, author, and date. Reserve a line for the change log and a title line so that later users can add change records. 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 
    --**************************************************************************
  • Requirements for 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 requirements for an INSERT statement

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

  • Clause arrangement requirements 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 based on the following requirements:
    • Use a line for each clause.
    • The clauses are left aligned with the SELECT statement.
    • Reserve two units of indentation between the first word of a clause and its content.
    • The logical operators and and or in a WHERE clause must be left aligned with the keyword 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.More than two units of indentation
  • Space requirements 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 CASE statement
    The CASE statement can be used to evaluate the value of a field in a SELECT statement. Take note of the following specifications on 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.ELSE clause
    • The CASE statement must contain the ELSE clause. The ELSE clause must be aligned with the WHEN clause.
  • Specifications that are provided to arrange nested queries
    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
  • Conventions that are provided to define table alias
    • After an alias is defined for a table in a SELECT statement, you must use the alias whenever you reference the table in the statement. Therefore, you must specify an alias for each table.
    • We recommend that you define the table aliases by using letters in alphabetical order, and do not use keywords for the aliases.
    • In a nested query, levels 1 to 4 of SQL statements are separately named part, segment, unit, and detail, which are abbreviated as P, S, U, and D. This indicates the hierarchy of the query in multiple levels. 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. Add comments for the table aliases as needed.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 conditional expressions that are difficult to understand.
    • Add a comment to important computations to clarify how they work.
    • If a statement is long, we recommend that you add comments based on the purposes of each segment.
    • The comment for the value of a constant or variable is required. The comment on the valid value range is optional.