Two sql's different results 1

[mpramods]

I have the following two sql's. The first one is by me and the second one(supposed to be more efficient) is recommended by the DBA.

SELECT COUNT(*) FROM TABLE1 WHERE ACCOUNT_NUMBER
IN (SELECT ACCOUNT_NUMBER FROM TABLE2);
COUNT(*)
----------
259463

SELECT COUNT(a.*) FROM TABLE1 a, Table2 b
WHERE a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER;
COUNT(a.*)
----------
335228

Both of them give different counts. Can somebody tell why is there a difference in the count? Also why is the 2nd one supposed to be more efficient.

Thanks,
Pramod

 

[jimbopalmer]

I am betting that Account_number is not unique in Table2 so records in Table1 match more than one record as the second query is written, but in the first query each record in table1 is selected just once.

The first query examines each record in table2 for every record in Table1, while the second query only has to go through each table one time.

I tried to remain child-like, all I acheived was childish.

 

[mpramods]

Jimbopalmer,

you are right. I had created the Table2 and had unknowingly inserted some duplicates. Hence the different results. I removed the duplicates and both the queries give the same result now.

Also thanks for the explanation for my 2nd question too.

Cheers,
Pramod