db2 != check

Dear experts

I have a table CUSTOMER with column ID and NAME
1  Eric
3  [Null]

I have a simple query to retrieve data where NAME is not 'Eric'

select * from CUSTOMER where NAME != 'Eric'

i am expecting to see
3 [Null]

but it doesnt return anything.
any clue?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
For grins, try this:
I know they're supposedly both the same thing, but curious if there is some logic that might make one different.

select * from CUSTOMER where NAME <> 'Eric'

Open in new window

royjaydAuthor Commented:
>>select * from CUSTOMER where NAME <> 'Eric'
nope, already tried that but still doesn't work.
RayData AnalystCommented:
is the "ID" field an actual entered field or is it an identity field?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

royjaydAuthor Commented:
when I do
select * from CUSTOMER where NAME = 'Eric'
I see one row, so I am really confused why != doesn't work.
royjaydAuthor Commented:
>>>is the "ID" field an actual entered field or is it an identity field?
ID and NAME are just a VARCHAR fields , no identity field in the table.
RayData AnalystCommented:
I think that null cannot be compared so it is not equal to and is not not equal to (I know that sounds crazy).  Try this please...

select * from CUSTOMER where NAME <> 'Eric' or name is null

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
royjaydAuthor Commented:
something like this works
select * from CUSTOMER where NAME != 'Eric' or name is null

but is that guaranteed to work is my concern.

I just want to return everything whose NAME is not Eric.
Kent OlsenDBACommented:
Hi Roy,

NULL is always a special case and all of the major DBMS have subtle differences in how NULL is managed.

But a universal truth is that NULL is not equal to anything.  When compared for equality (or inequality) NULL in any column tests FALSE;

Ray's suggestion is correct.  You need to test for the desired (or excluded) value(s), and also for NULL.  That will always work.

Good Luck,
RayData AnalystCommented:
The root issue withOUT the "or name is null" is that you can't evaluate null against a condition.  Something either is, or is not, null.  It cannot be compared to any specific value.

In conclusion, if you need the null results you simply have to include that extra bit of code.
A null is neither equal nor not equal. It's... null. That is, a value doesn't exist in that column in that row. If it doesn't exist, a comparison doesn't make logical sense.
royjaydAuthor Commented:
thanks all.
Tomas Helgi JohannssonCommented:

You could do a query like this
select coalesce(NAME , 'A EMPTY FIELD') NAME from CUSTOMER where NAME <> 'Eric' ;

Open in new window

This would give you the names that are in the column NAME excluding the name Eric and
report all rows that are NULL with 'A EMPTY FIELD' string.  
Note that a NULL value indicates that a particular value for a certain column in a certain row is missing.

     Tomas Helgi
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.