Empty string vs NULL

Question

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.

In sql Null is defined as Null and '' refers to some empty value — Saeed ur Rehman, Nov 17 '16 at 09:58
'' Empty string: We can say there is no value. But NULL means we cant say there is no value. It means there might be have value, but its unknown. That is the reason why NULL is different from empty string. — Shakeer Mirza, Nov 17 '16 at 09:59
Don't allow zero length strings as name (have a constraint, or trigger). Use NULL in that case. — jarlh, Nov 17 '16 at 09:59
Null means it contains 'NULL' as a value and '' means not any value — mansi, Nov 17 '16 at 10:01

sagi · Accepted Answer · 2016-11-17T10:11:28.497

13

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.

edited Nov 17 '16 at 10:11

answered Nov 17 '16 at 09:57

sagi

36,554
5
46
75

1

by selecting cell IS NULL and by cell='' – mansi Nov 17 '16 at 10:02
Actually, `null = null` will result in UNKNOWN, see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver15. – Qw3ry Jan 20 '20 at 11:51

score 2 · Answer 2 · answered Nov 17 '16 at 10:01

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 values represent missing unknown data.
Blank data is actual data entered as blank during input.

mansi · Answer 3 · 2016-11-17T10:15:26.670

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

score 1 · Answer 4 · answered Nov 17 '16 at 10:00

1

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

answered Nov 17 '16 at 10:00

Jbadminton

1
3
19
42

score 0 · Answer 5 · answered Mar 06 '20 at 11:17

0

It is because a cell containing NULL and an empty cell are 2 different things. i.e. -> NULL is not as same as '' or "".

answered Mar 06 '20 at 11:17

Palash Som

1
1

Empty string vs NULL

5 Answers5

Linked