SQL coding guidelines and specifications-DataWorks(DataWorks)-阿里云帮助中心

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

Coding guidelines

Follow these guidelines when you write SQL code:

Ensure your code is functionally complete.
Write clear, organized code with a logical structure.
Write code that is optimized for execution speed.
Add comments to improve code readability.
These specifications are guidelines, not strict rules. Deviations are acceptable if they do not violate core requirements.
Use a consistent case for every SQL keyword and reserved word, 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 mix cases, for example, Select or seLECT.
Use four spaces as one indentation unit. All indents must be a multiple of this indentation unit and aligned according to the code's hierarchy.
Do not use select *. Always specify column names explicitly.
Align matching parentheses in the same column.

SQL coding specifications

Follow these specifications when you write SQL code:

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
- Place each field selected by the SELECT statement on its own line.
- Indent the first field one indentation unit.
- For subsequent fields, start on a new line, indent by two indentation units, and add a leading comma before the field name.
- Place the comma that separates two fields immediately before the second field.
- Place the AS keyword on the same line as its corresponding field. For readability, align the AS keywords for multiple fields in the same column.
```
select  channel_id                as channel_id
       ,trade_channel_desc        as trade_channel_desc
       ,trade_channel_edesc       as trade_channel_edesc
       ,inst_date                 as inst_date
       ,trade_iswap               as trade_iswap
       ,channel_type              as channel_type
       ,channel_second_desc       as channel_second_desc
from   (...)
```
INSERT clause arrangement

Arrange the clauses of an INSERT statement in the same line.
SELECT statement arrangement
The from, where, group by, having, order by, join, and union clauses in a SELECT statement must follow these rules:
- Start each clause on a new line.
- Left-align each clause with the SELECT keyword.
- After the first word of a clause, indent the subsequent code by two indentation units.
- Left-align logical operators in a WHERE clause, such as and and or, with the WHERE keyword.
- For multi-word clauses, such as order by and group by, place the subsequent code on the same line, separated by a single space.
```
select    trim(channel) channel
          ,min(id)      id
from      ods_trd_trade_base_dd
where     channel is not null
and       dt = ${tmp_uuuummdd}
and       trim(channel) <> ''
group by  trim(channel)
order by  trim(channel)
```

Operator spacing

Place a single space before and after each arithmetic and logical operator. In the following example, a space is placed before and after the <> operator:

select      trim(channel) channel
            ,min(id)      id
from        ods_trd_trade_base_dd
where       channel is not null
and         dt = ${tmp_uuuummdd}
and         trim(channel) <> ''
group by    trim(channel)
order by    trim(channel)

Writing CASE statements
Use a CASE statement within a SELECT statement to conditionally evaluate field values. Format CASE statements as follows:
- Place the WHEN clause on the same line as the CASE statement, indented by one unit.
- Write each WHEN clause on a single line if possible. You can wrap the line if the statement is long.
```
,case  when p1.trade_from = '3008' and p1.trade_email is null then 2
       when p1.trade_from = '4000' and p1.trade_email is null then 1
       when p9.trade_from_id is not null then p9.trade_from_id
 end                                as trade_from_id
,p1.trade_email                     as partner_id
```
- A CASE statement should include an ELSE clause. Align the ELSE clause with the WHEN clauses.

Nested query specifications

Format nested subqueries as shown in the following example. They are frequently used in data warehouse ETL development.

select    p.channel
          ,rownumber()   order_id
from      (
          select    s1.channel
                    ,s1.id
          from      (
                    select  trim(channel)        as channel
                            ,min(id)             as id
                    from    ods_trd_trade_base_dd
                    where   channel is not null
                    and     dt = ${tmp_yyyymmdd}
                    and     trim(channel) <> ''
                    group by trim(channel)
                    ) s1
          left outer join
                    dim_trade_channel s2
          on        s1.channel = s2.trade_channel_edesc
          where     s2.trade_channel_edesc is null
          order by id
          ) p
;

Table alias conventions
- When you define a table alias, you must use it for all references to that table. For consistency, assign an alias to every table in your statement.
- Use simple characters for table aliases. We recommend naming them sequentially (for example, a, b, and c) and avoiding keywords.
- For multi-level nested subqueries, aliases must reflect the hierarchy. Name aliases for SQL statements by level, using P (Part), S (Segment), U (Unit), and D (Detail) to represent levels one through four, respectively. Alternatively, you can use a, b, c, and d.
  To distinguish between multiple clauses at the same level, append a number such as 1, 2, or 3 to the letter. Add comments to table aliases as needed. In the following SQL statement, the outer subquery alias is p (Part, level 1) and the inner subquery alias is s1 (Segment, level 2), which demonstrates this convention:
```
select    p.channel
          ,rownumber()   order_id
from      (
          select    s1.channel
                    ,s1.id
          from      (
                    select  trim(channel)        as channel
                            ,min(id)             as id
                    from    ods_trd_trade_base_dd
                    where   channel is not null
                    and     dt = ${tmp_yyyymmdd}
                    and     trim(channel) <> ''
                    group by trim(channel)
                    ) s1
          left outer join
                    dim_trade_channel s2
          on        s1.channel = s2.trade_channel_edesc
          where     s2.trade_channel_edesc is null
          order by id
          ) p
;
```
SQL comments
- Add a comment to explain each SQL statement.
- Place the comment for an SQL statement on a separate line before the statement.
- Place comments for fields immediately after the field.
- Add comments to explain complex conditional expressions.
- Add comments to explain the function of important calculations.
- For long functions, break down the statement into functional segments and add comments to explain each part.
- Comments for a constant or variable must explain its meaning. You can also include the valid range of values.
- To comment out a line, place your cursor on the target SQL statement and press Ctrl+/ or Cmd+/. To comment out multiple lines, select the statements and press Ctrl+/ or Cmd+/.
  Note
  - On Windows, use only Ctrl+/ to comment out SQL statements.
  - On macOS, use only Cmd+/ to comment out SQL statements.