Understanding using not in ( ) 1

[azoe]

I used this in a Where clause:
ps.patient_status_id not in ('PS150720QADP', 'PS155M3NI31J')

and later I found out that ps.patient_status_id = ''
weren't included in the dataset.

I went to SQL Help and looked up IN but I didn't see anything about that.

Now I have changed it to this:
(ps.patient_status_id not in ('PS150720QADP', 'PS155M3NI31J')
or isnull(ps.patient_status_id, '') = ''
or ps.patient_status_id = '')

I thought I would get anything returned that was not in the list. Can anyone explain why that was not true? the patient_status_id is a foreign key from another table if that matters. The data type is char.

Thank you

 

[gmmastros]

The real problem here is with NULL. NULL will never compare positively with ANYTHING. Not even NULL. Check it out.

What do you think would be the result of the following...
Select 1 Where NULL = NULL

If you quessed that there would be no records returned, you would be right.

To get your query to run correctly, you could have done the following...

IsNull(ps.patient_status_id, '') not in ('PS150720QADP', 'PS155M3NI31J')


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

but doing just this

ps.patient_status_id not in ('PS150720QADP', 'PS155M3NI31J')

should bring empty patient_status_id's

try...

ps.patient_status_id<>'S150720QADP' OR ps.patient_status_id<>'PS155M3NI31J'

-DNG

 

[DotNetGnat]

hmm...good point, George.

-DNG

 

[azoe]

George - That's better than what I had. Thanks so much for the info too.
Thanks to you also DNG