I am in a situation right now where I have to create a MySQL view from different databases on the same host. The team that created the data used different databases for every year (like sales_2014, sales_2015) etc.

All databases have the same tables with almost the same schema. Only some columns are different, but i only need those that are the same so I don't have to care about those that are different.

For my project I need to have that data in one view or table. What is the best way to do this? Should I create a view for every database and connect all views via a single view on a different database, should I just use one big view for all tables from the different databases or should i use something different than views?

1 Answer 1


Simply reference whatever databases you want. Note the "db.tbl" syntax:

    SELECT ...
        FROM sales_2014.blah AS x
        JOIN sales_2015.blah AS y ON ...

Similarly, you could use UNION ALL.

    AS all_sales
        SELECT * FROM 2014.sales
        SELECT * FROM 2015.sales;

For 'sanity', you might want to create a new database to store the VIEWs in. You might call it sales_all.

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.