Re: Best SQL Query

All three query is okey, all can use index when p_cust_id is not null, and all full table scan if p_cust_id is null, should be of very similar performance.

But query1, if have null parameter, will need to evaluate nvl(p_cust_id,cust_id) for every row, so should be more CPU intensive.

Personally I prefer Query3, this is more straight forward, so more easily understood and maintained.

Regards,
Michael

techcell_at_my-dejanews.com wrote:

> Query 1
>
> SELECT cust_name
> FROM customer
> WHERE cust_id = Nvl(p_cust_id,cust_id)
>
> Query 2
>
> SELECT cust_name
> FROM customer
> WHERE cust_id LIKE Nvl(p_cust_id,'%')
>
> Query 3
>
> SELECT cust_name
> FROM customer
> WHERE (cust_id = p_cust_id
> OR p_cust_id IS NULL)
>
Received on Mon Dec 14 1998 - 01:30:13 CST