结合简单，近乎相同的选择语句

Question

One of the parameters my PHP page accepts is a user-search query. The application then takes that query and performs >100 separate SELECT statements that look exactly like the following:

SELECT * FROM table1 WHERE user = 'query';
SELECT * FROM table2 WHERE user = 'query';
SELECT * FROM table3 WHERE user = 'query';
SELECT * FROM table4 WHERE user = 'query';
...etc...

Is there a faster/more efficient way to do what I'm trying to do? E.g. by combining all of the select statements into one statement somehow? I feel like having 100 individual queries going to my database would be slower than one large query. I do not know how to combine them though, please help me.

Answer 1

You can use the union all operator to combine these queries:

SELECT * FROM table1 WHERE user = 'query'
UNION ALL
SELECT * FROM table2 WHERE user = 'query'
UNION ALL
SELECT * FROM table3 WHERE user = 'query'
-- Etc...

That being said, you may want to reconsider your database design. If you always query these tables together, perhaps all the data should be added to one table.