Re: SQL performance - optimizer question

jl34778_at_corp02.d51.lilly.com writes:

> where (key1 = :key1 or :key1 is null)
> and (key2 = :key2 or :key2 is null)
> and (key3 = :key3 or :key3 is null);
 

>specifically with the 'or :key1 is null' piece. That 'or' condition does not
>include any reference to a database object, so I would think that it is either
>true for all rows or false for all rows, depending on the static value of
>:key1. I don't see how this would find null values of key1 when :key1 is null.

ANY comparison operator other than an 'is null' with a null variable results in a FALSE. Thus 'where null = null' would result in a FALSE, even though it would seem to be inherently TRUE.

In this case, the intent is to select all rows where the database field key1 matches the designated variable key1 or that variable is non-existant and thus should be ignored. (Likewise with the other two fields.)

This would arise from a Forms query, for example, where one or more queriable fields in a block are left empty and hence null.

Programming practices note: I realize that Forms and other tools may structures their queries this way, but variable fields with names identical to database field names is IMO a Bad Idea. Not only is it confusing to read, and thus more difficult to maintain, but it lends itself to all sorts of parser mischief.

As an example of the confusion that this can create, suppose that the query was rephrased as follows:

 	where	(key1 = :key1 or key1 is null)
 	and	(key2 = :key2 or key2 is null)
 	and	(key3 = :key3 or key3 is null);

Now the intent of the query is quite different. This will find all rows where the database field key1 is null or matches the variable key1, etc.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)