Using NULL value in the search conditions
So, searching for no-price records in the PC table (typist errors) may be performed with the following operator:
Typical mistake is the using of a predicate in the form of
This predicate results in empty result set, even if there are products with unspecific price. It occurs because comparison with NULL value according to the comparison predicate is always estimated as UNKNOWN. Whereas a row gets in result set only when a predicate in a WHERE clause is evaluated as TRUE. The same is fair for a predicate in HAVING clause also.
The similar, but not so obvious, mistake arise when comparison with NULL takes place in a CASE operator (see Chapter 5.10). To clarify this mistake, let's consider the following problem: «Determine launched year of the ships from Outcomes table . If year is unknown, take 1900».
As year of descent to water (launched column) is in Ships table , it is necessary to use the left joining (see Capter 5.6):
For the ships which are absent from Ships, the launched column will contain NULL value. Now we shall try to replace this value with value 1900 by means of CASE operator (see Chapter 5.10):
However nothing has changed. Why? This is because CASE operator used is equivalent to the following:
And here we receive comparison with NULL value and, as a result, we get UNKNOWN truth value that leads us to use of ELSE branch, and all remains as well as before. The following code will be correct: