difference between select * from tablename where id='' and select * from tablename where id is null

Question

I want to know what is the meaning or purpose of inserting a value like-

insert into table_name (column_name) values('');

And what is the difference b/w above statement and not inserting a value for the column(means adding null)

Answer 1

Selects records where ID is an empty string

select * from tablename where id=''

Selects records where ID is NULL

select * from tablename where id is null

A NULL indicates absence of data where as an '' (empty string) is actually a string of length 0.

For you better understanding:

SELECT LEN(NULL) AS LEN_NULL, LEN('') AS LEN_EMPTY_STR

would give :-

LEN_NULL    LEN_EMPTY_STR
NULL    0

Answer 2

in case if you set NOT NULL as a property of the column - this doesn't give you an error as a result of insert.

Answer 3

"" can be classed as an empty string

null means null, its a non existent value.

Answer 4

Null mean cell is not initialize where '' mean it is initialize by ''