I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use:

SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2013 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2014 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2015 

I am quite certain there is a less redundant and verbose way to write this query. The schema for each of the table is exactly the same. This is in MySQL 5.6

  • Unless its select * from table, I don't think there is a better way. – Anthony Genovese Jun 23 '16 at 15:16
  • 5
    One improvement you can probably make is using UNION ALL (I suppose there are no duplicate rows in these tables). It's probably going to improve performance a lot. – ypercubeᵀᴹ Jun 23 '16 at 16:58
  • @agenovese you mean SELECT * FROM visitor_archive_2012 UNION SELECT * FROM visitor_archive_2013 UNION SELECT * FROM visitor_archive_2014 ? – hanxue Jun 24 '16 at 3:47
  • NB difference between UNION and UNION ALL and the likely performance implications. – Michael Green Jul 3 '16 at 16:45

Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job.

But, that may not even be wise. What queries benefit from your splitting it into multiple tables?

  • No good reason to split into multiple tables; it is a legacy application with the database designed by an inexperienced programmer – hanxue Aug 17 '17 at 16:22

As @Rick says, I would question first why you need to have separate tables but assuming this is out of your control, if this is a regular query, may be simplest to create a view. Possibly if these are MyISAM tables you could consider a Merge table as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.