How do I account for count discrepancy in SELECT DISTINCT?

user_474657 0
Viewed 44

I don't think this is even a SQL question, I'm just struggling with the logic, and likely having a Moment of Stupid.

I'm trying to find the number of email addresses that exist in table cms but not in table cm. I'm using postgres 10.4. Both tables each comprise a single varchar column, "email".

Both these queries return a count of 18309:

SELECT COUNT(DISTINCT email) FROM cms;
SELECT COUNT(email) FROM cms;

Both these queries return a count of 17949:

SELECT COUNT(DISTINCT email) FROM cm;
SELECT COUNT(email) FROM cm;

which means there are... 18309 - 17949 = 360 more records in cms than cm.

I'm then querying the diffs with:

SELECT COUNT(cms.email) FROM cms LEFT JOIN cm ON cm.email = cms.email WHERE cm.email IS NULL;

This query returns 369.

Where is the discrepancy coming from?

  • Both tables contain no duplicate records in themselves.
  • One table has 360 more records than the other.
  • There are 369 records that exist in one table but not the other.

What are those nine records??

duplicates postgresql sql
2 Answers

Nothing mysterious. You have emails in each table that are not in the other.

You have:

  • 18,309 emails in cms, of which 17,940 (18,309 - 369) are in cm.
  • 17,949 emails in cm of which 17,940 are in cms.
  • You have 369 that are only in cms.
  • You have 9 that are only in cm.
user_11440350

Not only there are emails in the big table that are not in the small table, but also some emails from the small table are missing in the big one.

You could use a full join to count orphans from both tables:

select 
    count(*) filter(where cm.email  is null) in_cms_but_not_in_cm,  -- 369
    count(*) filter(where cms.email is null) in_cm_but_not_in_cms   -- 9
from cm
full join cms using(email)
where cm.email is null or cms.email is null
user_106767160
Related