Re: NULLs

On Tue, 20 Dec 2005 21:12:25 +0000, Andrew Clark <lark047_at_hotmail.com> wrote:

>I have an application whose database was recently upgraded from 7 to 9i. In
>a bit of code, columns are compared that may both be null. In Oracle 7,
>will two columns compare equal if they are both NULL?

 NULL never equals anything in SQL in general. It's certainly the case in any recent version of Oracle, and any reasonably compliant SQL-based database. Can't say I worked with Oracle 7.3 for very long, but I don't recall it having a case where NULL=NULL was true - this would be surprising and wrong.

 Oracle has a non-standard way of dealing with empty strings that you have to be aware of and code for appropriately - empty strings are treated as NULL, and under some circumstances (e.g. concatenation) NULL is treated as empty string.

> In 9i it seems that
>they do not and I have to change it to NVL(column1) = NVL(column2).

 Presumably with a second argument to both of those NVLs? Or without resorting to using "special" values in that way and risking false positives, then:

 (column1 is null and column2 is null) or (column1 = column2)

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool