Comparing multiple tables in mysql

Question

I have 4 tables with the same structure. This 4 tables are generated from another one, the main table.

• Main table -> A (10 000 rows)
  • table 1 -> a1 (2 000 rows)
  • table 2 -> a2 (3 000 rows)
  • table 3 -> a3 (4 000 rows)
  • table 4 -> a4 (1 000 rows)

It's supposed that 4 generated tables are disjoint, and their sum is equal to table A.

But i need to confirm this.

My question: How i compare this 4 tables, to see if they have any id in common?

I know that i can build a lot of querys to inner join a1 with a2, a1 with a3 and so on.

But i think that should be another better way.

Answer 1

I would just union all together all the ids from each table, group by each, and check whether any have counts more than one

select count(*) from (
select id from table1 union all
select id from table2 union all
select id from table3 union all
select id from table4) group by id having count(*)>1

If any id occurs more than once, it will show up in the results of this query.