Syntax Specification

About NULL

• Description: NULL judgment: IS NULL, IS NOT NULL.

• Description: Beware of boolean types taking the values true, false, NULL.

• Description: Beware of NOT IN collections with NULL elements.

• Recommendation: Use count(1) or count(*) to count rows, but not count(col) to count rows, because NULL values will not be counted.

• Rule: When count(multi-column names), the multi-column names must be enclosed in parentheses, e.g. count( (col1,col2,col3) ).

• Note: With multi-column count, the row is counted even if all columns are NULL, so the effect is the same as count(*).

• Note: count(distingu col) counts the number of non-NULL non-repeats of a column, NULL is not counted; count(distingu (col1,col2,...) ) counts the unique values of multiple columns, NULL is counted, while NULL and NULL are considered the same.

• Note: Distinction between count and sum of NULL

  select count(1), count(a), sum(a)  from (SELECT * FROM (VALUES (NULL), (2) ) v(a)) as foo where a is NULL;
  count | count | sum
  -------+-------+-----
     1 |   0 |
  (1 row)

• Check whether two values are the same (NULL is considered as the same value).

  select null is distinct from null;
  ?column?
  \---------
  f
  (1 row)
  
  select null is distinct from 1;
  ?column?
  \---------
  t
  (1 row)
  
  select null is not distinct from null;
  ?column?
  \---------
  t
  (1 row)
  
  select null is not distinct from 1;
  ?column?
  \---------
  f
  (1 row)

About Invalid Indexes

• During SQL statement writing, functions and expressions are usually used in query operations. It is not recommended that functions and expressions are used in condition columns. Using a function or expression in a condition column will make indexes of the condition column unused, thereby affecting the SQL query efficiency. It is recommended that functions or expressions are used in condition values. For example,

  select name from tab where id+100>1000;

  This statement can be changed to the following:

  select name from tab where id>1000-100;

• Do not use left fuzzy query. For example,

  select id from tab where name like '%ly';

• Do not use the negative query, such as not in/like. For example,

  select id from tab where name not in ('ly','ty');

Ensuring That All Variables and Parameters Are Used

• Declare-variable also generates certain system overhead and makes code look loose. If some variables are not used in compilation, they will report alarms. Make sure that no any alarm is reported.