0

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?

  • Does you want to obtain the filednames which exists in one table and absent in another with the mark what table it occures, or directly the filednames which exists in table1 and absent in table2? – Akina Mar 20 at 12:25
  • 1
    What am I missing? You add doublequotes in GROUP_CONCAT("\"", column_name, "\"") , whereas they are excess. Simple SELECT column_name is correct. – Akina Mar 20 at 12:30
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.