SQL Query to get records with or without null values from database

Introduction

Here I will explain how to write a query to retrieve records with or without null values from database in SQL Server.

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 empty values are same but my query return records without NULL values 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

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

After that I 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 IS NOT NULL AND City <>''

Now the result is 

UserId	Name	City	Education
1	Suresh	Guntur	B.Tech

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

Select UserId,Name,City,Education from UserInfo where City IS NULL OR City =''

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

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email