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'
