Select Distinct Not In Select Distinct Returning 0 Rows, Should be almost 1000

Question

Select Distinct FileId From dbo.Files

returns 3415 rows

Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1'

returns 2483 rows.

Select Distinct FileId From dbo.Files 
Where FileId In (Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1')

returns 2482 rows. Okay so someone synced this month but then deleted their file I assume.

BUT:

Select Distinct FileId From dbo.Files 
Where FileId NOT In (Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1')

Returns 0 rows.

I thought my connection was broken but I've run and rerun it and the results are consistent if also consistently wrong. Is this a sql bug? What am I missing here?

g.pickardou · Accepted Answer · 2015-07-20T16:17:55.527

0

It is possibly about the null's in the FileId column. Please check the nulls both in the inner and outer query executing them separately. If you find nulls then the following should work:

Select Distinct FileId From dbo.Files 
Where FileId In (Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1' 
AND FileId IS NOT NULL)

Select Distinct FileId From dbo.Files 
Where FileId NOT In (Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1' 
AND FileId IS NOT NULL)

edited Jul 20 '15 at 16:17

answered Jul 20 '15 at 16:12

g.pickardou

32,346
36
123
268

This does work but what about the null causes the Not In stop working? – William Mueller Jul 20 '15 at 16:32
William: For the explanation please refer the duplicate what is referred. It's the right explanation. My answer is adding a solution how to workaround this correct but weird behavior. In case that the explanation is not clear for you there, I am glad to explain here, trying it with other words, or more details. – g.pickardou Jul 21 '15 at 13:56
I did see the duplicate afterwards and read the explanation there, thanks – William Mueller Jul 21 '15 at 14:34

score 0 · Answer 2 · answered Jul 20 '15 at 16:17

0

Try

SELECT DISTINCT
a.FileID
FROM dbo.Files a
LEFT JOIN
(Select Distinct FileId From dbo.SyncHistory Where Date > '2015/7/1') b
ON a.FileID = b.FileID
WHERE b.FileID IS NULL

answered Jul 20 '15 at 16:17

SELECTCOUNTSTAR

100
2
11

Select Distinct Not In Select Distinct Returning 0 Rows, Should be almost 1000

2 Answers2