Comparing MySQL Data Across Schema

There are all sorts of reasons you might want to compare data between two tables in different schema. A common situation is that you’re moving data from one schema to another, perhaps because you’re renaming the schema. Now you want to make sure the new schema has all the important data before you drop the original schema.

Here’s how you compare data across schema.

In general, the format for identifying a schema name is

SCHEMA.TABLE

So let’s say you have two schema named oldschema and newschema. You want to see which users are in the newschema which didn’t exist in the oldschema. You would do:

SELECT * FROM newschema.users where user_id not in (select user_id from oldschema.users);

This lets you look at rows across multiple schema to see which data is in one table but not the other.

Note that the values do have to match up for this to work. Both User_id values would need to be the same type of data.

Ask with any questions!