Merge different database queries

Question

"It is possible to join information from tables of other databases?"

This doubt arose when I needed to do a survey make a comparison of data in tables of different stores, Every store has its own comic book, where you needed to compare the average of products sold, what comes out most in each store, how many customers authenticate quickly without having to run a code in multiple places and then merge the data separately.

Based on this problem, the question is:

How can I collect and compare data from other databases where tables have the same structure?

Answer 1

It is certainly possible to do a search in different databases at the same time. to access different databases just pass inside select the database, table and fields you want to take.

SELECT banco_01.vendas.* FROM banco_01.vendas;

But as they are different tables, only the select would not be enough to list the information correctly.

One way to solve this is to create a procedure where to create a view or a temporary table with the information. Or you can use the command union, the Union command joins N selects and returns a record with all the information.

To select the quantity of purchases made in each store, simply use the command union thus:

SELECT
    count(loja_01.vendas.id) as total_vendas
FROM
    loja_01.vendas
UNION
SELECT
    count(loja_02.vendas.id) as total_vendas
FROM loja_02.vendas;