Posted by Ed Murphy on 04/08/07 21:33

--CELKO-- wrote:

>>> It seems like dropping UNKNOWN would leave a sensible set of rules:
>
> and | T N F or | T N F not |
> ----+------ ---+------ ----+--
> T | T N F T | T T T T | F
> N | N N F N | T N N N | N
> F | F F F F | T N F F | T
>
> Am I overlooking anything? <<
>
> The NULL propagation rule.
>
> and | T N F or | T N F not |
> ----+------ ---+------ ----+--
> T | T N F T | T N T T | F
> N | N N N N | N N N N | N
> F | F N F F | T N F F | T
>
> This means that TRUE OR NULL = NULL, etc. and you can now prove that
> TRUE = FALSE. The UNKNOWN logical value does not have this behavior
> and that is why we have it.

NULL represents the concept "unknown" in all other contexts; it should
represent it in the context of the Boolean data type as well.

IINM, while SQL doesn't have a mandatory Boolean *type*, it already
follows TRUE OR NULL = TRUE and FALSE AND NULL = FALSE in Boolean
*expressions*. Example:

CREATE TABLE Table1 (Column1 varchar(10), Column2 varchar(10))

INSERT INTO Table1 (Column1, Column2) values ('A' , null)
INSERT INTO Table1 (Column1, Column2) values (null, 'B' )

SELECT * FROM Table1 WHERE Column1 = 'A' OR Column2 = 'B'
-- returns 2 rows

SELECT * FROM Table1 WHERE Column1 = 'A' AND Column2 = 'B'
-- returns 0 rows

[Back to original message]