Re: How to cope with missing values - NULLS?

pkl_at_mailme.dk (Peter Koch Larsen) writes:

> How to cope with missing values - NULLS?

Simple 3-value Logic Comparison

There was an article, "An Explanation of why three-valued logic is a mistake (Why Accept Wrong Answers?)" by CJ Date on pg. 21 of Database Programming & Design, Dec. 1992,

In the article, Date presents the following example

	Select E#
	FROM EMP
	WHERE JOB='Clerk'
	OR NOT JOB='Clerk';

and states that it will not correctly select for employees whose jobs are "null" (while article states that in the real world, one might assume that all employees would be selected, but NOT JOB='Clerk" is not selecting entries with NULL jobs).

The issue being addressed is that in relational databases all entries MUST have all specified relations. To ease the practical implementation, relational databases will fill-in NULL values for missing information. SQL logic operations are extended with "unknown" states to deal with NULL values.

SQL:

	True
	Unknown
	False

and   T   U   F        or    T   U   F       not
-----------------      ----------------      --------
T     T   U   F        T     T   T   T       T     F
U     U   U   F        U     T   U   U       U     U
F     F   F   F        F     F   U   F       F     T
                   

An alternative 3-value logic

Logic:

	Lo         Falsity.   The stated objective was not met
	DontCare   Don't Care The stated objective may have been met
	Hi	   Truth      The stated objective was met


and   Hi   DC  Lo      or    Hi  DC  Lo      not
------------------     -----------------     ---------
Hi    Hi   Hi* Lo      Hi    Hi  Hi  Hi      Hi    Lo
DC    Hi*  DC  Lo      DC    Hi  DC  Lo*     DC    DC
Lo    Lo   Lo  Lo      Lo    Hi  Lo* Lo      Lo    Hi

• difference in logic operation results compared to SQL.

Effectively, for "and" and "or" logic operations involving a DontCare state, the result is whatever the other state is. A DontCare result only occurs when both states are DontCare.

-- 
Anne & Lynn Wheeler | http://www.garlic.com/~lynn/ 
Internet trivia 20th anv http://www.garlic.com/~lynn/rfcietff.htm