Inconsistent answers to simple queries I receive results that I consider inconsistent to simple queries.

-- This returns 3 rows
select * from persons
where person_id in (12,13,14)

-- This returns nothing
select *
from documents 
where person_id in (12,13,14)

-- This returns nothing !?!?
select *
  from persons
 where persons.person_id not in ( select documents.person_id from documents )


There is a FK on documents.person_id pointing to persons.person_id

Am I missing something?
This is my @@version:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)
As @Scott Hodgin has pointed out on his comments there is some NULL value for person_id in the documents table.

You can easily avoid it by adding where person_id is not null




create table persons (personID int);
create table documents (documentID int, personID int);

insert into persons values(10),(11),(12),(13),(14),(15);
insert into documents values (1,10),(1,11),(1,null),(2,null),(3,11),(3,15);
GO



12 rows affected






select * from persons where personID in (12,13,14);
GO



| personID |
| -------: |
|       12 |
|       13 |
|       14 |






select * from documents where personID in (12,13,14);
GO



documentID | personID
---------: | -------:






select personID from documents
GO



| personID |
| -------: |
|       10 |
|       11 |
|     null |
|     null |
|       11 |
|       15 |






select *
from   persons
where  personID not in (select personID from documents);
GO



| personID |
| -------: |






select *
from   persons
where  personID not in (select personID from documents where personID is not null);
GO



| personID |
| -------: |
|       12 |
|       13 |
|       14 |



dbfiddle here