char field <> or != to a value does not select where field is null ???

Maybe this is just my misunderstanding of how this should work, but
I've got a table with a char(6) field named member_status. When I
select "member_status != '*'" or "member_status <> '*'", I get 14856
rows. However, if I select "member_status is null or member_status !=
'*'", I get 20308 rows.

It was my understanding that a NULL value would be not-equal to any
non-null value. Am I wrong about this? I'm seing this behavior on
7.3.2, on 7.4.5 and on 8.0.3.

On my 8.0.3 database, I created a test table with just a single char(6)
column and inserted 2 rows; 1 with a null value and one with a '*'.
When selecting != '*', I get zero rows, and ='*' returns 1 row. It
seems logical to me, to expect that != and = would return complimentary
results. What am I missing?

Douglas Toltzman, Oak Street Software, Inc.
voice: 910-526-5938
http://www.oakstreetsoftware.com/