MySQL - Why query with 'IN' fails?

Question

These queries return correct results:

SHOW FULL COLUMNS FROM `users`
SHOW FULL COLUMNS FROM `teachers`

But this one generates an error

SHOW FULL COLUMNS IN  ('users', 'teachers')

I have tried single quote, double quote, back-quote and no quote and all fail. What am I doing wrong?

The error is this:

#1064 - 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 '('users', 'teachers')' at line 1

Answer 1

According to the MySQL docs (section 12.4.5.5), you can use the keyword IN instead of FROM, but it doesn't have the same meaning as in a WHERE clause; it's just a synonym for FROM in this context.

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern']

{FROM | IN} signifies that the two are interchangable. The fact it doesn't say something like {FROM tbl_name | IN (tbl_name1, tbl_name2, ...)} means that there is no difference between the two.

I'm not sure that showing more than one table's columns is doable in one simple query. Maybe subqueries would do the trick? (Not an SQL wizard, but there are some around here.)

Answer 2

Maybe this gives you what you're after?

SELECT *
  FROM information_schema.columns
 WHERE table_name IN ('users', 'teachers')