IS NOT NULL returning NULLs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

IS NOT NULL returning NULLs

Author

Topic

M1North
Starting Member

9 Posts

Posted - 2011-11-18 : 15:46:42

Hello

I am querying a view using the below:

SELECT
FileTitle, DeletedBy, ActiveFromDate, ActiveToDate
FROM
SomeFiles
WHERE
(DeletedBy IS NOT NULL)
OR
((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))

The results returned include columns where DeletedBy is null (second active date criteria is false)... If I change it to (DeletedBy != null), the null records are excluded.

Strangely, if I do something like:

SELECT
FileTitle, DeletedBy, ActiveFromDate, ActiveToDate, LEN(DeletedBy)
FROM
SomeFiles
WHERE
(DeletedBy IS NOT NULL)
OR
((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))

The null records are also excluded.

Please could somebody explain what is going on here.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-18 : 15:49:42

It's due to the use of OR. So exactly what do you want to happen with that entire WHERE clause?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 15:50:15

I can go into more detail, but your logic is using an OR predicate. So, a row only needs to meet one of the two criterion in your WHERE clause.
EDIT: ahh too slow.. :(

M1North
Starting Member

9 Posts

Posted - 2011-11-18 : 16:01:26

Hi

The second active date criteria equates to false - the problem is pretty much to do with the DeletedBy clause because the same query using DeletedBy != null, correctly removes the null records...

Cheers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 16:44:50

I don't see a difference between where clause in the two queries you posted. Also, using "NOT NULL" versus "!= NULL" can have vastly different means, depending on your ANSI_NULLS settings.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 16:49:01

Maybe this will help illustrate. Try changing the ANSI_NULLS setting from ON to OFF:

SET ANSI_NULLS ON -- SQL SERVER Defaul ON
--SET ANSI_NULLS OFF

DECLARE @Foo TABLE (ID INT, Val INT)

INSERT @Foo (ID, Val)
VALUES
(1, NULL),
(2, 20),
(3, NULL),
(4, 40),
(5, 50),
(6, NULL)


SELECT *
FROM @Foo
WHERE Val IS NOT NULL
OR ID BETWEEN 3 AND 6


SELECT *
FROM @Foo
WHERE Val <> NULL
OR ID BETWEEN 3 AND 6

M1North
Starting Member

9 Posts

Posted - 2011-11-18 : 17:10:49

Hi

I'm probably not making myself clear... I have the following data:

FileTitle | DeletedBy | ActiveFromDate | ActiveToDate
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000
Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000

The following query returns null records (incorrectly):

SELECT
FileTitle, DeletedBy, ActiveFromDate, ActiveToDate
FROM
SomeFiles
WHERE
(DeletedBy IS NOT NULL)
OR
((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))

BUT, the following do not:

(Note, all that is different in the below query to the above is the addition of the LEN calculation)
SELECT
FileTitle, DeletedBy, ActiveFromDate, ActiveToDate, LEN(DeletedBy)
FROM
SomeFiles
WHERE
(DeletedBy IS NOT NULL)
OR
((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))

And also,

SELECT
FileTitle, DeletedBy, ActiveFromDate, ActiveToDate
FROM
SomeFiles
WHERE
(DeletedBy != NULL)
OR
((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))

Cheers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 17:21:47

Humm, maybe you can show us yor result. Did you switch the greater than/less than around? I don't get any results for any of those queries and the given sample data (nor did I expect to).

M1North
Starting Member

9 Posts

Posted - 2011-11-18 : 17:29:01

It gets even more strange... I am only getting this behaviour on my database. If I export the data on to another database, I don't experience the issue.

Could this be a DB specific setting?

I'm getting all records returned from the 1st query

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 17:38:16

Odd. Is it possible that NULL got put in as a string and not an actual value?

M1North
Starting Member

9 Posts

Posted - 2011-11-18 : 17:42:34

No I've checked that, plus I get the colour differentiation in SQL2008 for nulls...

M1North
Starting Member

9 Posts

Posted - 2011-11-19 : 06:32:35

Hi Lamprey

I could e-mail you a copy of the database where you'll be able to replicate this - if you have the time...

Cheers

M1North
Starting Member

9 Posts

Posted - 2011-11-19 : 06:33:30

quote:
Originally posted by Lamprey

Odd. Is it possible that NULL got put in as a string and not an actual value?

Hi Lamprey

I could e-mail you a copy of the database where you'll be able to replicate this - if you have the time...

Cheers

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-19 : 12:20:06

!= NULL isn't a valid test of anything.

Everything is != NULL. Even NULL != NULL.

And...this test

ActiveFromDate >= GETDATE() AND ActiveToDate <= GETDATE()

is unlikely to match anything either.

I'm betting Lamprey is right that the sting value 'NULL' is in there.

M1North
Starting Member

9 Posts

Posted - 2011-11-20 : 04:42:50

quote:
Originally posted by russell

!= NULL isn't a valid test of anything.

Everything is != NULL. Even NULL != NULL.

And...this test

ActiveFromDate >= GETDATE() AND ActiveToDate <= GETDATE()

is unlikely to match anything either.

I'm betting Lamprey is right that the sting value 'NULL' is in there.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-11-20 : 09:08:48

How is the view 'SomeFiles' defined? Could be something in the view is causing this issue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-21 : 00:47:16

Check youe ANSI_NULL setting on both yousr machine and the machine that works.

N 56°04'39.26"
E 12°55'05.63"

M1North
Starting Member

9 Posts

Posted - 2011-11-21 : 05:06:16

quote:
Originally posted by jeffw8713

How is the view 'SomeFiles' defined? Could be something in the view is causing this issue.

Hi

The view is defined as:

SELECT
dbo.SomeFiles.FileTitle,
dbo.SomeFiles.ActiveFromDate,
dbo.SomeFiles.ActiveToDate,
dbo.aspnet_Users.UserName AS DeletedBy
FROM
dbo.SomeFiles
LEFT OUTER JOIN
dbo.aspnet_Users
ON
dbo.Files.DeletedBy = dbo.aspnet_Users.UserId

Could it be the left join?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-21 : 16:28:35

Maybe you should try reading the answers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources