Re: Compare NULL value in WHERE clause

From: <prochak_at_my-dejanews.com>
Date: Mon, 12 Oct 1998 13:39:06 GMT
Message-ID: <6vt0po$1o2$1@nnrp1.dejanews.com>

In article <6vnsqf$emk$1_at_news.sas.ab.ca>,   suisum_at_freenet.edmonton.ab.ca () wrote:
> If I want have the following data:
>
> Field1 - NOT NULL
> Field2 - NULL
> Field3 - NULL
>
> The access key to retrieve a record is the combination of Field1, Field2,
> and Field3. If I use the following statement, the query will fail becasue
> of the NULL value:
>
> SELECT *
> FROM my_table
> WHERE Field1 = v_field1
> AND Field2 = v_field2
> AND Field3 = v_field3;
>
> Is there any better way to make the select statement more generic before I
> use the following statement:
>
> SELECT *
> FROM my_table
> WHERE NVL(Field1, '/') = NVL(v_field1, '/')
> AND NVL(Field2, '/') = NVL(v_field2, '/')
> AND NVL(Field3, '/') = NVL(v_field3, '/');
>

I'm assuming you do not know which input values (v_field*) may be NULL. (ie, you don't want specific "IS NULL" clauses) Also, I assume you don't want the performance hit due to putting functions on key columns, like NVL(Field3, '/').

Have you even considered using an OR clause? Take your original query and add the NVL() version.

 SELECT *
     FROM my_table
    WHERE (

       Field1 = v_field1
      AND Field2 = v_field2
      AND Field3 = v_field3

    )
    OR
    (

       NVL(Field1, '/') = NVL(v_field1, '/')
      AND NVL(Field2, '/') = NVL(v_field2, '/')
      AND NVL(Field3, '/') = NVL(v_field3, '/')

    ) ;

I've put the grouping parentheses on separately lines to make them stand out. Looking at this I now realize that if the first key field can possibly be NULL, then you can't guarantee an index scan. So, If you can guarantee that Field1 is never NULL, then your best bet is to write:

 SELECT *
     FROM my_table
    WHERE Field1 = v_field1

      AND NVL(Field2, '/') = NVL(v_field2, '/')
      AND NVL(Field3, '/') = NVL(v_field3, '/')

    ;

Otherwise, to find the NULLs, Oracle will have to do a full table scan. So go ahead and use the NVL()'s on all the columns, and take the performance hit.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Oct 12 1998 - 08:39:06 CDT