Recipe 12.14. Performing a Join Between Tables in Different Databases

Problem

You want to use tables in a join, but they're not located in the same database.

Solution

Use database name qualifiers to tell MySQL where to find the tables.

Discussion

Sometimes it's necessary to perform a join on two tables that are located in different databases. To do this, qualify table and column names sufficiently so that MySQL knows what you're referring to. Thus far, we have used the artist and painting tables with the implicit understanding that both are in the cookbook database, which means that we can simply refer to the tables without specifying any database name when cookbook is the default database. For example, the following statement uses the two tables to associate artists with their paintings:

SELECT artist.name, painting.title   FROM artist INNER JOIN painting   ON artist.a_id = painting.a_id; 

But suppose instead that artist is in the db1 database and painting is in the db2 database. To indicate this, qualify each table name with a prefix that specifies which database it's in. The fully qualified form of the join looks like this:

SELECT db1.artist.name, db2.painting.title   FROM db1.artist INNER JOIN db2.painting   ON db1.artist.a_id = db2.painting.a_id; 

If there is no default database, or it is neither db1 nor db2, it's necessary to use this fully qualified form. If the default database is either db1 or db2, you can dispense with the corresponding qualifiers. For example, if the default database is db1, you can omit the db1 qualifiers:

SELECT artist.name, db2.painting.title   FROM artist INNER JOIN db2.painting   ON artist.a_id = db2.painting.a_id; 

Conversely, if the default database is db2, no db2 qualifiers are necessary:

SELECT db1.artist.name, painting.title   FROM db1.artist INNER JOIN painting   ON db1.artist.a_id = painting.a_id;