Performing a Join Between Tables in Different Databases

12.3.1 Problem

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

12.3.2 Solution

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

12.3.3 Discussion

Sometimes it's necessary to perform a join on two tables that live in different databases. To do this, qualify table and column names sufficiently so that MySQL knows what you're referring to. We've been using the shirt and tie tables under 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. For example, the following query retrieves the combinations of items from the two tables:

mysql> SELECT shirt.item, tie.item FROM shirt, tie;
+-----------+--------------+
| item | item |
+-----------+--------------+
| Pinstripe | Fleur de lis |
| Tie-Dye | Fleur de lis |
| Black | Fleur de lis |
| Pinstripe | Paisley |
| Tie-Dye | Paisley |
| Black | Paisley |
| Pinstripe | Polka Dot |
| Tie-Dye | Polka Dot |
| Black | Polka Dot |
+-----------+--------------+

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

SELECT db1.shirt.item, db2.tie.item FROM db1.shirt, db2.tie;

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

SELECT shirt.item, db2.tie.item FROM shirt, db2.tie;

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

SELECT db1.shirt.item, tie.item FROM db1.shirt, tie;