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

Coding guidelines

When you write SQL code, take note of the following guidelines:
  • The code is comprehensive.
  • Code lines are clear, neat, well-organized, and structured.
  • The optimal execution speed is considered during SQL coding.
  • Comments need to be added if necessary to enhance the readability of your code.
  • The guidelines impose non-mandatory constraints on the coding behavior of developers. In practice, reasonable deviations are allowed if developers obey general rules.
  • All SQL keywords and reserved words must be entirely 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, or count/COUNT. Do not use mixed-case letters, such as Select or seLECT.
  • One unit of indentation contains four spaces. All indentations must be an integral multiple of one indentation unit. The code is aligned based on its hierarchy.
  • The SELECT * statement is prohibited. A column name must be specified in all statements.
  • Matching opening and closing parentheses must be placed in the same column.

SQL coding specifications

When you write SQL code, take note of the following specifications:
  • Code header

    The code header contains information such as the subject, description, author, and date. Reserve a line for change logs and a title line so that users can add change records in the future. Each line can contain a maximum of 80 characters. The following code provides an example template:

    -- 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 field name in a new line with two units of indentation and a comma (,).
    • Place a comma (,) between two fields right before the second field.
    • Place the AS statement in the same line as its matching 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 in compliance with 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, add a space between the clause name, such as ORDER BY and GROUP BY and its content. Clause name exceeds two units of indentation
  • 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. Take note of 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 single line for each WHEN clause. Wrap the line if the clause is excessively long. ELSE clause
    • The CASE statement must contain the ELSE clause. The ELSE clause must be aligned with the WHEN clause.
  • Nested query
    Nested queries are often used to implement the extract, transform, load (ETL) operations of data warehouse systems. The following figure shows an example of arranging nested queries. Subqueries
  • Table alias
    • If 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 in alphabetical order, and do not use keywords for the aliases.
    • In a nested query, levels 1 to 4 of SQL statements are named part, segment, unit, and detail, 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. Add comments to the table aliases as needed. Alias
  • SQL comments
    • Add a comment to each SQL statement.
    • Use a separate line for the comment of each SQL statement and place the comment before 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.
    • To add a comment to an SQL statement, place the mouse cursor at the end of the SQL statement and press Ctrl+/ or cmd+/. To comment on multiple lines of code, you can select the code on which you want to comment and press Ctrl+/ or cmd+/.
      Note
      • If your computer runs a Windows operating system, press Ctrl+/ to add a comment to an SQL statement.
      • If your computer runs a macOS, press cmd+/ to add a comment to an SQL statement.