List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:October 28 2004 11:47am
Subject:Re: bug or feature, <> 'blah' does NOT work with null records
View as plain text  
From: "Harald Fuchs" <hf0722x@stripped>


> In article <001f01c4bcd3$84cf5c80$2801a8c0@localdomain>,
> "Jigal van Hemert" <jigal@stripped> writes:
>
> > Fortunately there is function COALESCE() that will return the first
argument
> > that is not NULL. In case of NULL values you can use a default value for
an
> > expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL.
>
> This idiom, albeit terse, is not something you should get used to,
> because it precludes index usage.

I didn't say you should use it in a WHERE clause or other condition on large
tables! ;-P

I used a lot of COALESCE's to calculate some kind of score for each selected
record and that was pretty fast. In this particular case I could find NULL
values, but the score should still be a number. The COALESCE function came
in handy to turn NULL values in some kind of default.

But you're right when it comes to fast queries one should only compare
columns to other columns or constants (these could be calculated constants).

Regards, Jigal.

Thread
bug or feature, <> 'blah' does NOT work with null recordsmatt_lists25 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsKeith Ivey25 Oct
    • Re: bug or feature, <> 'blah' does NOT work with null recordsSGreen25 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsPaul DuBois25 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsHarald Fuchs28 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsJigal van Hemert28 Oct
    • Re: bug or feature, <> 'blah' does NOT work with null records(Pete Harlan)29 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsHarald Fuchs28 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsJigal van Hemert28 Oct
  • Re: bug or feature, <> 'blah' does NOT work with null recordsMartijn Tonies30 Oct
RE: bug or feature, <> 'blah' does NOT work with null recordsJay Blanchard25 Oct