Русский    English

Using NULL value in the search conditions


  1. IS [NOT] NULL
allows for checking the absence (availability) of value in the table columns. In such cases, using ordinary comparison predicates can lead to incorrect results, because comparing with NULL value evaluates to UNKNOWN.

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

  1. SELECT *
  2. FROM PC
  3. WHERE price IS NULL

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

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

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

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

  1. SELECT ship, CASE launched
  3. THEN 1900
  4. ELSE launched
  5. END 'year'
  6. FROM Outcomes o LEFT JOIN
  7. Ships s ON o.ship=s.name;

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

  1. CASE
  2. WHEN launched = NULL
  3. THEN 1900
  4. ELSE launched
  5. 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:

  1. CASE
  2. WHEN launched IS NULL THEN 1900
  3. ELSE launched
  4. END 'year'
i.e. check on absence of value.

Suggested exercises: 43, 52, 59, 64, 75

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.