SQL anti-pattern: "fear of unknown", SQL anti-Pattern

SQL anti-pattern: "fear of unknown", SQL anti-Pattern

NULL

1. select A | B from table;

If one of the fields A or B is null, the result of A | B is null;

2. Use of NULL (identify the suspended value ):

(1) Replace uncertain values

(2) Replace a value that may be meaningless or inappropriate

(3) return value when an invalid parameter is input

(4) non-matching column placeholders during external queries

3. For most databases, Null is a special value, different from 0, false, or Null String. However, in oracle and sybase, Null is a Null string with a length of 0.

4. null value in a scalar expression

Expression	Actual value	Cause
NULL = 0	NULL	NULL is not 0
NULL = 12345	NULL	Unknown if the unspecified value is equal to the given value
NULL <> 12345.	NULL	Unknown if not equal
NULL plus 12345	NULL	Unknown if no value or number of consecutive periods are specified
NULL | "string"	NULL	Null is not an empty string and is unknown.
NULL = NULL	NULL	Unknown if the unspecified value is equal to another value
NULL <> NULL	NULL	Unknown if different

5. null value in a Boolean expression

Expression	Actual value	Cause
NULL AND TRUE	NULL	NULL is not false
NULL AND FALSE	FALSE	For and, false
NULL OR FALSE	NULL	Null is not true
NULL OR TRUE	TRUE	True or
NOT (NULL)	NULL	Null is neither true nor flase.

6. Adding is null or is not Null in the where condition will cause the query to stop using the index and increase the query time. The original intention of Null is to use it as a vacant value.