Checking if contents of two table are identical in MySQL

Question

I have views in two databases V1 and V2 with same set of columns col1, col2.

Now I would like to know if they contains identical rows. In this context I have gone through this post: SQL compare data from two tables.

Following this I am running the below query:

  select * from v1.A
  minus
  select * from v2.A;

But I am getting the below error:

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from v2.A' at line 3

I am not getting any clue regarding how to fix this error.

Answer 1

You can simulate minus which is not supported in MySql with NOT EXISTS:

select t1.* from v1.A t1
where not exists (
  select * from v2.A t2
  where t2.col1 <=> t1.col1 and t2.col2 <=> t1.col2
)

I use the NULL-safe equal operator <=> instead of just = just in case there are nulls that need to be compared.

But if this query does not return any results it does not mean that the 2 views return the same rows, because v1.A may return a subset of the rows returned by v1.B.
So you also need:

select t2.* from v2.A t2
where not exists (
  select * from v1.A t1
  where t2.col1 <=> t1.col1 and t2.col2 <=> t1.col2
)

and maybe better a UNION ALL of the 2 queries to be sure:

select 1 from_view, t1.* from v1.A t1
where not exists (
  select * from v2.A t2
  where t2.col1 <=> t1.col1 and t2.col2 <=> t1.col2
)
union all
select 2 from_view, t2.* from v2.A t2
where not exists (
  select * from v1.A t1
  where t2.col1 <=> t1.col1 and t2.col2 <=> t1.col2
)