CODEWITHSUNDEEP
mysqlsql
Abe Miessler
Abe Miessler

Reputation: 85056

Count not showing correctly?

I have two tables on different databases. I am trying to find the differences in the number of records. So far I have tried:

select COUNT(*) 
from tabel1 a1 
where not exists 
(
  select * 
  from db2.table1 a2 
  where a1.id = a2.id
);

Which returns 31298. But if I run the count on the tables by them selves I get the following:

SELECT COUNT(*) FROM Table1 --  227429 

SELECT COUNT(*) FROM db2.Table1 --  256406

Which is a difference of 28977 records. Can anyone see what I am doing that would cause the difference in counts?

UPDATE

I am aware I can determine the difference by subtracting the counts. What I'm wondering is if it's possible to get an accurate difference using not exists. This is a simplified version of a more complex query.

Upvotes: 0

Views: 86

Answers (5)

codester
codester

Reputation: 117

Your db2.table1 likely has a few ids that do not exist in db1. Hence it is showing fewer differences in row count difference. e.g. db1.table1 has

1 Apple
2 Orange
3 Banana

db2.table1 has

1 Apple
4 Pineapple

Your first query will return a result of 2. Your row count comparison will return 1

Interestingly, note that your row count difference will never be higher than your first query result.

Upvotes: 0

invisal
invisal

Reputation: 11171

The following are the sample data

DB1.TBL

╔════╦══════════════════╗
║ ID ║       Name       ║
╠════╬══════════════════╣
║  1 ║ invisal          ║
║  2 ║ Thomas Jones-Low ║
║  4 ║ Mehran           ║
║  6 ║ Abe Miessler     ║
╚════╩══════════════════╝

DB2.TBL

╔════╦══════════════════╗
║ ID ║       Name       ║
╠════╬══════════════════╣
║  1 ║ invisal          ║
║  2 ║ Thomas Jones-Low ║
║  3 ║ Gordon Linoff    ║
║  8 ║ Martijn Pieters  ║
╚════╩══════════════════╝

The definition of "differences in the number of records" can mean many things. For example,

  • If you talk about "difference total number of row", then DB1.TBL and DB2.TBL has the difference of 0.
SELECT
ABS((SELECT COUNT(*) FROM DB1.TBL) -
    (SELECT COUNT(*) FROM DB2.TBL))
  • If you talk about "number of different records", then there are 4 
SELECT
(SELECT COUNT(*) FROM DB1.TBL) +
(SELECT COUNT(*) FROM DB2.TBL) -
(SELECT COUNT(*) FROM 
 DB1.TBL INNER JOIN DB2.TBL 
 ON DB1.TBL.id = DB2.TBL.id
) * 2

Upvotes: 0

Mehran
Mehran

Reputation: 16831

This is what you want:

SELECT ((SELECT COUNT(*) FROM Table1)) - ((SELECT COUNT(*) FROM db2.Table1))

The one you wrote is something completely different.

[UPDATE]

If you are trying to count the number of records in first database that do not exist in the second then your query is correct but your assessment is incorrect. You can't compare the result of arithmetic subtract with set subtract. They are totally different concepts.

Upvotes: 0

Thomas Jones-Low
Thomas Jones-Low

Reputation: 7161

There are records in table2 that don't have any corresponding entries in table1.

Your inner query only select values in table2 that also correspond to table1.

So if table1 has IDs

1
3
5

and table 2 has IDs

1
3
7

you would see count of 1 (id 5) because rows 1 and 3 match, but 7 doesn't match anything.

Upvotes: 1

Tom
Tom

Reputation: 6663

How about this:

SELECT (SELECT COUNT(*) FROM Table1) - (SELECT COUNT(*) FROM db2.Table1)

Upvotes: 1

Related Questions