How do I get column names of two tables at a time

Question

How do I get the column name of two tables in a single query ?

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'table_name';

This works for single table. But if I try

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'table1'
AND
SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'table2';

This throws error.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'table2' ' at line 5
Error Code: ER_PARSE_ERROR

score 0 · Answer 1 · answered Nov 28 '22 at 05:39

if you want to select 2 columns from different tables, you need to add some relation between them.

On the basis of that relationship, you can fetch data from two different tables.

Please check the link: https://www.w3schools.com/mysql/mysql_join.asp

Hope it will solve your problem.

score 0 · Accepted Answer · answered Nov 28 '22 at 05:50

Different queries will be put together using UNION ALL (or UNION only if it's necessary to exclude identic values which appear in both queries):

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'table1'
UNION ALL -- here use "UNION ALL" instead of "AND"
SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'table2';

Since you want to get data from the same table, you don't need two queries at all.

Just use an IN clause...

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name IN ('table1','table2');

...or use OR:

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'table1' 
OR table_name = 'table2';

How do I get column names of two tables at a time

2 Answers2