Re: A Question regarding null columns.

(drifting off thread...)
It's not right to say that NULL = NULL is a false statement, or that NULL <> NULL is a true statement. The picture is more complicated than that, and involves a strange twist on boolean logic caused by the presence of nulls. You end up with 'trinary' truth tables instead of the classic binary TRUE/FALSE truth tables.

Assume your code example is written in PL/SQL. It is better to think of NULL as 'unknown value'. If a PL/SQL comparison is unable to evaluate as definitely TRUE or FALSE, then it will evaluate as NULL, and the 'else' clause of the if statement will be executed.

Here are some illustrative examples:

  if (null = null) then a := 1; else a:=2; endif;

Does one unknown equal another unknown? Well, the answer is of course, unknown (i.e. null) and so the 'else' clause is triggered and a = 2.

  if (2 + 2 = 4 and b = null) then a := 1; else a:=2; endif;

2+2=4 is certainly TRUE, but b = null yields NULL (an unknown answer). So we have TRUE AND NULL, which because of the unknown will also be unknown (both operands must be TRUE for the AND operator to return TRUE). Result is null, and a = 2.

  if (2 + 2 = 4 or b = null) then a := 1; else a:=2; endif;

Now we have TRUE OR NULL? The OR operator only requires one of its operands to be TRUE in order to return TRUE. Result is TRUE, and a = 1.

true and true => true
true and false => false
true and null => null
false and false => false
false and null => false (AND only needs one false to return a false) null and null => null

true or true => true
true or false => true
true or null => true
false or false => false
false or null => null (OR will only return false if both operands are false) null or null => null

Same rules apply to the predicate in a WHERE clause, if the overall predicate evaluates to TRUE then the row will be selected, if it evaluates to FALSE or NULL then it will not.

If you want to test for two variables' equality, (treating NULL as a data value), you'd have to write it like this:

  if (a = b or (a is null and b is null)) then ... else ... endif;

or maybe

  if (nvl(a,'X') = nvl(b, 'X')) then ... else ... endif;

but this is less reliable because a might be null and b might be 'X' but they would evaluate as equal.

(Please note, above examples based on knowledge of PL/SQL 2.0 only)

Regards
Martin Doherty

P.S. if rusty memory serves me, in Oracle 7.3 it was possible to create indexes on NULL columns, it could even be a UNIQUE index. You could have any number of rows in the table with a key value of null (or all nulls), and the index would contain no entries for those rows. If it's a multi-column unique index, NULL begins to act like a data value, e.g. with two key columns you would not be allowed to have two rows both with the same key "ABC"/null but you could still have unlimited rows with the key of null/null.

P.P.S. You'll also see null behaving something like a data value when you ORDER BY (all nulls go to the end) or GROUP BY (all nulls in same group), which is a practical if not theoretically pure way of handling the situation. Think of it as putting all the unknown values together, without implying that they equal each other.

Chris Forbis wrote:

> One possible reason is NULL can not be indexed with a normal index.
> It would need a Bitmap index. (Unless someting has changed with newer
> Oracle versions)
>
> 0 <> NULL
> heck.. NULL <> NULL keep that in mind
>
> to test you can make a procedure..
> if (NULL=NULL) then ... this will be be done because NULL <> NULL.
>
> "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<ZVZT8.588$i5.5345_at_news.indigo.ie>...
> > Consider :
> >
> > (Oracle 8173)
> > Create table eraseme as select object_id from all_objects;
> >
> > Connected to:
> > Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> > JServer Release 8.1.7.3.0 - Production
> >
> > SQL> desc eraseme;
> > Name Null? Type
> > ----------------------------------------- -------- ----------
> >
> > OBJECT_ID NOT NULL NUMBER
> >
> >
> > SQL> explain plan for select count (*) from eraseme where object_id is null;
> >
> > Explained.
> >
> > SQL> @ ?/rdbms/admin/utlxplp.sql
> >
> > Plan Table
> > ----------------------------------------------------------------------------
> > ---
> > ----------------------------
> > | Operation | Name | Rows | Bytes| Cost | TQ
> > |IN-OUT|
> > PQ Distrib | Pstart| Pstop |
> > ----------------------------------------------------------------------------
> > ---
> > ----------------------------
> > | SELECT STATEMENT | | | | | |
> > |
> > | | |
> > | SORT AGGREGATE | | | | | |
> > |
> > | | |
> > | TABLE ACCESS FULL |ERASEME | | | | |
> > |
> > | | |
> > ----------------------------------------------------------------------------
> > ---
> > ----------------------------
> >
> > 6 rows selected.
> >
> >
> > Without wishing to cause major fuss my question would be -
> >
> > since object_id is known to be not null from the data dictionary why does
> > the optimizer ask for an FTS rather than immediate 0 ?
> >
> > or is it a 'fiddled' FTS ? Checking the waits (there aren't many but there
> > are a few consistent reads )
> >
> > Just part of an issue I am trying to solve with distributed tables (the
> > above is self-contained on one instance though)
> >
> > TIA
> >
> > T