Search multiple tables for the same value

Question

I have a MYSQL database with a few tables, all of which have the same structure. I want to search all the tables to find a row with a specific value for a column. Do I have to search the tables one by one or there is an easier way?

Answer 1

You can union all tables. You still need traverse all tables one by one, but in case of union you will not have cartesian multiplication, hence best from all:

SELECT column FROM table1 WHERE column = 'value'
UNION ALL
SELECT column FROM table2 WHERE column = 'value'
;

Answer 2

Easily done and ALSO TESTED IN MYSQL WORKBENCH.

SELECT ALL:

SELECT * FROM table_one, table_two;

SELECT ONE VALUE FROM TWO TABLES:

SELECT * FROM table_one, table_two WHERE field = 'some_val'

SELECT MULTIPLE VALUES FROM TWO TABLES:

SELECT * FROM table_one, table_two WHERE field = 'some_val' AND field2 = 'some_val' AND field3 = 'some_val'