Thread: 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/

Douglas Toltzman <doug@oakstreetsoftware.com> writes:
> It was my understanding that a NULL value would be not-equal to any
> non-null value.  Am I wrong about this?

Yes.  A NULL is neither equal nor unequal to anything else.

You can use IS DISTINCT FROM to get the behavior you are looking for,
but it might be better to rethink how you are using NULL.

            regards, tom lane