Joining tables with
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
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
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 (
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