mysql select from multiple tables but no join

Question

I have several separate tables defined in my (in construction) database that shares the same fields. But also in database, I have other tables that aren't related to data, so to differentiate, I mark some of them with the same comment 'data_table'.

It is useful in procedures:

select table_name from information_schema.tables where table_schema = 'myDB' and table_comment = 'data_table'

I need to do a select over all tables marked with this comment, to obtain something like, but not using JOIN because every time I have to add more table names in that select.

ID Value

CAP-001 0.1uF <-- from capacitor table

CAP-002 1nF

RES-001 1k <-- from resistor table

RES-002 100k

                       <-- from other table in the future

It is possible? Thank you very much for suggestions.

Answer 1

I don't understand why this would be a good idea, but I think you can solve your problem by doing multiple queries using a UNION statement. Read the syntax in the docs.

Answer 2

You can use UNION statements.

UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+


(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);