142

In MySQL, I have two different databases -- let's call them A and B.

Is it possible to perform a join between a table that is in database A, to a table that is in database B?

Tim Cooper
  • 151,519
  • 37
  • 317
  • 271
user3262424
  • 6,959
  • 12
  • 51
  • 81

4 Answers4

178

Yes, assuming the account has appropriate permissions you can use:

SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

You just need to prefix the table reference with the name of the database it resides in.

potashin
  • 43,297
  • 11
  • 81
  • 105
OMG Ponies
  • 314,254
  • 77
  • 507
  • 490
  • 10
    What about two databases from different servers? (for example, one db on a cloud service server, and on db on your own server) – Yuval A. May 10 '15 at 10:27
  • 1
    Is it possible to join to different DB's, DB1 = mysql & DB2 = PostgreSQL) . Both have few common tables. – MAX Oct 27 '16 at 12:35
  • 1
    @YuvalA.@boatcoder i don't think yuval is asking about performance. simply asking about how to do a cross-server join. would be quite difficult since you need to ask the client to make two connections. – Jayen Apr 16 '17 at 07:02
  • 1
    make sure that the database name is NOT inside the same backticks as the table name otherwise you will get `ERROR 1146 (42S02): Table 'currentdb.otherdb.tablename' doesn't exist` – Jeff May 12 '17 at 17:14
  • Thanks. it also worked with me without alias names `FROM A.table1 JOIN B.table2 ON B.table2 .column2 = A.table1.column1` – Accountant م Aug 20 '17 at 00:04
4
SELECT <...> 
FROM A.tableA JOIN B.tableB
potashin
  • 43,297
  • 11
  • 81
  • 105
Senthil
  • 5,120
  • 1
  • 21
  • 11
2
SELECT *
FROM A.tableA JOIN B.tableB

or

SELECT *
  FROM A.tableA JOIN B.tableB
  ON A.tableA.id = B.tableB.a_id;
Kalaivani M
  • 1,200
  • 14
  • 28
1
SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

Just make sure that in the SELECT line you specify which table columns you are using, either by full reference, or by alias. Any of the following will work:

SELECT *
SELECT t1.*,t2.column2
SELECT A.table1.column1, t2.*
etc.
Noel Swanson
  • 191
  • 1
  • 4