Cross-Database JOIN With MySQL

You ever wish you could have done something for years, only to realize one day you could have done it this whole time?

I have a couple tables that I was replicating across 3 or 4 MySQL databases because I didn’t think I could reference a table from database A while working with database B.

Today I accidentally tried to do a SELECT on a table from a database I wasn’t even working with and instead of getting an error, it worked. As long as the user you are connected to MySQL as has the proper privileges (duh), it works. You just prefix your table/column name with the database name.

For example:
[code=sql]SELECT database1.tablename.a_column,database2.another_table.another_column
FROM database1.tablename
LEFT JOIN database2.another_table ON(database1.tablename.some_id = database2.another_table.some_id)[/code]

You can do anything you can do with tables in the same database (INSERT SELECT, JOINS, sub-queries, etc.)

Wow, I’m an idiot… its probably been a feature since version 1.0 too. 🙂 Now if you could just do the same thing across different MySQL servers (something like ipaddress.database.table.field), that would be really handy.

25 thoughts on “Cross-Database JOIN With MySQL”

its amazing what we get done without knowing the “right” or “best” ways to do it eh?

I can’t blame you for not knowing that cross-db joins are possible in MySQL. Today I was wondering whether it was possible and looked at the MySQL manual, as well as did a Google search. This blog entry is the only place I’ve seen so far which explicitly states that it’s possible.
Thanks for documenting this.

Dont worry, until now, I didn’t know it either :))

I was searching for the equivalent of schema in postgres and came to this page. If i don’t find the equivalent then i would certainly use what u have told coz its logical and simple. thanks

Hi Guys

Realised this was possible before reading this blog post BUT does anyone have any ideas on to do cross database queries when the login credentials for db B are different to those of db A and you want to keep them seperate (i.e. no adding in of a ‘super-user’ that can login to both as well as keeping A and B’s account)?

unfortunately this does not work on a server where you only get one dbs per username/password even though we can have unlimited dbs

I have tried to use REFERENCES CLAUSE for a column to reference to a table in another database but I got a syntax error. ( REFERENCES dbname.tablename.column)
It seems that it is not possible to have a foreign key constraint while working cross database. Any ideas about this problem?

Tis just saved my day, thanks!

“You ever wish you could have done something for years, only to realize one day you could have done it this whole time?”

I firsted using mysql in october 2001, and today I also discover that cross database joins are possible.

Nice.. I didn’t know about that. Thanks for the help

Waw..thank you, i need this way to join some table from other database. finally, i found this article, thank you very much.. 🙂

Forehead-Slap!

This is awesome! Huge time saver. I feel the same way as you — kinda silly for never realizing that this was possible.

Yup – got here via google, too. I was wondering if this was possible. This is going to save my company (me!) so much time and work. 🙂

>Now if you could just do the same thing across different MySQL servers (something like ipaddress.database.table.field), that would be really handy.

Oracle can do something like this by using database links.
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm
http://decipherinfosys.wordpress.com/2007/03/03/database-links-in-oracle/

It seems like by using FEDERATED Storage Engine is possible to do cross server queries in mysl.

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Yep, the syntax is very similar to SQL Server:

DB1..Table

– or –

DB1.dbo.Table

By the way, have you tried your example with an alias for the db.table combo in MySQL? For instance…

SELECT a.a_column, b.another_column
FROM database1.tablename a
LEFT JOIN database2.another_table b ON(a.some_id = b.some_id)

In the systems I work in now, I reference the database name explicitly in almost every query I write, even when I’m not joining across databases! I’m even starting to consider it good form, since it makes it absolutely clear in the query string which database you’re querying, and it improves scalability – if you ever want to update the query string by adding in a table from another database, most of the work is already done!

But how to can i do create a reference cross between 2 or more servers with MYSQL … i try the sentence ip.db.table , and the result was FAIL, can us help me ?