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