Re: bug or feature, <> 'blah' does NOT work with null records

[Jigal van Hemert]

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.