Select SQL from multiple tables with identical structure

Question

SELECT  * 
FROM table1, 
     table2, 
     table3 
ORDER BY id ASC 
      LIMIT

how to do this right? I'm using MySQL and all the tables have identical structure.

Answer 1

You can do it like this

SELECT * FROM (
                SELECT  * FROM table1
                UNION ALL
                SELECT  * FROM table2
                UNION ALL
                SELECT  * FROM table3   
) as l
ORDER BY id ASC LIMIT 50

UNION will only work when you have same number of columns in each table

Answer 2

1 - While assuming from statement identical structure that all three tables have a column id which us used as a primary-foreign key in these tables. And you need record for a particular id from all these three.

SELECT  * 
FROM table1 t1, 
     table2 t2, 
     table3 t3
WHERE t1.id=t2.id AND t2.id=t3.id 
-- ORDER BY id DESC
LIMIT 10;

This will give you the top 10 records from all three tables related a particular id.

2 - Assuming you just to combine results from three different queries into one statement and prioritize them by id

SELECT  * FROM table1
UNION
SELECT  * FROM table2
UNION
SELECT  * FROM table3
--ORDER BY id DESC
LIMIT 10;

• In case you need record from the last un-comment the statement -- ORDER BY id DESC assuming you have an incrementing id column
• If you need more record than just change 10 in LIMIT to your required number.
• Please note: The UNION operator selects only distinct values by default. To allow duplicate values, use the UNION ALL.

Answer 3

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

(SELECT u.name FROM user as u where u.active=1) UNION (SELECT pu.name FROM productuser as pu WHERE pu.active=1);