MySQL: NULL and ''

[cancer10]

Hi,

Is there any difference between, NULL and ''?

For example, consider this query


select * from employees where username is null


and

select * from employees where username = ''

Please explain the diff.


Thanx

[felgall]

NULL means you don't know the value of the field (yet).

'' means that you do know the value of the field and that the value is nothing.

Perhaps it would have been less confusing if they had called it UNKNOWN instead of NULL.

[cancer10]

Aint they kinda similar in someway?

[chazzy]

Aint they kinda similar in someway?

No, they ain't.

[felgall]

Aint they kinda similar in someway?

Not at all.

For example, my father didn't have a middle name but I do. If you have a middle_name field in your database then that field could be NULL for both of us if you don't know what our middle names are. If you set the field to '' for me then it is set to the wrong value since that isn't my middle name. Setting the field to '' for my father would be correct since that is his middle name - ie he hasn't got one.

There is a lot of difference between not knowing what value a field should have and knowing that it should be empty. '' is no different in terms of how specific that it is than 'John' is. Both values mean exactly the value they contain while NULL means that the field doesn't have a value in it yet.

'' and 'John' are kinda similar (both known values) but NULL is totally different (unknown value).

If NULL and '' had the same meaning then there wouldn't have been any reason to create NULL in the first place.