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.

回答1:

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.

回答2:

• Null is an absence of a value. An empty string is a value, but is just empty.

• Null is special to a database.

• Null has no bounds, it can be used for string, integer, date, etc. fields in a database.
• NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

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

回答3:

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

回答4:

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:

1. NULL values represent missing unknown data.
2. Blank data is actual data entered as blank during input.