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 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, ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.