Get num of records with identical values from 2 tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get the number of identical values from 2 tables. The problem
is the query takes a really long time to run. Is there a way to do this
faster? This is what I do now:

select t1.f1 from t1 where t1.f1 IN (Select distinct t2.f2 from t2)

Thanks!
 
Hi,



SELECT COUNT(*)
FROM t1 INNER JOIN t2 ON t1.f1=t2.f2




You really don't need to use a DISTINCT, in your proposed syntax. The
DISTINCT use in this manner probably kill the possibility to use an index on
t2.f2 (if such an index exists). Furthermore, an index on t1.f1 and an index
on t2.f2 would probably enhance the run time speed of execution. If you
don't have them, and if one of the table have close to a thousand, or more,
records, explore the possibility to add them.




Hoping it may help,
Vanderghast, Access MVP
 
Dear Mike:

This is often faster:

SELECT f1
FROM t1
LEFT JOIN t2 on t2.f2 = t1.f1
WHERE t2.f2 IS NOT NULL

You could actually just use the INNER JOIN and drop the WHERE clause. As
written above, you can see those rows NOT duplicated by removing "NOT" from
the above.

SELECT f1
FROM t1
INNER JOIN t2 on t2.f2 = t1.f1

The above is equivalent.

SELECT f1
FROM t1
LEFT JOIN t2 on t2.f2 = t1.f1
WHERE t2.f2 IS NULL

Shows the unduplicated rows.

Tom Ellison
 
Thank you, what I didn't include is that there are instances when the 2
fields have duplicate values within which say I want to exclude. What would
you suggest then?
 
Thank you, what I didn't include is that there are instances when the 2
fields have duplicate values within which say I want to exclude. What would
you suggest then?
 
Hi,



In this case, you need a DISTINCT or a GROUP BY. If the table is quite
large, consider to create a temporary table (with the distinct values) with
an index.



Hoping it may help,
Vanderghast, Access MVP
 
Dear Mike:

My examples posted previously shows how to show those the have duplicates as
well as those that don't. Have you tried both? Or do you want something
else. I'm not getting what else you want. Give example, please.

Tom Ellison
 
Sorry Tom, what I mean is that I want # of records that exist in t1.f1 and
t2.f2 that are the same(match/equal/duplicate), but I also want to exclude
duplicates within t1.f1 and t2.f2, hence the query I was using did this but
was too slow when the tables are large.

select t1.f1 from t1 where t1.f1 IN (Select distinct t2.f2 from t2)

Thanks Again!
 
Back
Top