SELECT statement

Predicates II

Predicate

IS [NOT] NULL

So, searching for no-price records in the PC table (typist errors) may be performed with the following operator:

Console

Execute

SELECT *
FROM PC
WHERE price IS NULL

SELECT * FROM PC WHERE price IS NULL

    Scheme

Typical mistake is the using of a predicate in the form of

WHERE price = NULL;

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):

Console

Execute

SELECT ship, launched 
FROM Outcomes o LEFT JOIN 
 Ships s ON o.ship = s.name;

SELECT ship, launched FROM Outcomes o LEFT JOIN Ships s ON o.ship = s.name;

    Scheme

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):

Console

Execute

SELECT ship, CASE launched 
 WHEN NULL 
 THEN 1900 
 ELSE launched 
 END 'year' 
FROM Outcomes o LEFT JOIN 
 Ships s ON o.ship=s.name;

SELECT ship, CASE launched WHEN NULL THEN 1900 ELSE launched END 'year' FROM Outcomes o LEFT JOIN Ships s ON o.ship=s.name;

    Scheme

However nothing has changed. Why? This is because CASE operator used is equivalent to the following:

CASE 
 WHEN launched = NULL 
 THEN 1900 
 ELSE launched 
END 'year'

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:

CASE 
 WHEN launched IS NULL THEN 1900 
 ELSE launched 
END 'year'