Re: to NULL OR NOT to NULL ? That is the Question.

Thus spake sahmad_at_mfa.com (Saad Ahmad):

>Ian Harcourt (ianhar_at_u09002.skm09.svskt.se) wrote:
 

>> Hi !
>> I'm new to this group but I hope this is thew right forum to air my
>> problem.
 

>> I'm working with Oracle 6.0 and Forms 3.0. I have a problem with a
>> SELECT statement.
 

>> The table in question has a column that CAN contain a VALUE or NULL.
 

>> I wish to incorporate into ONE select statement the possibility to select all
>> posts OR just those posts with a certain value, i.e.
 

>Try this:
 

> select col
> from table
> where
> col like in_parameter or
> (col is null and
> rtrim(in_parameter) is null )
>--
>**************************************************************
>* Saad Ahmad *
>* E-Mail: sahmad_at_mfa.com *
>**************************************************************

A little note on this. I assume in_parameter is some sort of bind variable.

The use of 'OR' might to some indicate possible problems with performance, but oracle is actually capable of optimizing this. So my recommandation is write:

...
WHERE
  ((:bind_parameter IS NULL AND col IS NULL)    OR
   (col LIKE :bind_parameter)
  )

as the clauses is evalueted in the sequence in which they are written, and conditionals are automatically conditional, so here only the apropriate of the conditions including the column is executed (and the 'expensive' LIKE is only executed if the bind_varaible actually is not null).

This might be of greater interest if the other half of the 'OR' statement is a really expensive subquery.

/anders

| Anders Harder (harder_at_daimi.aau.dk) I learn mostly from my failures...      |
| Comp. Sci. Dept.| Private address:  it isn't that I don't learn from my     |
| Aarhus Univ.    | Ydunsvej 12       successes...                            |
| DK-8000  Aarhus | DK-8230 Aabyhoj   but I have more failures than successes!|
| Denmark         | Denmark           (Rune T. Kidde)                         |
-------------------------------------------------------------------------------