Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> writes: >>> ... and I do something like "select id where animal <> 'Cat';" then >>> shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. >>> NULL is not 'Cat'. I realize that if I were testing for NULL itself I > NULL is nothing, you can't compare something with nothing. A better way to think about it is that NULL means UNKNOWN. Thus the result of NULL <> 'Cat' is not FALSE but UNKNOWN (ie NULL) --- if you don't know what the value is, you don't know whether or not it's equal to any specific other value. The other mistake novices typically make is to expect that NULL = NULL will yield TRUE. It doesn't, it yields NULL, because again you can't say whether two unknown quantities are equal. You can hack around this behavior to some extent with IS DISTINCT FROM, but generally the right thing is to redesign your data representation. Trying to make NULL act like a normal data value is almost always going to lead to tears in the long run. regards, tom lane
