Skip to main content

Difference Between NULL and Blank in SQL

Yes, a very big difference! Be carefully if you have NULL valued fields. If
you do a compare and one or both are NULL, then the result is always NULL,
never true or false. Even comparing two fields which are both NULL will
give NULL as result, not true! Or if you have something like "select
sum(field) from ..." and one or more are NULL, then the result will be
NULL. Use always "if field is NULL ..." for NULL checking and for safety
maybe something like "select sum( IsNull(field,0) ) from ...". Check the
function ISNULL() in the manual.

No, the comparison " = NULL" will result in UNKNOWN. If the
predicate is part of the WHERE clause, then the row is removed from the
result. If the predicate is part of a CHECK constraint, then the row is
allowed.
[color=blue]
> Even comparing two fields which are both NULL will
> give NULL as result, not true![/color]

The result of the comparison "NULL = NULL" also results in UNKNOWN.
[color=blue]
> Or if you have something like "select
> sum(field) from ..." and one or more are NULL, then the result will be
> NULL.[/color]

NULL values are excluded from aggregates. If one or more NULL values are
encountered, SQL Server will issue a warning stating that these rows are
disregarded. The only exception is the aggregate COUNT(*)
[color=blue]
> Use always "if field is NULL ..." for NULL checking and for safety
> maybe something like "select sum( IsNull(field,0) ) from ...".[/color]

This only good advice if you want a NULL row to be treated as 0 in an
aggregation (for example the calculation of an average).
[color=blue]
> Check the function ISNULL() in the manual.
>
> bye,
> Helmut[/color]

In addition to Helmut's warnings, note that NULLs are promoted in
expressions. So if you write SELECT A + B AS sum_of_A_and_B and either A
or B is NULL, then sum_of_A_and_B will be NULL.



Shwetabh (shwetabhgoel@gmail.com) writes:[color=blue]
> My question is, is there any difference between a NULL and a Blank
> (Unknown, Not Applicable) field in MS SQL or are they the same?[/color]

As said in other posts, they are not. I just like to add one thing:

NULL stands for "unknown, not appliable". An empty string, is very much
a defined value as far as SQL is concerned.

Comments

Post a Comment

Popular posts from this blog

URL Rewritting

http://www.simple-talk.com/dotnet/asp.net/a-complete-url-rewriting-solution-for-asp.net-2.0/ http://msdn.microsoft.com/en-us/library/ms972974.aspx URL Rewriting in ASP.NET Summary: Examines how to perform dynamic URL rewriting with Microsoft ASP.NET. URL rewriting is the process of intercepting an incoming Web request and automatically redirecting it to a different URL. Discusses the various techniques for implementing URL rewriting, and examines real-world scenarios of URL rewriting. (31 printed pages) Download the source code for this article. Contents Introduction Common Uses of URL Rewriting What Happens When a Request Reaches IIS Implementing URL Rewriting Building a URL Rewriting Engine Performing Simple URL Rewriting with the URL Rewriting Engine Creating Truly "Hackable" URLs Conclusion Related Books Introduction Take a moment to look at some of the URLs on your website. Do you find URLs like http://yoursite.com/info/dispEmployeeInfo.aspx?EmpID=459-099&type=summ...
Post a Comment
Read more

SEND A PDF FILE AS AN ATTACHEMENT OF MAIL!

System.Net.Mail.MailMessage m1 = new System.Net.Mail.MailMessage(); m1.From = "manpreet@gmail.com" m1.Subject = "Test mail "; m1.Body = str.ToString(); m1.IsBodyHtml = true; m1.To.Add("jasdeep@gmail.com"); m1.CC.Add("jasdeep123@gmail.com"); m1.Attachments.Add(new Attachment(strserverpath + @"\pdf\" + PdfFileName)); smtp.Send(m1);
Post a Comment
Read more

Sql Server Tips

IF WE WANT TO SELECT  TOP N ROWS  FROM A TABLE ,  WE USE THE FETCH NEXT  SELECT    * FROM  table1   ---//First m rows ignore first n rows ORDER BY id OFFSET n ROWS FETCH NEXT m ROWS ONLY if n=0, m=10 then first  10 rows if n=10, m=10 then first  10 rows start from 11th row Row_number()   SELECT  ROW_NUMBER() OVER(order by db.id) Insertion Simultaneouly in temp table with output clause DECLARE @table1 table (   Id int,   name nvarchar(50) ); DECLARE @table2 table (  Id int,  name nvarchar(50) ); INSERT INTO @table2 OUTPUT INSERTED.*   INTO @table1   select top 10 id, name  from FinalTable SELECT * FROM @table2; SELECT * FROM @table1;
Post a Comment
Read more