Tuesday 5 February 2013

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

No comments:

Post a Comment

Please leave a comment for this post