CODEWITHSUNDEEP
mysqlsqlcountrowsequals
user6030295
user6030295

Reputation:

MySQL: count how many rows of two tables are the same

I have two tables, for example:

+---------+---------+
| Table A | Table B |
+---------+---------+
|      52 |      12 |
|      64 |       6 |
|      36 |      69 |
|      48 |      52 |
|      12 |         |
+---------+---------+

And I want to find how many rows of those tables are the same in one MySQL query. Any help?

(In our example 2)

Upvotes: 0

Views: 724

Answers (4)

Ullas
Ullas

Reputation: 11556

You can use EXISTS to find the matching rows between the two tables and then use COUNT.

Query

select count(*) from TableA a
where exists(
    select 1 from TableB b
    where a.col1 = b.col1
);

And if you count the unique values,

Query

select count(distinct *) from TableA a
where exists(
    select 1 from TableB b
    where a.col1 = b.col1
);

Upvotes: 2

Danula
Danula

Reputation: 3

You could use INNER JOIN

SELECT COUNT(*) FROM TableA A INNER JOIN TableB B on A.col1=B.col1

Upvotes: 0

Ashraful Islam
Ashraful Islam

Reputation: 12830

Use INNER JOIN
Let's assume TableA have column A and TableB have column B
So the query will be : 

SELECT count(*) FROM TableA INNER JOIN TableB ON (A = B);

Upvotes: 0

Hieu Le
Hieu Le

Reputation: 1132

You can use JOIN statement to counting it:

SELECT COUNT(*) FROM tb1
JOIN tb2 ON tb1.ColumnA = tb2.ColumnA

Upvotes: 0

Related Questions