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.
UNION and JOIN : You use UNION in the "Vertical", to match the query on separate lines:
You use JOIN in "Horizontal" to list two tables on the same line:
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://user@hostname/test/t1'
-- aqui vão os dados do outro servidor --
More details here: