Doing a Natural Join on Two Tables in two Different Databases

Question

I have two tables: p.Test and q.Test on two different Databases - p and q.

Both these tables have different MYSQL connections and are on two different servers.

I need to do a Natural Join as is given here : http://www.microshell.com/database/sql/comparing-data-from-2-database-tables/2/

How do I possibly do that? I am using java PreparedStatement as well to actually run the query. Is there a way to possibly do this?

Say I create two Connections to each of them:

Connection conn1 = DriverManager.getConnection(URLtoPDB, Username,Password);
Connection conn1 = DriverManager.getConnection(URLtoQDB, Username,Password);

How do I fire a preparedStatement to even do say: select * from p.Test,q.Test

*Why* do you need a Natural Join on Two Tables in two Different Databases? — Your Common Sense, Jul 03 '13 at 09:28
I have this replicated table in my database from another one that I only have read access to. I need to check for any difference in row-data between them. I thought this was a nice way to do it. — gran_profaci, Jul 03 '13 at 09:34

Mahesh · Accepted Answer · 2013-07-03T09:30:48.673

0

You can specify the database name before the table name in query as below

SELECT
    *
FROM
    [yourdatabase1].[jajal].[j]
    NATURAL LEFT JOIN [yourdatabase2].[jajal_copy] jc
WHERE
     jc.user_id IS NULL
UNION ALL
SELECT
   *
FROM
    [yourdatabase1].[jajal].[j]
NATURAL RIGHT JOIN [yourdatabase2].[jajal_copy] jc
WHERE
    j.user_id IS NULL

you can user servernames for different servers as

 select
    *
 from
    LocalTable,
      [OtherServerName].[OtherDB].[dbo].[OtherTable]

edited Jul 03 '13 at 09:30

answered Jul 03 '13 at 09:24

Mahesh

8,694
2
32
53

Will this work even if DB1 and DB2 are on different servers and require different connections? – gran_profaci Jul 03 '13 at 09:25
1

For cross server you can specify the server names in query – Mahesh Jul 03 '13 at 09:31
Hmmm.... including the UserName and PassWord? OR will I have to be connected beforehand? – gran_profaci Jul 03 '13 at 09:32

Loša · Answer 2 · 2013-07-03T09:38:20.363

0

From comments in other answers, it looks like you need to compare two tables from different database servers. In that case, you have to use federated storage engine. This is a change that has to be done on the database server. You can't do that in your Java code.

If you have to do it in Java and you cannot modify anything in the database, you have to download all the data to memory and compare them manually.

edited Jul 03 '13 at 09:38

answered Jul 03 '13 at 09:27

Loša

2,621
2
14
19

Any efficient way to do this? How about getting all the data in two Hashmaps and comparing them? http://stackoverflow.com/questions/12721735/how-to-receive-difference-of-maps-in-java – gran_profaci Jul 03 '13 at 09:30
1

Wouldn't it be better to setup [database replication](http://dev.mysql.com/doc/refman/5.0/en/replication.html) to keep your tables in sync, so you don't have to care about it manually? – Loša Jul 03 '13 at 09:42

score 0 · Answer 3 · answered Jul 03 '13 at 09:38

0

Do your replica again, into another table of current database and then compare.

answered Jul 03 '13 at 09:38

Your Common Sense

156,878
40
214
345

score -1 · Answer 4 · answered Jul 03 '13 at 09:22

-1

If you are using mysql then database is equivalent to schema. so it is straight forward

select * 
from p.Test natural join q.Test;

answered Jul 03 '13 at 09:22

chetan

2,876
1
14
15

Can they be in totally different Databases? – gran_profaci Jul 03 '13 at 09:23
yes if they are in same mysql instance. and you don't even need two connection – chetan Jul 03 '13 at 09:23
So, If I use a different connection to each of them... It won't work? – gran_profaci Jul 03 '13 at 09:24

Doing a Natural Join on Two Tables in two Different Databases

4 Answers4