--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
wrote:

>
> > Alan McDonald wrote:
> > >>
> > >>Often used example:
> > >>I don't know how much money you've got in your wallet, so the

value is

> > >>NULL (unknown).
> > >>
> > >>You don't know how much money I've got in my wallet, so it's

NULL, too.

> > >
> > > I've never thought of this as a good example.
> > > I have money in my wallet - it could be nothing or it could be
> > $5 or $10 etc
> > > In the DB, if the field state is NULL - it NEVER has an unknown
> > value in it
> > > of 5 or 10 - it has no value it's state is NULL. if it has 5 or

10 in it

> > > then it is NOT NULL.
> >
> > But the key here is that NULL means unknown (to others than the

wallet

> > owner in the above example). So, to the wallet owner, the amount

of

> > money in his/her wallet is NOT NULL (unless he/she hasn't counted

it for

> > a while...), but to the rest of the world, it's NULL.
> >
> > Kjell
> > --
> > --------------------------------------
> > Kjell Rilbe
>
> ahhh, we need a consistent view on our data - you can't have a

system which

> reports NULL to some people and NOT NULL to others based on who is

asking.

> Alan
>

Unless it is MGA, and someone has run a statement such as

update wallet set
amount = 5
where name = 'Alan';

And everyone else still see's the null value until you commit, but we
are getting a bit picky?

The concept of null can often be avoided with normalisation, too many
nulls in a field is usually an indicator that it should be in another
table, but there are uses for it.

Firebird treats nulls in a good manner with one or two exceptions
when it comes to aggregates (summing a field in a table with 0
records returns null instead of 0 is one such exception).

99% of the battle is to stop thinking of null as just another value.
It is a flag on the field that defines whether the value is known /
valid.

There is an important distinction that is not often made. I have a
middle name, but you do not know what it is. To say my middle name
is '' would be incorrect. You may also have a middle name (or may
not), but if you do, then unless you reveal it, we would have to
treat it as null.

Does my middle name = your middle name? Perhaps, unlikely but there
is a chance. That function correctly returns null. By definition, any
function that does not return true will not enter the if statement.
By the same token, my middle name <> your middle name is just as
impossible statement to evaluate.

Sometimes (especially in triggers), you want to see if a field or
variable has been modified, because Firebird has triggers on the
record level rather than on the field level.

The simple test may be

if (old.somefield = new.somefield) then
begin

end

But of course we already know that this will return false if the
field was and still is null.

(NULL = NULL) returns NULL

To make this test not concerned with the null state, it is a simple
case of:

if (Coalesce(old.somefield, 0) = Coalesce(new.somefield, 0)) then
begin

end

Of course that assumes that 0 is not a legitimate value in its own
right. Perhaps there is a good argument for some sort of function
available in PSQL that does a comparison that treats null as another
value.


Adam