2

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

4
  • Unless its select * from table, I don't think there is a better way. Jun 23, 2016 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. Jun 23, 2016 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, 2016 at 3:47
  • NB difference between UNION and UNION ALL and the likely performance implications. Jul 3, 2016 at 16:45

2 Answers 2

1

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?

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

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 and acknowledge you have read our privacy policy.

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