List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 19 2004 3:59pm
Subject:Re: detect rows that has zero values
View as plain text  

Bulant Denys wrote:

>  --- Michael Stassen <Michael.Stassen@stripped> a
> écrit : 
> 
>>denys wrote:
>>
>> > SELECT  country
>> > FROM Rates
>> > WHERE fieldvalue1 <> 0 AND fieldvalue2 <> 0 AND
>>fieldvalue3 <> 0
>> > ORDER BY country
>> > is it what you want ?
>> >
>>
>>Gleb Paharenko wrote:
>>
>>
>>>Hello
>>>
>>>You may use query like this:
>>> select country from rates where (fieldvalue1 !=0)
>>
>>and (fieldvalue2 !=0) 
>>
>>> and (fieldvalue3 !=0) order by country;
>>>
>>>See:
>>> 
>>
> http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
> 
>>And since 0 is false and every other number is true,
>>this could be 
>>simplified to
>>
>>   SELECT country
>>   FROM Rates
>>   WHERE fieldvalue1 AND fieldvalue2 AND fieldvalue3
>>   ORDER BY country;
>>
>>Michael
>>
> I will go to bed less fool this evening... Is it just
> a clearest way to write it, or is there some
> performance issues too ?

In theory, "fieldvalueN != 0" causes a comparison operation which will 
return 1 or 0 (true or false) for each field, then the results are combined 
with ANDs, which would be 3 extra comparisons compared to simply combining 
with ANDs.  In practice, I'd bet the optimizer is smarter than that, and the 
performance will be identical.  Even if the "extra" comparison were not 
optimized away, I doubt it would appreciably affect the speed.  You could 
certainly try it both ways with your data to see if if you get any 
difference, but I don't expect any.

Michael
Thread
detect rows that has zero valuesLouie Miranda19 Nov
  • Re: detect rows that has zero valuesdenys19 Nov
  • Re: detect rows that has zero valuesGleb Paharenko19 Nov
    • Re: detect rows that has zero valuesMichael Stassen19 Nov
      • Re: detect rows that has zero valuesSGreen19 Nov
        • Re: detect rows that has zero valuesMichael Stassen19 Nov
      • Re: detect rows that has zero valuesBulant Denys19 Nov
        • Re: detect rows that has zero valuesMichael Stassen19 Nov
        • Re: detect rows that has zero valuesMartijn Tonies19 Nov