SQL Queries SELECT IN and SELECT NOT IN

Posted by Sequenzia on Stack Overflow See other posts from Stack Overflow or by Sequenzia
Published on 2012-11-09T00:01:48Z Indexed on 2012/11/13 17:01 UTC
Read the original article Hit count: 233

Filed under:
|

Does anyone know why the results of the following 2 queries do not add up to the results of the 3rd one?

SELECT COUNT(leadID) FROM leads 
WHERE makeID NOT IN (SELECT uploadDataMapID FROM DG_App.dbo.uploadData WHERE uploadID = 3 AND uploadRowID = 1)
AND modelID NOT IN (SELECT uploadDataMapID FROM DG_App.dbo.uploadData WHERE uploadID = 3 AND uploadRowID = 2)

SELECT COUNT(leadID) FROM Leads 
WHERE makeID IN (SELECT uploadDataMapID FROM DG_App.dbo.uploadData WHERE uploadID = 3 AND uploadRowID = 1)
OR modelID IN (SELECT uploadDataMapID FROM DG_App.dbo.uploadData WHERE uploadID = 3 AND uploadRowID = 2)

SELECT COUNT(leadID) FROM Leads

The first query is the count I need. The second one is to tell the user how many records were suppressed based on the contents of the DG_App.dbo.uploadData table. The third query is just a straight count of all the records.

When I run these the results of query 1 + the results of query 2 comes up about 46K records less than the count of the entire table. I have played with grouping the WHERE statements with () but that did not change the counts at all.

This is MSSQL Server 2012.

Any input on this would be great.

Thanks

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server