CODEWITHSUNDEEP
sqlsqlitecountduplicates
Nirmal Patel
Nirmal Patel

Reputation: 75

Count the overlapping values between two tables?

I have two tables that are structured the same with a sequence column and I am trying to count the number of sequences that show up in two different tables. I am using this right now:

SELECT A.sequence FROM p2.pool A WHERE EXISTS (SELECT * from
p1.pool B WHERE B.sequence = A.sequence)

And then I was going to count the number of results. Is there an easier way to do this using COUNT so I don't have to get all of the results first?

Upvotes: 1

Views: 2458

Answers (1)

CL.
CL.

Reputation: 180060

Yes, there is an easier way using COUNT:

SELECT COUNT(*)
FROM p2.pool A
WHERE EXISTS (SELECT *
              FROM p1.pool B
              WHERE B.sequence = A.sequence)

You could also use a join instead of a subquery, but the speed is unlikely to change:

SELECT COUNT(*)
FROM p2.pool A
JOIN p1.pool B ON A.sequence = B.sequence

Upvotes: 1

Related Questions