How to link tables that are in different databases?

3

I would like to know how I can link two tables that are in different databases in order to find data that is similar in both tables.

For example, we can have the Purchasing to-do list table and Purchased shopping list table table, to end auto create a table of items in the same table leaving aside items that have not been purchased.

    
asked by anonymous 09.01.2018 / 08:35

1 answer

5

Joining tables with UNION and JOIN :

You use UNION in the "Vertical", to match the query on separate lines:

  

Search in Multiple Tables

  

What is the difference between UNION and UNION ALL?

  

Look in different tables knowing what the result came from

You use JOIN in "Horizontal" to list two tables on the same line:

  

Select data from two tables

  

What's the difference between INNER JOIN and OUTER JOIN?


Applying to different databases:

As you said that they are different databases , you should prefix the name of the tables with their respective bases (applicable to UNION and JOIN ):

SELECT tabelaA.campo1, tabelaB.campo2 FROM baseA.tabelaB JOIN baseC.tabelaD ON <condicao>
                                           -----              -----


What if the tables have the same name in the different DBs?

Then you use an alias on each:

SELECT aliasX.campo1, aliasY.campo2
FROM baseA.tabela AS aliasX
JOIN baseC.tabela AS aliasY ON <condicao>


What if the databases are on separate servers?

One of the solutions would be to solve the problem in the client language (PHP in the case) by simply running two queries and showing the results next (or filtering and showing with array in> for example).

Depending on the application, you can use FEDERATED TABLES , but you need to be careful about application efficiency:

create table t1 (
a int,
b varchar(32))
ENGINE=FEDERATED CONNECTION='mysql://[email protected]/test/t1'
                 -- aqui vão os dados do outro servidor --

More details here:

  

How do I use MySQL Federated?

  

The FEDERATED storage engine

  

Wikipedia - MySQL Federated

    
09.01.2018 / 09:48