Performing a Join Between Tables in Different Databases
You want to use tables in a join, but they’re not located in the same database.
Use database name qualifiers to tell MySQL where to find the tables.
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
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
db2, it’s necessary to use this fully
qualified form. If the default database is either
db2, you can dispense with the corresponding
qualifiers. For example, if the default database is
db1, you can ...