[thelist] SQL query question (easy)

[Mattias Thorslund]

I agree that ORs can be problematic for performance.

If there's no COALESCE(), look for IFNULL(), which basically does the
same thing. In Tab's example below, IFNULL() would do the exact same thing:

IFNULL(ColumnWithNulls, 'replacedValue')

The difference is that COALESCE can take more than one parameter for the
replaced value. If the first parameter is null, the value from the
second parameter is used, and if that's also null, the value from the
third parameter is used, and so on until a non-null value is found or
there are no more parameters.

NOW() is used in MySQL, and maybe other database servers too. MySQL has
both COALESCE and IFNULL (as does MSSQL).


Tab Alleman wrote:
> When you use OR, sql basically does a UNION for you behind the scenes.  You actually get better performance by writing out the UNIONs, but it's a lot of code to look at.
>
> Whenever NULLs are involved, I always first look to COALESCE:
>
> WHERE GETDATE() BETWEEN COALESCE(start_on,'1/1/1900') AND COALESCE(end_on,'6/6/2079')
>
> GETDATE() is TSQL for now(), by the way.  I don't know what database uses now(), and I can only assume it uses COALESCE, or something like it.
>
>
>
>   
>> -----Original Message-----
>> From: thelist-bounces at lists.evolt.org
>> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of John Hicks
>> Sent: Wednesday, May 10, 2006 2:01 AM
>> To: thelist at lists.evolt.org
>> Subject: Re: [thelist] SQL query question (easy)
>>
>>
>> April wrote:
>>     
>>> I have start_on and end_on dates, which can also be null.  
>>>       
>> I'm writing a 
>>     
>>> SQL select to find all instances where a date is within the 
>>>       
>> start_on, 
>>     
>>> end_on range and but returns if one or the other is null 
>>>       
>> too.  If the 
>>     
>>> start_on is null and the end_on is greater than the date, 
>>>       
>> then it needs 
>>     
>>> to return.
>>>
>>> Basically, I need to combine:
>>> start_on <= now() AND end_on >= now()
>>> with:
>>> start_on IS NULL AND end_on IS NULL
>>> start_on IS NULL AND end_on >= now()
>>> start_on <= now() AND end_on IS NULL
>>>       
>> How about:
>>
>> where
>> (start_on <= now() OR start_on IS NULL)
>> AND (end_on <= now() OR end_on IS NULL)
>>
>> --J
>>
>> -- 
>>
>> * * Please support the community that supports you.  * *
>> http://evolt.org/help_support_evolt/
>>
>> For unsubscribe and other options, including the Tip Harvester 
>> and archives of thelist go to: http://lists.evolt.org 
>> Workers of the Web, evolt ! 
>>
>>