Difference between updation with NULL and ''?

Question

I have used to two queries to update one column as NULL:

update table_name 
set col1 = NULL
where col2 = 'MUTHU';

update table_name 
set col1 = ''
where col2 = 'MUTHU';

But when i used to query with function NVL then i am getting the same result for both queries.

select nvl(col1, 'value') from table_name;

My question is: what is the 'difference' and 'use' between NULL and '' ?

can you show the sequence of your query fired? and the output — Bhavik Shah, Jul 01 '13 at 09:07
Are you using Oracle or MySQL? That makes a **huge** difference. — , Jul 01 '13 at 09:08
@a_horse_with_no_name with respect to the query above, I don't think they would make any difference. but is there NVL function in Mysql?? — chetan, Jul 01 '13 at 09:10
Do you know what `NVL` does? http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm — Salman A, Jul 01 '13 at 09:10
@chetan: it **would** make a huge difference as Oracle treats an empty string as `null` — , Jul 01 '13 at 09:13
@chetan - with Oracle's "an empty string in a `varchar` is the same as `NULL`", yes, it does. — Damien_The_Unbeliever, Jul 01 '13 at 09:13
read this http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null — BigMike, Jul 01 '13 at 09:25

score -1 · Answer 1 · answered Jul 01 '13 at 09:11

One difference is that null usually propagates so if you concat null with another string :

create table t
(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10)
  );

insert into t values ( null, '', 'hello' ) ;

select 
 concat(col1 ,col3), 
 concat(col2 ,col3) 
from t

>> NULL, 'hello'

Sashi Kant · Answer 2 · 2013-07-01T09:50:26.423

-1

'' implies that the column has a value, which is an empty string

But NULL means “a missing unknown value”

so NULL cannot be compared with =, <= and so on

edited Jul 01 '13 at 09:50

answered Jul 01 '13 at 09:13

Sashi Kant

13,277
9
44
71

Why down votes?? if anything wrong, please specify – Sashi Kant Jul 01 '13 at 09:55
incorrect - in oracle, `''` is indistinguishable from `NULL`. – Jeffrey Kemp Jul 03 '13 at 01:39

Difference between updation with NULL and ''?

2 Answers2