MySQL Cookbook, 2nd Edition

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 ...