Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

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'





Comments

Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...