0

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)

  • ( select documents.person_id from documents where person_id IS NOT NULL) – sepupic Nov 14 '17 at 9:52
  • ok, that was the best case scenario, I missed something stupid :-) do you want to make an answer out of it so that I accept? – carlo.borreo Nov 14 '17 at 9:55
  • @carlo.borreo - Have a look at SQL Server Three-Valued Logic and NOT IN clause with NULL values – Scott Hodgin Nov 14 '17 at 12:00
  • What returns select documents.person_id from documents? – McNets Nov 14 '17 at 12:02
  • Yes this post is rightly duplicate.Just using EXISTS clause will solve your problem. – KumarHarsh Nov 15 '17 at 5:51
2

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.