Cross table join using MYSQL rather than MSSQL

Question

Is it possible to do a cross table join in mysql spaning different tables? in different databases.

This seem to be easily possible in MSSQL, and greatly speeds up data transfer?

How about mysql, do you need to use a powerful IDE to achieve this? or do you have to write a program to do something like this?

UPDATE tblUser SET tblUser.ReceiveInfo=old_database.dbo.contact.third_party_info_yn FROM tblUser inner join old_database.dbo.contact ON old_database.dbo.contact.id=tblUser.oldid

Answer 1

Sure, very easy. Prefix the table name with the database name, like you show. We do cross-database joins on a regular basis. For example:

SELECT COUNT(*) FROM newusers1.users JOIN newusers2.users

It certainly won't speed up data transfer compared to having both tables in the same database, though.

Now, if your tables are sitting on different database servers, the answer is no. An example of this is if your database is too big and you need to shard your tables. Things get more than a little messy. But given that you seem to be happy with the MS SQL solution, that does not seem to apply here.

Answer 2

In MySQL you can do cross DB joins and, by way of the FEDERATED engine, even cross server joins.

Answer 3

MySQL doesn't actually care if the tables are in the same "database", it's just a logical collection of tables for convenient administration, permissions etc.

So you can do a join between them just as easily as if they were in the same one (see ChrisInEdmonton's answer)