The "Fear of the Unknown" in SQL anti-pattern

A discussion of NULL

1. Select A | | B from table;

If field A or B has a null, then a| | b result is null;

2. Use of NULL (to identify dangling values):

(1) Replace the indeterminate value

(2) Substituting a value that may not be meaningful or inappropriate

(3) Return value when an invalid parameter is passed in

(4) Non-matching column placeholders when querying outside

3. For most databases, NULL is a special value, different from 0, false, or an empty string, but in Oracle and Sybase, NULL is an empty string of length 0.

4, null value in scalar expression

An expression	Actual value	Reason
Null=0	Null	Null is not 0
null=12345	Null	If no value is specified and the given value is equal, the unknown
null<>12345	Null	Not Equal is unknown
null+12345	Null	No value specified and always number of seats and unknown
null| | " String	Null	Null is not an empty string, unknown
Null=null	Null	Unspecified value and another value equal are unknown
Null<>null	Null	If the difference is unknown

5, null value in Boolean expression

An expression	Actual value	Reason
NULL and TRUE	Null	Null is not false
NULL and FALSE	FALSE	For and, a fake is false
NULL OR FALSE	Null	Null is not true
NULL OR TRUE	TRUE	For OR, a true
Not (NULL)	Null	Null is not true, nor is flase

6. Adding is null in the Where condition or is not NULL causes the query to discard the index, increasing the query time, and NULL is originally designed as a dangling value

The "Fear of the Unknown" in SQL anti-pattern