NULL question


This is a kind of stupid question. I created a new field in one of the table, ActivityStatus. The value in that field is NULL.

When I do the SELECT * FROM MyTable WHERE ActivityStatus <> 'CLOSED'

It returns 0. Why is that? I though it should have returned everything? thanks
Who is Participating?
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.

ste5anSenior DeveloperCommented:
Cause in (ANSI) SQL NULL means no value entered by user, not value unknown by user. This results in a tri-state logic:

1. Comparisions with NULL on normal operators are evaluated to UNKOWN internally. But WHERE conditions require a TRUE to return a row.
2. Comparisions to return rows where a column is NULL requires explicit, different syntax: column IS [NOT] NULL. column = NULL follows the first rule and evaluates to UNKOWN.

Your problem is normally tackled by set theory: your activity status must be a mandatory column. And it consists of the states (OPEN, CLOSED, UNKOWN) as materialized values.

Then your query works as intended. Otherwise you need to implement the tri-state logic:

FROM MyTable 
WHERE ActivityStatus <> 'CLOSED'
  AND NOT ActivityStatus IS NULL;

Open in new window

btw, using states (OPEN, CLOSED, UNKOWN) is different to states (OPEN, CLOSED, NULL). Cause in the first case you know that the users set this value intentionally. In the second case, you don't know, what the user means. It's

"I checked it, But I don't know it."  vs.  the user was lazy and forgot to enter it.

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
mcrmgAuthor Commented:
thank you very much
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
Query Syntax

From novice to tech pro — start learning today.