Null Booleans (Was: Re: SQL performance - optimizer question)

Thus spake nolan_at_helios.unl.edu (Michael Nolan):

>jl34778_at_corp02.d51.lilly.com writes:
 

>> where (key1 = :key1 or :key1 is null)
>> and (key2 = :key2 or :key2 is null)
>> and (key3 = :key3 or :key3 is null);
 

>>specifically with the 'or :key1 is null' piece. That 'or' condition does not
>>include any reference to a database object, so I would think that it is either
>>true for all rows or false for all rows, depending on the static value of
>>:key1. I don't see how this would find null values of key1 when :key1 is null.
 

>ANY comparison operator other than an 'is null' with a null variable results
>in a FALSE. Thus 'where null = null' would result in a FALSE, even though it
>would seem to be inherently TRUE.

Not quite true. Any comparison operator other than an 'is null' with a null variable results in a (boolean) NULL., and NULL is treated as FALSE, but 'where null = null' and 'where not ( null = null)' both results in no rows returned.

The Boolean logik in SQL is thus 3-values TRUE, FALSE or NULL.

The logic operators treats NULL reasonable, but not as FALSE. E.g. 'NULL or TRUE' is TRUE.

SO ALWAYS BE CAREFUL, WHEN NULLS CAN APPEAR IN LOGIC EXPRESSIONS !!!
> [...]
 

>---
>Michael Nolan, Sysop for the DBMS RoundTable on GEnie
>nolan_at_notes.tssi.com, dbms_at_genie.geis.com
>(posted from nolan_at_helios.unl.edu)

| Anders Harder (harder_at_daimi.aau.dk) I learn mostly from my failures...      |
| Comp. Sci. Dept.| Private address:  it isn't that I don't learn from my     |
| Aarhus Univ.    | Ydunsvej 12       successes...                            |
| DK-8000  Aarhus | DK-8230 Aabyhoj   but I have more failures than successes!|
| Denmark         | Denmark           (Rune T. Kidde)                         |
-------------------------------------------------------------------------------