How to connect tables that are in different databases?

Question

I wonder how I can connect two tables that are in different databases to find data that are similar in the two tables.

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

Answer 1

Joining tables with UNION and JOIN:

You use UNION in "Vertical", to relate according to the question, in separate lines:

Search in multiple tables

What’s the difference between UNION and UNION ALL?

Search in different tables knowing what the result came from

You use JOIN in "Horizontal", to relate two tables in the same row:

Select data from two tables

What is the difference between INNER JOIN and OUTER JOIN?

Applying to different databases:

As you said they are different databases, you must prefix the name of the tables with their respective bases (applicable both to UNION how much JOIN):

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

And if by chance the tables have the same name in the different Dbs?

Then you use a alias in each:

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

And 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), simply by running two darlings and showing the results next (or filtering and showing with array for example).

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

create table t1 (
a int,
b varchar(32))
ENGINE=FEDERATED CONNECTION='mysql://user@hostname/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