Difference between NULL and Blank Value in Mysql

Question

I have to check for a value of a particular Column named RESULT is blank or not.

When I check with if RESULT IS NULL, the query failed, but When I checked RESULT='', it worked.

What is difference between two.

please explain.

"UPDATE RLS_TP_2012_03 a, RLS_TP_2012_03 b SET a.COMMENT=b.COMMENT where b.TCODE='T1199' and  a.GROUPNAME='xyz' and a.HLABNO=b.HLABNO and a.RESULT =''; ";   
"UPDATE RLS_TP_2012_03 a, RLS_TP_2012_03 b SET a.COMMENT=b.COMMENT where b.TCODE='T1199' and  a.GROUPNAME='xyz' and a.HLABNO=b.HLABNO and a.RESULT is NULL; "

possible duplicate of MySQL, better to insert NULL or empty string? — Sadikhasan
– Sadikhasan, Commented Jun 27, 2014 at 5:28

Andrii Abramov · Accepted Answer · 2017-12-04 15:12:46Z

37

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 "".

edited Dec 4, 2017 at 15:12

Andrii Abramov

10.9k12 gold badges81 silver badges107 bronze badges

answered Jun 27, 2014 at 5:33

Ricky

2,3726 gold badges22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aki Over a year ago

Does this mean that NULL technically uses up less space/memory, thus better for performance wise? E.g. if you had nvarchar(max) with a NULL value versus an empty string?

paxdiablo · Accepted Answer · 2014-06-27 06:16:59Z

6

In tri-value logic, NULL should be used to represent "unknown" or "not applicable" An empty column should represent "known but empty".

Some DBMS products don't follow this convention all the time but I consider them deficient. Yes, you know who you are, Oracle :-)

In addition, while NULL can be put into any column type (ignoring not null clauses for now), you cannot put an "empty" value into a non-character field (like integer).

edited Jun 27, 2014 at 6:16

answered Jun 27, 2014 at 5:33

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Comments

Ron Hiner · Accepted Answer · 2017-05-18 02:21:54Z

2

Adding to @ricky, NULL can never equal NULL, but an empty string is always equal to an empty string. That's a huge difference when you are trying to match records.

answered May 18, 2017 at 2:21

Ron Hiner

211 bronze badge

Comments

Sameer · Accepted Answer · 2016-12-24 15:55:01Z

0

NULL in database systems - No value set for the field. A void

NULL (character) - A blank, an ASCII/Unicode character with no graphical representation. But it is present in code page as a character constant '\0' with a character value 0 or 000.

answered Dec 24, 2016 at 15:55

Sameer

1

Collectives™ on Stack Overflow

Difference between NULL and Blank Value in Mysql

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related