Difference Between NULL and Blank in SQL

Hi,

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?

Awaiting your comments,
RegardsAm 1 Mar 2006 01:57:11 -0800 schrieb Shwetabh:

> Hi,
> 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?
> Awaiting your comments,
> Regards

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.

bye,
Helmut|||I thought aggreators like SUM ignored nulls...|||Please see some corrections inline...

helmut woess wrote:
> Am 1 Mar 2006 01:57:11 -0800 schrieb Shwetabh:
> > Hi,
> > 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?
> > Awaiting your comments,
> > Regards
> 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.

No, the comparison "<somevalue> = 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.

> Even comparing two fields which are both NULL will
> give NULL as result, not true!

The result of the comparison "NULL = NULL" also results in UNKNOWN.

> Or if you have something like "select
> sum(field) from ..." and one or more are NULL, then the result will be
> NULL.

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(*)

> Use always "if field is NULL ..." for NULL checking and for safety
> maybe something like "select sum( IsNull(field,0) ) from ...".

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).

> Check the function ISNULL() in the manual.
> bye,
> Helmut

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.

HTH,
Gert-Jan|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> 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?

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.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Difference between IS and =

Does anyone know the difference between the operators "IS" and "=" ?
I'm new to SQL, so...
Why for querying something like "... AND filed IS NULL" I have to use IS instead of = ?
Thanks anyway.Originally posted by 435 Gavea
Does anyone know the difference between the operators "IS" and "=" ?

I'm new to SQL, so...

Why for querying something like "... AND filed IS NULL" I have to use IS instead of = ?

Thanks anyway.

Hi The main advantage of IS is that you can use it for nulls, but you can't use '=' to check for nulls.

regards,
Chalam N|||It is a feature of the "3 valued logic" used by SQL. This says that any logical condition may have 3 possible values: TRUE, FALSE or NULL. NULL means "unknown" or "not applicable" or whatever.

Also, NULL is not a value like 0, 1 or 'hello', it is the "absence" of a value. And NULL is not equal to anything, not even to NULL. It just is NULL.

The result of the expression "field = NULL" is always NULL, regardless of whether field contains a NULL or a value. That may not make much sense on the face of it, but it does if you think the way SQL thinks:

Suppose I have 2 employee records:

insert into employee (name, salary) values ('John', NULL);
insert into employee (name, salary) values ('Mary', NULL);

The NULL value for salary in both cases doesn't mean they earn the same salary, it means we don't know what it is at the moment. So the query:

select name
from employee
where name != 'John'
and salary = (select salary from employee where name='John');

... should not return 'Mary'. We don't know their salaries, so we can't claim that they earn the same salary.

Nor do we know that they don't earn the same salary! Maybe they do, for all we know. Hence this query must return no rows either:

select name
from employee
where name != 'John'
and salary != (select salary from employee where name='John');

In other words, NULL is neither equal to, nor not equal to, NULL! It just is NULL...|||Now THAT is a great reply!

Ain't this what the forums are all about! :cool:|||Thanks for the help !!!

So NULL in SQL isn't like NULL in C, for example...sql

Determining what columns are all Null

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

Hi,

I wrote a procedure for that:

ALTER PROCEDURE spDetermineNullColumns

(

@.TableName SYSNAME

)

AS

BEGIN

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

DECLARE @.HIT INT

DECLARE @.sql NVARCHAR(2000)

SELECT IDENTITY(INT,1,1) AS IDCol,COLUMN_NAME

INTO #NullableColumns

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @.TableName AND IS_NULLABLE = 'YES'

SET @.ROWCOUNT = @.@.ROWCOUNT

SET @.I = 1

WHILE @.I <= @.ROWCOUNT

BEGIN

SET @.Hit = 0

SELECT @.sql = N'SELECT @.Hit = 1 '+ ' FROM ' + @.TableNAME +

' WHERE ' + COLUMN_NAME + ' IS NOT NULL'

FROM #NullableColumns WHERE IDCol = @.I

PRINT @.SQL

EXEC sp_executesql

@.query = @.sql,

@.params = N'@.Hit INT OUTPUT',

@.Hit = @.Hit OUTPUT

/* This here can be customized as you want to

IF @.Hit = 0

SELECT COLUMN_NAME + ' is completly NULL.'

FROM #NullableColumns WHERE IDCol = @.I

SET @.I = @.I +1

*/

END

END

GO

spDetermineNullColumns 'Categorie'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||DId that help Sean ?|||

You could do something like below:

select min(case when col1 is not null then 0 else 1 end) as col1_is_all_null

, min(case when col2 is not null then 0 else 1 end) as col2_is_all_null

...

from tbl