SQL anti-Pattern Learning Note 14 about the use of null values

2014-10-14 14:53:25

target : Identify and use null values

anti-pattern : null value as normal value, and vice versa

1. Use NULL in an expression:

Null values are not the same as empty strings, and null values participate in any addition, subtraction, multiplication, and other operations, and the result is null;

Null values are also different from false. And, or, and not three bool operations if the design is null, the result is confusing.

2. The search runs empty columns: Any comparison with nulls returns "Unknown", neither true nor false.

Only is null or is not NULL in the where expression, and no other operation can query the result.

3. You cannot use NULL as a parameter in an incoming query expression.

To avoid these problems, you can use constraints to set columns to disallow null. Stored values must be meaningful content.

how to recognize anti-patterns : May be anti-pattern when the following conditions occur

1. How do I remove a column that has no value (Null)?

2, concatenation of the string with NULL, the result returns null

Rational use of anti-patterns :

Using NULL is not an anti-pattern, and the inverse pattern is to treat null as a normal value or use an ordinary value instead of NULL.

There is a case where null is considered to be a normal value, which is when importing or exporting data.

solution : Treat a null value as a special value　

1. Use NULL in scalar expressions

do =, <>, +, | | Operation, as long as there is a null value, the result is null.

2. Use NULL in Boolean expressions

In a Boolean expression, only: null and False results in false,null or true, and other conditions result in null.

3. Retrieving null values

Using is NULL, is not NULL

4. Declaring NOT NULL column

Sometimes it is possible to avoid null by using the default value, but sometimes it is not possible to do so.

5. Use dynamic default values

Coalesec () and IsNull () functions in SQL Server

SQL anti-Pattern Learning Note 14 about the use of null values