How to execute same queries with different column names against different databases to get single result/data set

Question

As you can see, I have multiple databases(db10, db12) and each database contains individual customer data.

I want to query time series data for the currency stored in currency table.

The query is

Select Rupee, Yen from db10.currency where dateTime between value1 and value2;

Result

It is working fine against one database

but I want to process multiple queries against different dbs.

eg.

Select Rupee, Yen from db10.currency where dateTime between value1 and value2;
Select Dollar, Pound from db12.currency where dateTime between value1 and value2;

and so on.

Keep note that I generate these queries dynamically. All different information eg customerId, value1, value2, will be passed as a payload from Front to Backend. I consume the payload and make these queries dynamically.

I initial though it would work using UNION ALL as below

Select Rupee, Yen from db10.currency where dateTime between value1 and value2 
UNION ALL 
Select Dollar, Pound from db12.currency where dateTime between value1 and value2;

but lately I found it requires same column name, in same order to work. Unfortunately I have different columns eg. Rupee, Yen, Dollar, Pound so it doesn't work.

I want to know best and performant approach to deal with this type of query.

Expected Result something like below (or better if you can suggest something else)

Answer 1

This can be done if the databases are on the same server and you have a column which will be used on the join condition, on your case I see dateTime.

Try:

SELECT db10.Rupee,
       db10.Yen,
       db20.Dollar,
       db20.Pound
FROM db10.currency
INNER JOIN db20.currency ON db10.currency.dateTime = db20.currency.dateTime 
WHERE dateTime between value1 and value2;

1. Where condition is missing. I want to filter dataset based on dates.

Edited and added on the query.

2. I'm not sure if inner join is right clause. Assume for given date range, if data is not present in db12.currency but present in db10.currecy, it returns empty data set. The expectation is it should return null for not available data

This is a demonstration on how this can be done, use LEFT JOIN instead.

3. what if I have one more database and one more currency table for 3rd customer ?

The same as above add another join condition

Here left join will fetch data from left table. What if, for given date range, left table doesn't have data but right table has some data ? I can't go with left/right/inner join. The requirement is I want to fetch records for individual query and return result in single set. If, in any table, data is not available, it must return null value in that column

In that case you need full outer join which MySQL doesn't support but you can emulate it:

https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql#:~:text=We%20can%20emulate%20it%20by,t2%60%20ON%20%60t1%60.

https://www.geeksengine.com/database/multiple-table-select/full-join.php

SELECT db10.Rupee,
       db10.Yen,
       db20.Dollar,
       db20.Pound
FROM db10.currency
LEFT JOIN db20.currency ON db10.currency.dateTime = db20.currency.dateTime 
WHERE dateTime between value1 and value2
UNION
SELECT db10.Rupee,
       db10.Yen,
       db20.Dollar,
       db20.Pound
FROM db10.currency
RIGHT JOIN db20.currency ON db10.currency.dateTime = db20.currency.dateTime 
WHERE dateTime between value1 and value2;