Search code examples
sqlsql-servernullcells

Empty string vs NULL

I've a table where some rows have some blank cells. I tried to select such rows using IS NULL function. But the query select 0 rows. 

select * from zzz_fkp_registration_female where fname is null;
(0 row(s) affected)

Now I changed my query to:

select * from zzz_fkp_registration_female where fname is null or fname ='';

I got the desired result.

Why is IS NULL not giving the result? What is the difference between IS NULL and '' since the cells are empty. Please explain.

Solution

  • Some differences between them:

    • NULL can be assigned to any type, as opposed to empty string which won't be compatible with date/numerical fields.
    • NULL is an UNKNOWN value, it doesn't have a value as opposed to an empty string, which is a value, but empty one.
    • As far as I'm aware of, NULL shouldn't capture memory as opposed to an empty string which does.
    • null = null will result in null as opposed to ''='' which will result in TRUE.