CODEWITHSUNDEEP
mysqlselectjoinsubquery
Michele
Michele

Reputation: 1488

how to merge multiple select in mysql

i have severals tables, named like these:

table_2012_12
table_2012_10
table_2012_07

both with the same structure:

id
order_id
account_id

i know i have to perform these queries:

SELECT * FROM table_2012_12 where id=1
SELECT * FROM table_2012_12 where id=5  
SELECT * FROM table_2012_10 where id=1
SELECT * FROM table_2012_07 where id=22

What's the best (in performances) method to use a single query?

Upvotes: 0

Views: 102

Answers (3)

Denys Séguret
Denys Séguret

Reputation: 382122

Given this structure, you can't do any optimization but you may make a union query :

SELECT * FROM table_2012_12 where id=1
UNION SELECT * FROM table_2012_12 where id=5  
UNION SELECT * FROM table_2012_10 where id=1
UNION SELECT * FROM table_2012_07 where id=22

UNION will automatically remove duplicates.

Upvotes: 1

DevT
DevT

Reputation: 4933

SELECT * FROM table_2012_12 where id=1 OR  id=5
UNION ALL SELECT * FROM table_2012_10 where id=1
UNION ALL SELECT * FROM table_2012_07 where id=22

Upvotes: 1

Roland Bouman
Roland Bouman

Reputation: 31961

The UNION operator does that. See: http://dev.mysql.com/doc/refman/5.6/en/union.html

SELECT * FROM table_2012_12 where id=1
UNION ALL
SELECT * FROM table_2012_12 where id=5  
UNION ALL
SELECT * FROM table_2012_10 where id=1
UNION ALL
SELECT * FROM table_2012_07 where id=22

Usually, you should not just write UNION but UNION ALL. UNION has the effect of joining the query results, as well as filtering out any duplicate rows. UNION ALL will simply merge the query results, and keep all rows. This is much more efficient

Upvotes: 1

Related Questions