Q:

Comparing int columns in SQL

I have two int columns. OrgNum in the Org table and ServiceNum in the Service table. OrgNum has 1892 distinct rows. ServiceNum has 2362 distinct rows. 
select distinct orgnum from org
where orgnum in (select distinct servicenum from services)

The above query returns 3 records. 
select distinct orgnum from org
where orgnum not in (select distinct servicenum from services)

This query returns no records. 
How is this possible because either OrgNum exists in ServiceNum or it doesn't. Both are not possible. Is there a different way to compare int's in SQL?

A:

not in has strange behavior when the subquery returns any null values.  In this case, no rows are returned.
For this reason, I recommend that you always use not exists:
select distinct o.orgnum
from org o
where not exists (select 1 from services s where o.orgnum = s.servicenum);

This behavior is explainable.  In SQL, null generally has the semantics of "unkonwn" rather than "missing".  So:
1 in (2, 3)            -- returns false (you know it is not there)
1 in (null, 2, 3)      -- returns null (could be there or not).  BUT `null` is treated like false in where
1 not in (2, 3)        -- returns true
1 not in (null, 2, 3)  -- returns null.  But this is treated as false.

