Thursday, March 18, 2010

Where column alows null

DECLARE @Test TABLE (ID INT  IDENTITY (1,1),  
Name VARCHAR(250) NOT NULL, Street VARCHAR(250) NULL)

INSERT INTO @Test
SELECT 'Ashok', NULL UNION ALL
SELECT 'Guru', NULL UNION ALL
SELECT 'Babu', 'Idappadi' UNION ALL
SELECT 'Gopi', NULL UNION ALL
SELECT 'Balaji', NULL UNION ALL
SELECT 'Senthil', NULL UNION ALL
SELECT 'Saravanan', NULL UNION ALL
SELECT 'Suresh', NULL

I want to get the records whose street not equal to 'Idappadi'

The query given below returns nothing.
SELECT Name, Street FROM @Test
WHERE Street != 'Idappadi'

Then I modified as below which returns correct result.
SELECT Name, Street FROM @Test
WHERE ISNULL(Street, '') != 'Idappadi'

No comments: