# Re: So what's null then if it's not nothing?

From: <michael_at_preece.net>
Date: 28 Nov 2005 13:25:37 -0800

vc wrote:

> Jon Heggland wrote:
> > boston103_at_hotmail.com says...
> > >
> > > michael_at_preece.net wrote:
> > > [...]
> > > >
> > > > Any two variables/"fields" (of any type) with "no value at all" are,
> > > > indeed, equal.
> > > >
> > > > Mike.
> > >
> > > Cute.
> >
> > "Cute"?
> >
> > > So, using your valuation for equality, would a query asking for a
> > > pairwise list of people living in the same country produce a correct
> > > result if some pairs that happen to have nulls for their countries ?
> >
> > That, of course, depends on what you mean by a correct result, what it
> > means that country is NULL, and how the query is formulated.

```>
```

> By the correct result I mean the result that would not claim that
> people [about whose location we have no clue] live in the same place.
> Sort of obvious, no ?
```>

> >
> > If we assume that it is not generally the case that two people with

> > NULLs for their countries (speaking loosely here) live in the same
```
> > country, the query would simply have to be written so as to disregard
> > people with NULL country.
```>
```

> So you insist that the valuation of "null=null" as true can make sense
> in some cases ? If so, what are those cases ?

"X3H2-92-154/DBL CBR-002
3.1 Definitions

h) distinct: Two values are said to be not distinct if either:

```            both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>". Otherwise they are
distinct. Two rows (or partial rows) are distinct if at
least
one of their pairs of respective values is distinct.
Otherwise
they are not distinct. The result of evaluating whether or
not
two values or two rows are distinct is never unknown."

```

> If not so, why do we
> need to modify our query at all ? The standard SQL does not evaluate
> null=null to true, so why bother writing the query "so as to disregard
> people with NULL country" ?
>
> > --
> > Jon
Received on Mon Nov 28 2005 - 22:25:37 CET

Original text of this message