Handling Null Values in SQL Server

What is NULL?

In SQL, A Null is an unknown or undefined value. Some interesting operations on NULL.

--query                 --result

select 3*NULL      --NULL

select 3+NULL      --NULL

select NULL/0      --NULL

select 'queryingsql' where null=null --Null never be equal to Null unless SET ANSI_NULLS OFF.

Now See an Example

create table tbltest

(

id int identity(1,1),

name varchar(12),

city varchar(200)

)

insert into tbltest(name,city)values

('a',NULL)

,('b','Meerut')

,('c','Rampur')

,('d',NULL)

,('e',NULL)

,('f','NOIDA')

select *from tbltest

Output:

id            name    city

1              a              NULL

2              b             Meerut

3              c              Rampur

4              d             NULL

5              e             NULL

6              f              NOIDA

Find rows that does not have city Meerut.You make a query like

select *from tbltest where city<>'merrut'

Output:

id            name    city

3              c              Rampur

6              f              NOIDA

But this result is wrong. Result should have a, c, d, e, and f.  Where are a, d, and e.as

id            name    city

1              a              NULL

3              c              Rampur

4              d             NULL

5              e             NULL

6              f              NOIDA

It was due to NULL value. To resolve this Problem either use ISNULL() as

select *from tbltest where isnull(city,'')<>'merrut'

Or use  COALESCE()

select *from tbltest where COALESCE(city,'')<>'merrut'

Prev--->For XML Clause in SQL Server                                                          Next--->Cursor in T-SQL