Help me understand something. I have a database of patients. A small percentage (15%) will actually have a value in the field "MedicalRecordNumber". Of that small percent, many will start with the characters "IN" or "IG" which denotes a particular status. Recently I've been asked to modify a query to omit these users. Seems simple enough but the results are messing with my head.
Total records (select * from patientprofile where pstatus = 'A'
; (Active patients) = 4,724
Count of all patients with IG or IN as a prefix
select count(*) from patientprofile
where pstatus = 'A'
and left(MedicalRecordNumber,2) in ('IG','IN')
Logically speaking, the inverse should be obvious and return the inverse of 4,724 - 531 = 4193
but by simply changing the IN to NOT IN returns 248 which I didn't expect. This apparently has to do with the NULL values being ignored and I have to add "or MedicalRecordNumber is null" to the value. Why is that? And where the heck did the value of 248 come from?? A null value is not in the list values and should therefore be included. (By the way, I get the same behavior if I try to use <> or != instead of the "IN". I've worked with Oracle for many years and this doesn't seem to be the case. I really could use an explanation or understanding of why this scenario occurs.