Working with NULL problem

Square1

Hi all:

Let's say you have a VARCHAR column. If the requirements apply for a given situation you populate the field for this column (Named "OLD_PEOPLE" 😉) with a "Y". So, if the requirement is, employees born before 1960, if Tom is over 52 years old he would get a "Y" in this column. Otherwise, the column is NULL.

Here is the problem. When writing a select statement such as "SELECT * FROM EMPLOYEES WHERE OLD_PEOPLE <> Y" then results would not be as I expect, which would be all employees where the field is NULL. Nothing is returned.

What is the proper way to handle this? Is Boolean the way? Do I need to actually use "N" instead of NULL?

Thanks!

traq

I would use a BOOL column, since all you really need in this case is 1 (yes) or 0 (no).

To answer your question, for your existing schema, use

SELECT * FROM `EMPLOYEES` WHERE `OLD_PEOPLE` IS NULL

(I would also recommend -for simple readability reasons- to use lowercase names for your tables and columns.)

planetphp

You could possibly use mysql ISNULL

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull

bradgrafelman

Just thought I'd chime in:

I agree with traq - sounds like this should be a BOOL column, possibly NOT NULL and possibly not. Note that most people would say there is definitely a difference between it being NOT NULL vs. NULL; to me, boolean FALSE (or 0) means that the column does have a valid state (which happens to be negative or false). A NULL value, however, means that the column does not have a valid state/value (which could take on various meanings depending upon the application, e.g. the user hasn't answered a given question yet, so the value isn't true or false yet).

Derokorian

Another option would be to calculate the years in the query, instead of having a column that's a Y/N type deal. I imagine since it's an employee table that you have their DOB already. Then you could do something like:
SELECT * FROM employees WHERE YEAR(dob) < 1960

Square1

Derokorian;10995621 wrote:
Another option would be to calculate the years in the query, instead of having a column that's a Y/N type deal. I imagine since it's an employee table that you have their DOB already. Then you could do something like:
SELECT * FROM employees WHERE YEAR(dob) < 1960

Thanks all. The example I gave really was just that; an example. I don't really need an exact answer for that.

If I use BOOL instead doesn;t that really hace the same effect as replacing all the NULLs with "N" and leaving it VARCHAR?

traq

No. You could well end up with code that treats them the same way, however.

Derokorian

Also something to note, VARCHAR should be used when the length of the string in VARiable where as CHAR should be used when the length is always the same.

cretaceous

As I see it a null value is like Schrodinger's cat : it's both dead and not dead
in other words it's indeterminate (as bradg states)
[though luckily it doesn't change value when it's looked at 😉]

I've also realised that in some cases where I have used them in the past I would be better off with a simple default of '0'

but you can always fix some sql with

WHERE (thing=0 OR thing IS NULL)

however this:
WHERE (thing=1 OR thing IS NOT NULL)
would not do what I can see as being useful
(um, and now I'm not sure how I would handle that last scenario..)

Derokorian

however this:
WHERE (thing=1 OR thing IS NOT NULL)
would not do what I can see as being useful
(um, and now I'm not sure how I would handle that last scenario..)

WHERE thing = 1 would cover is not null. if you had a mix of 1, 0 and null using your thing=1 or this is not null would also get you 0, so you would actually just want thing is 1...

Ashley_Sheridan

That's why I'd go for an enum type instead of a bool (assuming your DB supports it of course) with a NOT NULL enforced on the field and a default value. It can make the queries a bit more human to read and avoids problems with that third state where there might be confusion over exactly what a NULL means in a given context.