Re: Intentional, or bug?

On Sun, 16 Sep 2001, Kovacs Baldvin wrote:

> select NULL = NULL;
>
> The answer is: true!!!

This has got to be a bug.  NULL is not equal to anything (nor is it
unequal to anything).

SQL uses tri-valued logic, where answers are either true, false, or
unknown.

When selecting where a = b, if either is null the row will not be
selected.  Likewise for a <> b.  This is standard correct behaviour for
SQL.  One rationale is, if null represents an unknown value then how can
you know if it's equal or not?  It might in fact be equal but we don't
know.

> However, I have a real life problem, when not all data is given in
> a column, so I need a query to include the NULL=NULL rows. I can
> workaround it as defining a new operator, ~=, with the
> meaning "both null, or =". I did it, it works, but very slow.

If you want the rows where a = b and where a and b are both NULL, you must
specify it precisely:

where a = b or (a is null and b is null)

> So, is it an intentinal way of functioning, or it is bug somewhere?

This is how it is supposed to work.
-- 
Tod McQuillin