Skip to content

SQL Rules

SQL Rules

Do not use count(column) or count(constant) instead of count() - [Mandatory]

💡 count(*) will count the rows with NULL values, while count(column) will not count the rows with NULL columns.

count(distinct col) calculates the number of non duplicates of the column except NULL - [Mandatory]

💡 Note that count(distinctcol1, col2) if one column is all NULL, it will return 0 even if the other column has different values.

When the values of a column are all NULL, the return result of count(column) is 0, but the return result of sum(column) is NULL - [Mandatory]

💡 Pay attention to NPE when using sum()

Use ISNULL() to determine whether it is a NULL value - [Mandatory]

💡 Note that a direct comparison of a NULL value with any value is NULL

When writing paging query logic in code, if count is 0, it should be returned directly to avoid executing subsequent paging statements - [Mandatory]

Foreign keys and cascading are not allowed. All foreign key concepts must be solved in the application layer - [Mandatory]

💡 only suitable for Business logic related procedures. Non-business logic related procedures are still allowed, for example: scheduled jobs / procedures to timely delete partitions.

During data revision, when deleting and modifying records, you should first select to avoid accidental deletion, and the update statement can be executed only after confirmation - [Mandatory]

in clause should be avoided. Record set size of the IN clause should be evaluated carefully and control it within 1000, if it cannot be avoided - [Recommend]

💡 if in clause leads to index invalidation, then don’t use in. - [Mandatory]

Characters should be represented and stored with UTF-8, and be cautious of character number counting - [Recommend]

💡 remove precondition on i18n case.

💡 It must be considered clearly before use.