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.
-
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. -
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)?
-
#7 written by Mohammad 5 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/ -
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 -
#17 written by Randy 4 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!
-
-
-
- Comment Feed for this Post
its amazing what we get done without knowing the “right” or “best” ways to do it eh?