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/