List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:October 31 2006 2:44pm
Subject:RE: finding NULL records
View as plain text  
[snip]
>And the reason for that is because nothing is actually equal to NULL.
>For a field to be equal to NULL it would actually have to contain NULL,
>in which case it would not be actually NULL.

What?

NULL doesn't equal NULL because NULL means "unknown".

A column can have 2 states: known or unknown (NULL). If the
case of a "known" state, the column has a value, in cause of unknown,
it's NULL.

You cannot compare to "unknown". So this is why the SQL standard
says: you either ask for a specific value (eg: myid = 1002) or you ask
for a state: "myid IS NULL" or "myid IS NOT NULL" (= filled).

It has nothing to do with "contain NULL" or whatever.
[/snip]

Philosophically this has been the argument concerning NULL for several
decades when concerned with data operations. So Martin, you are correct
here NULL is unknown and lacks state. Nothing can be equal to (=)
something that lacks state, you can only check to see if state does or
does not exist.

Many more youthful database users do not fully understand NULL and its
use. Some actually think (as appeared to be the case with the OP here)
that the field contains a value of NULL. As you stated, a value has
state and if the field has state it is certainly not NULL. 'IS NULL' and
'= NULL' are two very different things.

Sure I was being a little cheeky with my response, I should have taken
the time to more carefully explain NULL.
Thread
finding NULL recordsAlan Nilsson31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsAlan Nilsson31 Oct
    • Re: finding NULL recordsChris31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • RE: finding NULL recordsJerry Schwartz31 Oct
    • Re: finding NULL recordsMartijn Tonies31 Oct
      • Re: finding NULL recordsmizioumt15 Nov
        • RE: finding NULL recordsJerry Schwartz15 Nov
          • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsmizioumt17 Nov
          • Re: finding NULL recordsmizioumt17 Nov