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.
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.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.
null = null will result in UNKNOWN, see learn.microsoft.com/en-us/sql/t-sql/language-elements/….You can have your query modified as below:
select * from zzz_fkp_registration_female where isnull(fname,'') = '';
This query will result all the blanks cells as well as cell with null values.
Note:
Null is an absence of a value. An empty string is a value, but is just empty.
Null is special to a database.
By using NULL you can distinguish between "put no data" and "put empty data".
Some more differences:
A LENGTH of NULL is NULL, a LENGTH of an empty string is 0. NULLs are sorted before the empty strings. COUNT(message) will count empty strings but not NULLs You can search for an empty string using a bound variable but not for a NULL. This query:
SELECT *
FROM mytable
WHERE mytext = ?
will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you'll have to use other query
SELECT *
FROM mytable
WHERE mytext IS NULL
He can't find NULL because it doesn't have a value
An empty string is a value, but its empty.
Only if its a value it can compare with another value
It is because a cell containing NULL and an empty cell are 2 different things. i.e. -> NULL is not as same as '' or "".