9

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; "
Himanshu
  • 101
  • 1
  • 1
  • 4

4 Answers4

37

  1. NULL is an absence of a value. An empty string is a value, but is just empty. NULL is special to a database.

  2. NULL has no bounds, it can be used for string, integer, date, etc. fields in a database.

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

Andrii Abramov
  • 10,019
  • 9
  • 74
  • 96
Ricky
  • 2,323
  • 6
  • 22
  • 22
  • 3
    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? – Aki Jan 27 '16 at 17:05
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).

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
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.

Ron Hiner
  • 21
  • 1
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.

Sameer
  • 1