retrieve records with or without null values from database in sql server

how to retrieve records with or without null values from database in sql server

Introduction

Here I will explain how to retrieve records with or without null values from database.

Description

I have one table with three fields like this 

UserId	Name	City	Education
1	Suresh	Guntur	B.Tech
2	Nagaraju	NULL	MCA
3	Sai		MBA
4	Madhav	null	MBBS

Now I need to retrieve records from this table where City not null in this situation I have written query like this.

Select UserId,Name,City,Education from UserInfo where City <>'NULL'

Now this Query is return values like this 

UserId	Name	City	Education
1	Suresh	Guntur	B.Tech
3	Sai		MBA

I thought that NULL or null or empty values are same but my query return records without NULL and nullvalues but it return records for empty values at that time after search in many website I found interesting point like this 

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

Based on this I have written query like this 

Select UserId,Name,City,Education from UserInfo where City IS NOT NULL

UserId	Name	City	Education
1	Suresh	Guntur	B.Tech
3	Sai		MBA
4	Madhav	null	MBBS

 

If I written query based on above statement it return empty and null records also just like above tables records.

After that I have written query to retrieve records without null vales in City Column it has worked perfectly for me 

Select UserId,Name,City,Education from UserInfo where City NOT IN('NULL','null','')

 Now the result is 

UserId	Name	City	Education
1	Suresh	Guntur	B.Tech

If you want get records where City contains NULL or null or empty you need to write query like this

Select UserId,Name,City,Education from UserInfo where City IS NULL or City IN('null','')

Now the result is

UserId	Name	City	Education
2	Nagaraju	NULL	MCA
3	Sai		MBA
4	Madhav	null	MBBS

This way we can get retrieve records with or without null values from database