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: 264
sql
|sql-server
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