Difference in results between Sub-select & Equi-join

[shaslik]

Hi All,

Could someone please explain the reason why I would get different results from the following queries. In particular, what could cause the results in the equi-join to be higher?

Would really appreciate your help.

---

Application Sub-query

Select Count(*)
From TABLE_A
Where (TABLE_A.KEY IN
(SELECT KEY FROM TABLE_B))

3.2m

ODBC equivalent Sub-query

SELECT Count(TABLE_A.key) AS Cnt
FROM TABLE_A
WHERE TABLE_A.key IN
(SELECT TABLE_B.KEY FROM TABLE_B);

3.7m

ODBC equi-join

SELECT Count(TABLE_A.key) AS Cnt
FROM TABLE_A INNER JOIN TABLE_B
ON TABLE_A.key = TABLE_B.key;

6.6m

 

[fredericofonseca]

the last one is perfectly fine to have more results if there are several records on the second table matching the first table.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[shaslik]

Do you why the queries wouldn't return the same results?

 

[SQLSister]

Becasue you clearly have multiple records in table b which each will join to one record in table a. Suppose you have two tables
table a
AIdField Description
1 test
2 test2

table b
BIdField SomeOtherField FkIdField
1 junk 1
2 more junk 1

in the first query you would get a result of 1 because the subquery would return a result of 1 (assuming AIdField relates to FkIdField in table b)

but if you do a join then record 1 in table a relates to both record 1 and record 2 in table b hence a count of two.




"NOTHING is more important in a database than integrity." ESquared