Blog Post

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'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating