145

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?

user3262424
  • 7,223
  • 16
  • 54
  • 84

4 Answers4

183

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
  • 44,205
  • 11
  • 83
  • 107
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 11
    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
  • 44,205
  • 11
  • 83
  • 107
Senthil
  • 5,514
  • 2
  • 22
  • 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,250
  • 15
  • 29
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
  • 311
  • 2
  • 6