MySQL SELECT * FROM table1,table2,table3

Question

I have several tables with same structure. All I want is get all results from all tables from a database.

For example:

Table1:

id |  Name  

-----------

1  |  John

2  |  Alex

3  |  Patrick

4  |  Donald

5  |  Jane

Table2:

id |  Name  

-----------

1  |  Ben

2  |  Dale

3  |  Kane

4  |  Tom

5  |  George

My desired result with SELECT Name FROM ... is:

 Name  

-------

John

Alex

Patrick

Donald

Jane

Ben

Dale

Kane

Tom

George

Is there any way to do this easily without using UNION like: SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM table3....

Because this makes my query so long and it is pain for me.Probably I'll have more than 20-30 tables in a database. I'm looking for somethings like SELECT * FROM table1,table2.. etc.

Thanks.

UPDATE

I did it like this (in PHP), I hope it also helps for others:

$dbc1 = $db->query("SELECT group_concat(table_name) AS tables FROM information_schema.tables WHERE table_schema='dbname'");

This query return all table names as 1 string with comma seperated like this : table1,table2,table3....
Then I explode that string and I used in foreach loop.

$dbc1_ftchd = $dbc1->fetch(PDO::FETCH_ASSOC);    

foreach(explode(',',$dbc1_ftchd['tables']) as $next_tb_name){  

        echo $next_tb_name.'<br>';

    }

Now I able to use all tables as I want one by one instead of use all of them at the same time. But no problem it is return same result for me.

Thanks to everyone who helped me :)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

This should work:

SELECT `Name` FROM `Table1` 
UNION
SELECT `Name` FROM `Table2` 
UNION
SELECT `Name` FROM `Table3`

EDIT: Sorry, I didn't saw the last paragraph of your question. You can create a view or temporary table if you don't like UNIONs that much.

Create View: https://dev.mysql.com/doc/refman/5.7/en/create-view.html

Create Temporary Table: CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table AS (SELECT * FROM table1 UNION ...)

MySQL SELECT * FROM table1,table2,table3

Sign up