> > The thing about NULL is ... you just don't know!
<snip what I thought was a very good example>
> so what you're saying is NULL = * in Postgres?? With every other database
> server I've used, NULL is its own value, not any value, or I'm completely
> misunderstanding what you're trying to say here.
Either you are mistaken, or those other databases were incorrectly
implemented (assuming they were following the SQL standard). Here is an
independant Knowledge Base item (with a great subject: "SQL: NULL is not
Nothing") that discusses NULLs in Oracle:
http://www.metrokc.gov/gis/services/KnowledgeBase/SQLtipNull.htm
They even quote the Oracle documentation:
"To test for nulls, use only the comparison operators IS NULL and IS NOT
NULL. If you use any other operator with nulls and the result depends on the
value of the null, the result is UNKNOWN. Because null represents a lack of
data, a null cannot be equal or unequal to any value or to another null."
> However, the last time i checked, NULL does not equal to 1, 2, 3, or
> anything else besides NULL. Thats why I'm confused as to why SELECT
column
> FROM table WHERE column != 1 also excludes NULL, because 1 does NOT have
the
> same value as NULL.
NULL does NOT *have* a value. NULL is an *undefined* value. Therefore you
can't say if it is equal to a value or not. They probably said it better
than I am: "A condition that evaluates to UNKNOWN acts almost like FALSE.
For example, a SELECT statement with a condition in the WHERE clause that
evaluates to UNKNOWN returns no rows."