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”

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

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

  3. 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)?

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

  5. 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?

  6. “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.

  7. Waw..thank you, i need this way to join some table from other database. finally, i found this article, thank you very much.. ๐Ÿ™‚

  8. 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. ๐Ÿ™‚

  9. 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!

  10. 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 ?

  11. Uh, I totally did not know you could do that. I’ve also being doing MySQL for about 7 years and had no idea… I feel stupid.

Leave a Reply

Your email address will not be published. Required fields are marked *