Re: Beginner SQL question

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 7 Mar 1999 09:35:59 +0100
Message-ID: <36e23a72$0$20293@pascal>

rmaggio wrote
> Select * from Table where addresss = NULL

Someone already told you to use the keyword IS, not = . That is needed because, by definition, null never equals null. It really could be translated by Oracle saying "I do not know what value it is". When Oracle needs to compare two of such values, then it "cannot be sure" if both nulls specify the very same "unkown value". So the only thing Oracle can return as the result is "I do not know". Get the idea?

Having said this, it is also easy to understand the following:

    true and null = null
    true or null = true
    false and null = false
    false or null = null

    (a = null) yields false
    (a < null) yields false
    (a > null) yields false

Things can get complicated:

    function myMin(a in number, b in numer) as number     is
    begin

        if a < b then
            return a
        else
            return b
        end if

    end myMin;

If a is null then (a<b) yields false, and b is returned. If b is null, then (a<b) also yield false, so b (null) is returned again! And in the next function, which seems to do the same, you get a if either a or b is null...

    function myMin2(a in number, b in numer) as number     is
    begin

        if a > b then
            return b
        else
            return a
        end if

    end myMin2;

Check out the nvl(..) function, like in

    select ...
    from ...
    where nvl(a, -1) = nvl(b, -1);

This would return -1 if a is null. Only if -1 is not used as a meaningful value in your database, then you could use the above syntax.

Next step for a beginner: make sure you understand the RR, RRRR and CC date formats.

Arjan. Received on Sun Mar 07 1999 - 02:35:59 CST