Why there is a difference in query results?

Question

1. SELECT count(*) FROM Table1 t1
2. SELECT count(*) FROM Table1 t1 Where t1.ID not in (select ID from Table2)
3. SELECT Count(*) FROM Table1 t1 inner join Table2 t2 on t1.ID = t2.ID

Results:-

1. 83
2. 62
3. 26

Why there is difference in results of 2nd and 3rd query when total is 83 from Ist Query? Is it not supposed to be either 57 (from 2nd query) if 3rd's result is 26

OR

21 (from 3rd query) if 2nd's result is 62? Can someone help me out here whats wrong in my select statements above?

Answer 1

My guesses:

• ID columns are not unique

• ID columns may be NULL

Answer 2

You are getting multiple rows for the same ID in #3.

Answer 3

I suspect that queries 1 and 2 are correct and that you have a few records in table 2 with the same ID so the 3rd query is returning multiple matches for some IDs in table 1.

Answer 4

You probaly have duplicate records in the second table for ID...hence you get more...just guessing...

Answer 5

T1 IDs: 11, 12, 13
T2 IDs: 11, 11, 11

T1.Count = 3
T1.WhereNotInT2.Count = 2
T1.JoinT2.Count = 3

To fix your queries, change Count(*) to Count(DISTINCT T1.ID)