MySQL: count how many rows of two tables are identical

advertisements

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)


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
);