Selecting difference between two tables returns different number of results than counts of same two tables

Question

I'm using SQL Server and when I run SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2 it returns 1610 entries. When I query

SELECT COUNT(*) FROM TABLE1 
SELECT COUNT(*) FROM TABLE2

I get 6399 and 4583, the difference of which is 1546, not 1610 as I would expect from the first query. What could be causing this discrepancy between the number of results returned and the difference of the individual table counts?

Answer 1

The EXCEPT operator is not going to limit the rows returned from TABLE1 to the difference between the row counts in TABLE1 and TABLE2 - it will eliminate from the resultset every row in TABLE1 that has a matching row in TABLE2.

In your example, the difference in row counts is 1816 but your resultset is 1610 entries. This means there are 4789 entries in TABLE1 that match a row in TABLE2, suggesting that some of the rows in TABLE1 are not unique.

You can test this out by running SELECT COUNT(DISTINCT *) FROM TABLE1. If the value is less than 6399, then there are duplicate rows of data and some of these are likely being eliminated by the EXCEPT operator hence the difference in row counts.