Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Nov 24, 2010
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 RSS subscribe by email Subscribe by Email

6 comments :

Anonymous said...

nice INFO

Anonymous said...

i have a table 'size'(Table Name) and in this table fields are size 'compname','branchname','sizename','Qty'
values insert in table-
(cmp001,brc001,si001,'200'
cmp001,brc001,null,400)
i want to get Qty-400 where size is null
(only sigle query according to need both Qty are retrive)
pls

Anonymous said...

ur website is very good...it's really help me.........

Anonymous said...

yep yep,, faced this question yestrday in an interview !!

Sheetal said...

In my table i have null values in multiple column and i want to fetch all records without null values so what will be the query.... Please help me....

Anonymous said...

Nice and Simple explanation

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.