Hi Guys,
Need help to understand, Please help me to understand....
Here is my sample data
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
2,Faid,Kim,Null
3,Jim,Al,Null
My Simple SQL Query is
Select * from table1
where STATUS not in ('InActive','Confirmed')
I am getting this result back
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
It should return all three records. Any thought why it is behaving like this ? STATUS data type is VARCHAR(10)
NULL does not match anything, not even another NULL, other than an IS NULL or IS NOT NULL test
where (STATUS not in ('InActive','Confirmed') OR STATUS IS NULL)
Kristen is correct. As a bit of a sidebar, though, you should avoid NULLs in data whenever possible. Even if the status is unknown, you shouldn't use a NULL. Instead, use "Unknown" (for example) and avoid the general problem of trying to do comparisons with NULL.
Not sure I agree with that ...
... but for me a STATUS would be a required, NOT NULL, column - with an appropriate "unknown" value. Personally I'd prefer a short INT value, rather than words [for a STATUS column]
I agree to disagree. I would rather say "learn to deal with nulls"
One of my interpretations of NULL is "Operator has never been given the chance to enter data for this field, so we don't (yet) know if someone actually knows the value for this, of if the value is genuinely unknown, or if the value is easily get-able, but we haven't had a chance to do that yet (either software not asked, or we were too lazy/busy/whatever")
I agree to having a value (e.g. for a lookup-code column such as Status) for "We were given the opportunity to get & enter this data, but we don't [yet] have a known-value") and also for "We will never know this value"
If the column is NOT NULL then I think a value is also required for "Operator has not yet been given the opportunity to provide a value". That, itself, must obviously not be allowed as a choice from a user - e.g. on a data entry form.
In the context of the O/P I would much prefer to have a numeric value for the status, than a text-string (which I cannot, for example, sort into "logical order" - e.g. in terms of the progression of an order from "New order" to "Shipped and Invoiced"). Of course I can do that by JOINING to a Status Definition Table, which has a SortSequence column,. but we assign codes such as 10, 20, 30 for these type of status codes, leaving gaps for future status additions, so that, without having to make a JOIN, we can do Logical Sort Ordering.
We can also do STATUS < 90 where 90+ is Closed, Cancelled, whatever, or STATUS BETWEEN 20 AND 50 if, for example, they represented "in production"
Heh... I did learn how to deal with NULLs... and that's precisely why I recommend that you should avoid NULLs in data whenever possible.