CODEWITHSUNDEEP
mysql
Sharpeye500
Sharpeye500

Reputation: 9063

Compare two table and find matching columns

I have two tables table1 and table2, i need to write a select query which will list me the columns that exist in both the tables. (mysql)

I need to do for different tables (2 at a time)

Is this possible?

I tried using INFORMATION_SCHEMA.COLUMNS but am not able to get it right.

Upvotes: 5

Views: 14768

Answers (3)

yas mimi
yas mimi

Reputation: 1

To find columns that are in 'table1' and not in 'table2' try this :

SELECT h.COLUMN_NAME 
FROM  INFORMATION_SCHEMA.COLUMNS h
WHERE h.TABLE_NAME = 'table1' AND 
h.COLUMN_NAME not  IN (
SELECT a.COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS a
 JOIN INFORMATION_SCHEMA.COLUMNS b
 ON a.COLUMN_NAME = b.COLUMN_NAME
 AND b.TABLE_NAME = 'table2'
 AND b.TABLE_SCHEMA = database() 
 WHERE a.TABLE_NAME = 'table1'
 AND a.TABLE_SCHEMA = DATABASE() 
 )

nb: if you want the opposite you have just to switch between tables name in the query

Upvotes: 0

Remy
Remy

Reputation: 12693

In case someone needs the opposite:
Find all columns that exist in one table, but are missing in the other:

 SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS a 
 WHERE a.TABLE_NAME = 'craft_content'
 AND a.TABLE_SCHEMA = 'craftcms2'
 AND a.COLUMN_NAME NOT IN (
   SELECT b.COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS b 
   WHERE b.TABLE_NAME = 'craft_content'
   AND b.TABLE_SCHEMA = 'craftcms'
 )

Upvotes: 12

Wrikken
Wrikken

Reputation: 70460

 SELECT a.COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS a
 JOIN INFORMATION_SCHEMA.COLUMNS b
 ON a.COLUMN_NAME = b.COLUMN_NAME
 AND b.TABLE_NAME = 'table2'
 AND b.TABLE_SCHEMA = database() //or manually enter it
 WHERE a.TABLE_NAME = 'table1'
 AND a.TABLE_SCHEMA = database(); //or manually enter it

Upvotes: 15

Related Questions