NOT IN on Null value returns incorrect results

Question

Post reply

NOT IN on Null value returns incorrect results

zach_john

SSCertifiable

Points: 5922
More actions
February 20, 2009 at 9:58 am

#197313

I run this qry across a number of servers however if the database server doesn't have a NASA and NASA_Prod database, returning nulls, the query falls a part. I can fix it by moving zero when null.
I'm confused why if x is 5 would this record not be true?
select * where x not in (1,2,3,,)
Actuall code
declare @ex_db1 int
declare @ex_db2 int
declare @ex_db3 int
declare @ex_db4 int
declare @ex_db5 int
select @ex_db1=db_id('DBA_Audit')
select @ex_db2=db_id('tempdb')
select @ex_db3=db_id('DBA')
select @ex_db4=db_id('NASA')
select @ex_db5=db_id('NASA_Prod')
SELECT distinct
substring(ServerName,1,20) as 'SQLServerName',
substring(LoginName,1,20) as 'NTID_Name',
substring(HostName,1,20) as 'HostName',
FROM DBA_Audit.dbo.tbAuditUsers
WHERE DatabaseID not in (@ex_db1,@ex_db2,@ex_db3,@ex_db4,@ex_db5)

John Zacharkan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yeah the issue is db_id can return null:

select db_id('Bob')

i doubt you have a 'Bob' database...

your example would actually be

select * where x not in (1,2,3,NULL,NULL) --<<--NOT IN NULL RETURNS NULL

you need to do something like this:

select @ex_db1=ISNULL(db_id('DBA_Audit'),0)

select @ex_db2=ISNULL(db_id('tempdb'),0)

select @ex_db3=ISNULL(db_id('DBA'),0)

select @ex_db4=ISNULL(db_id('NASA'),0)

select @ex_db5=ISNULL(db_id('NASA_Prod'),0)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

Doing it this way is slightly more flexible if you want to add more databases to your exclusion list:

DECLARE @ex_db TABLE (id int)

INSERT INTO @ex_db

SELECT db_id('DBA_Audit') UNION ALL

SELECT db_id('tempdb') UNION ALL

SELECT db_id('DBA') UNION ALL

SELECT db_id('NASA') UNION ALL

SELECT db_id('NASA_Prod')

SELECT DISTINCT

SUBSTRING(AU.ServerName, 1, 20) AS 'SQLServerName',

SUBSTRING(AU.LoginName, 1, 20) AS 'NTID_Name',

SUBSTRING(AU.HostName, 1, 20) AS 'HostName'

FROM DBA_Audit.dbo.tbAuditUsers AU

WHERE NOT EXISTS(SELECT 1 FROM @ex_db WHERE (id = AU.DatabaseID))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Another way to think about NULL is this; NULL = NULL is false, NULL <> NULL is also false. This is why you check for NULL using IS NULL or IS NOT NULL.

Try it out yourself:

declare @TestVar1 int,

@TestVar2 int;

select

@TestVar1 as TestVar1,

@TestVar2 as TestVar2,

case when @TestVar1 = @TestVar2 then 'Null = Null'

when @TestVar1 <> @TestVar2 then 'Null <> Null'

else 'Null = Null and Null <> Null do not work'

end

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Another way to think of it: NULL isn't a value in a column, it's the status of a column (the absence of a value).

Here is one of many articles discussing the subject.

http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

zach_john SSCertifiable Points: 5922 More actions · Answer 5

Very well said Lynn it comes back to me like a sledge hammer.

Guys thanks for the responses Andrew I like your idea of Union into a table var and will incorporate that code.

John Zacharkan