What the hell??!? (blah, blah of a wannabe alien)
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.
| Print article | This entry was posted by Shawn on December 13, 2005 at 9:38 pm, and is filed under Coding. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
about 6 years ago
its amazing what we get done without knowing the “right” or “best” ways to do it eh?
about 5 years ago
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.
about 5 years ago
Dont worry, until now, I didn’t know it either
)
about 4 years ago
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
about 4 years ago
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)?
about 4 years ago
unfortunately this does not work on a server where you only get one dbs per username/password even though we can have unlimited dbs
about 4 years ago
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?
about 4 years ago
Tis just saved my day, thanks!
about 4 years ago
“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.
about 3 years ago
Nice.. I didn’t know about that. Thanks for the help
about 3 years ago
Waw..thank you, i need this way to join some table from other database. finally, i found this article, thank you very much..
about 3 years ago
Forehead-Slap!
about 3 years ago
This is awesome! Huge time saver. I feel the same way as you — kinda silly for never realizing that this was possible.
about 3 years ago
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.
about 2 years ago
>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/
about 2 years ago
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
about 2 years ago
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!
about 2 years ago
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 ?
about 2 years ago
Thanks! it helps me in my project.
about 1 year ago
Your examples are amazingly clear! Saved me a lot of headaches
about 1 year ago
Great tip, thanks for posting it. Saved me a big mess of copying tables and multiple database connections
about 1 year ago
Awesome, awesome tip. This just made my job easier/more fun – thanks!
about 8 months ago
AWESOME! I didn’t know this was possible haha. Thanks for documenting