MySQL – Get Column Difference Between Same Table in Different Schemas

MySQL

Suppose I have the following tables:

schema_a.products

+----+------+------+-------+
| id | code | name | price |
+----+------+------+-------+

schema_b.products

+----+------+------+
| id | code | name |
+----+------+------+

I'm trying to get the column differences between the two tables. So I'd like the query to return the price column (as it exists in schema_a.products but not schema_b.products)

I thought I could select the column names from INFORMATION_SCHEMA for schema_a.products and then omit any column names selected from schema_b.products, like so:

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN (
  SELECT GROUP_CONCAT("\"", column_name, "\"") 
  FROM information_schema.columns
  WHERE table_name = "products"
  AND table_schema = "schema_b"
);

However this just returns all of the column names. If I run the subquery separately, and then replace it with the result, it works fine.

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN ("id", "code", "name");

What am I missing?

Best Answer

Find column names which exists in the table in schema_a.products and not exists in schema_b.products:

SELECT t1.column_name
FROM (SELECT column_name
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema = 'schema_a') t1
LEFT JOIN (SELECT column_name
           FROM information_schema.columns
           WHERE table_name = 'products'
             AND table_schema = 'schema_b') t2 ON t1.column_name = t2.column_name
WHERE t2.column_name IS NULL

Find column names which exists in the table in one schema and not exists in another schema

SELECT column_name, table_schema 
FROM (SELECT column_name, table_schema 
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema IN ('schema_a', 'schema_b')) both
GROUP BY column_name
HAVING GROUP_CONCAT(table_schema ORDER BY table_schema) != 'schema_a,schema_b'

If only_full_group_by is enabled, wrap table_schema field in output with some aggregate function applicable to string data type (MIN, MAX, GROUP_CONCAT, etc.).

If tablenames in schemas are not equal, replace WHERE condition with

WHERE (table_name, table_schema) IN (('tablename1', 'schemaname1'),
                                     ('tablename2', 'schemaname2'))

and add table_name field (maybe wrapped too) to output list.

Related Question