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

hanxue
  • 245
  • 1
  • 4
  • 10

2 Answers2

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?

Rick James
  • 80,479
  • 5
  • 52
  • 119
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.

Ian Roberts
  • 36
  • 1