Compare two tables and find the corresponding columns

Question

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

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

Is it possible?

I tried using INFORMATION_SCHEMA.COLUMNS, but can't get it right.

Answer 1

 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

Answer 2

If someone needs the opposite:
Find all the columns that exist in one table but are missing in another:

 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'
 )

Answer 3

, , MySQL SHOW COLUMNS , PHP array_intersect(), .