« Brass Knuckle Implants
MySQL Memory Fragmentation »

Tuesday, December 13th, 2005Tuesday, December 13th, 2005

Cross-Database JOIN With MySQL

by Shawn @ 9:38 pm · filed under Coding

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:

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)

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.

20 Responses to “Cross-Database JOIN With MySQL”

  1. Shoemoney Says:
    December 14th, 2005 at 2:18 pm

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

  2. MarkR Says:
    December 7th, 2006 at 7:32 am

    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.

  3. klpof Says:
    January 23rd, 2007 at 4:43 am

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

  4. hasan Says:
    February 20th, 2007 at 2:52 am

    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

  5. Chris Dean Says:
    August 3rd, 2007 at 8:44 am

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

  6. j Says:
    August 7th, 2007 at 8:03 pm

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

  7. Mohammad Says:
    October 7th, 2007 at 2:42 am

    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?

  8. Rocky Says:
    October 12th, 2007 at 12:36 pm

    Tis just saved my day, thanks!

  9. Graham Mattingley Says:
    December 16th, 2007 at 1:53 pm

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

  10. Lucas Says:
    May 18th, 2008 at 5:15 pm

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

  11. Pipiew Says:
    July 31st, 2008 at 12:23 am

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

  12. Bootzero Says:
    August 12th, 2008 at 10:41 am

    Forehead-Slap!

  13. Robert Says:
    November 6th, 2008 at 9:59 am

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

  14. evan Says:
    November 12th, 2008 at 2:46 pm

    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. :)

  15. Andrei Says:
    April 7th, 2009 at 9:00 am

    >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/

  16. Andrei Says:
    April 7th, 2009 at 9:11 am

    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. Is it a good idea to design one database(the parameter inmysql_select_db()) with many many tables? | keyongtech Says:
    May 2nd, 2009 at 3:06 am

    […] Re: Is it a good idea to design one database(the parameter in mysql_select_db())with many many tables? The Natural Philosopher schreef: > Jerry Stuckle wrote: >> The Natural Philosopher wrote: >>> Erwin Moller wrote: >>>> Phper schreef: >>>>> I’m developing a website with six products. The DBMS is MySQL. I want >>>>> to design seven databases on one DBMS,namely, MySQL, one for each, and >>>>> the 7th database is a common database shared among the six >>>>> products.There is a problem, I often need cross- >>>>> database operations. I want to create some stored procedures/routines >>>>> and some of them need cross-database operations and I don’t know how >>>>> to do it.It is MySQL. Is it a good idea to design only one database >>>>> (the parameter in mysql_select_db()) with many many tables? What are >>>>> the advantages and disadvantages? >>>> >>>> If the products in the database are related, why not stuff them all >>>> into one and the same database? >>>> However, if there are six different costumers, you better use 6 >>>> databases. >>>> >>>> >>>>> >>>>> For example, on the website of yahoo.com, there are so many web >>>>> products, Yahoo! answers is one of them. On the website of google.com, >>>>> there are so many products,say,Google Web search, Google shopping, >>>>> Google maps, gmail, etc. There is one website(one domain) with many >>>>> subdomains,say,google.com is a domain, and images.google.com is a >>>>> subdomain. One owner will own the entire website. >>>> >>>> You can easily connect from the same PHP script to many different >>>> databases, even different brands. >>> >>> Strictly, you don’t make a new connection: Just use mysql_select_db >>> >>> However cross database joins are not possible. >> >> Incorrect. >> > Example in Mysql.? Go to google: type: cross database joins mysql follow first link. ;-) http://www.shawnhogan.com/2005/12/cr…ith-mysql.html Regards, Erwin Moller — "There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult." — C.A.R. Hoare […]

  18. Randy Says:
    May 11th, 2009 at 6:10 pm

    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!

  19. Sergio Says:
    September 22nd, 2009 at 8:54 am

    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 ?

  20. Imran Farid Says:
    January 18th, 2010 at 1:00 am

    Thanks! it helps me in my project.

Leave a Reply