Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata