Most teams start with one database and slowly collect more. Sales runs on MySQL, accounting on PostgreSQL, and operations keeps a few Excel files nobody wants to retire. Then someone asks for a single report that shows revenue per customer next to open invoices, and the question appears: how do we join data that lives in different systems?
Why a plain SQL join does not work
A JOIN only works inside one database engine. PostgreSQL cannot see the tables in your MySQL server, and the reverse is also true. So you need something that brings the rows together first. The usual options differ in cost, freshness and how much work they leave behind.
Option 1: export to CSV and combine in a spreadsheet
This is where most teams begin. It is fast for a one-off question and needs no setup. The downsides show up the second time you need it: the files are stale as soon as they are exported, the steps live in one person's head, and a single wrong key match goes unnoticed.
Option 2: database-level federation
Some engines can reach out to another system. PostgreSQL has foreign data wrappers, SQL Server has linked servers. They keep data live and let you write ordinary SQL, but each pair of systems needs its own setup, network access between the servers, and a DBA who understands the performance side effects. A badly planned cross-server join can pull a whole table over the network.
Option 3: a warehouse and ETL
Copy everything into one analytical store on a schedule and query it there. This is the right answer when you have large volumes, many consumers, and a team to run it. It is usually too much for a handful of recurring reports: you pay for storage, pipelines and the people who keep both healthy.
Option 4: a tool that joins across connections
A middle path is a tool that connects to each source, reads only the columns and rows you ask for, and performs the join itself. You get fresh data without building a warehouse. The trade-off is volume: this suits reports, reconciliations and checks, not scans over hundreds of millions of rows.
What the join looks like
Whatever the tool, the logic is the same. Here is what you would write if both tables lived in one database:
SELECT c.customer_id,
c.name,
SUM(o.total) AS revenue_30d,
SUM(i.amount_due) AS open_invoices
FROM customers c -- MySQL (sales)
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN invoices i ON i.customer_id = c.customer_id -- PostgreSQL (accounting)
WHERE o.created_at >= CURRENT_DATE - 30
GROUP BY c.customer_id, c.name;Pitfalls to check before trusting the result
- Key types: a customer id stored as an integer on one side and as text with leading zeros on the other will not match until you convert it.
- Duplicate keys: if the right table has two rows per customer, the join multiplies the left rows and totals double. Compare row counts before and after.
- Collation and case: "ABC-001" and "abc-001 " are different keys until you normalize case and trim spaces.
- Time zones: one system may store UTC and the other local time. A "today" filter then returns different days.
- Nulls: inner joins silently drop rows with missing keys. Decide whether you want a left join and a visible "no match" group.
Choosing between the options
- One-off question, small data: a spreadsheet is fine, just document the steps.
- Recurring report, moderate data, small team: use a tool that joins across connections.
- Heavy analytics, large history, many users: invest in a warehouse.
- Two systems that must always agree in real time: federation or application-level integration.
Doing it in Ruamhub
In Ruamhub you connect each database once, drag a table from each connection onto the Join Canvas, draw a line between the matching columns, and preview the result. When the preview looks right, save it as a pipeline and run it again whenever you need fresh numbers, or on a schedule. The number of rows per query and runs per month depend on your plan.