Re: Null in subquery returns no records

>
> I was trying to emphasise the point that your selection of logical
> rules is arbitrary. SQL picked one. There were others. And it's not
> clear to me that any one of them is better than any other.

First thanks for participating,

And allthough I agree with you that 'sometimes' the choice of rules is arbitrary, I do not agree with that in this case.

Starting from boolean logic.

(False and (anything)) Evaluates to false. anything can only be True or false in boolean logic.

Extending this to three valued logic and accepting that the null value is only used because we do not know the value, the logic example with anything in it stays the same. Therefore :
(False and null) evaluates to false.

I agree with you that the interpretation of null can be of several kinds. In your case the weight of a planet should exceed a certain minimum (why does an electron not count as a planet ?). So there is weight, we just don't know it. Or it could be an occurence in the future (a date) which has not occured yet so it is a data larger than now but we just do not know it.
But I do realise that some people use null to represent a value outside the domain of the 'normal' values. In that case boolean logic does not apply and the three valued logic as implement in SQL can fall apart. I personaly think that allthough a value is unknown it should not be a value outside the domain. Example : store an age of somebody as from 0 to 99 and using null for ages above that range. I think that is wrong use of null.

So I think the implementation of three valued logic as is in SQL (at least Oracle and MS-sql-server) is logical and the correct choice and it is not an arbitrare choice.

Yes and there are some inconsistencies in SQL. After evalueting to null in a where clause, if the dataset is used further on, it is interpreted or (evaluated) to false. So using an exists (or not exists) construct, the 'null' row, becomes a 'false' row, hence causing 'inconsistencies'. (For myself I remember : NULL does not survive outside the where clause).

ben brugman Received on Thu Feb 12 2004 - 09:58:22 CET