Cross-Database Queries

Reviewed & published by Brayan K

By the end of this lesson you'll be able to query data that lives in a different database — or even a different server or engine — using ordinary SQL. You'll set up a PostgreSQL Foreign Data Wrapper, JOIN a local table to a remote one, and know which tool (FDW, dblink, linked servers, FEDERATED) fits each job — plus the traps that make these queries slow or unsafe.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Set up postgres_fdw: SERVER, USER MAPPING, IMPORT FOREIGN SCHEMA
• JOIN a local table to a foreign (remote) table
• Run ad-hoc remote queries with dblink
• Query SQL Server with OPENQUERY and 4-part names
• Reach MySQL data with the FEDERATED engine
• Reason about pushdown, latency, and cross-system transactions

Real-World Analogy

Imagine your company has filing cabinets in London, New York, and Tokyo. A Foreign Data Wrapper is like installing a dumbwaiter to each remote office: you stay at your London desk, ask for a folder, and the system fetches it for you. You get one drawer that looks local but is really pulling paper across the ocean — which is exactly why what you ask for matters. Request "all invoices since Monday" and only those cross the wire; request "everything" and you wait while the whole archive is shipped.

Our Two Tables: one local, one foreign

customers lives in your local database. page_views lives on a remote analytics server and reaches you as the foreign table remote.page_views. The whole lesson JOINs across these two.

1. PostgreSQL FDW — Set Up a Foreign Server

A Foreign Data Wrapper (FDW) is an extension that teaches PostgreSQL how to read another data source. With postgres_fdw, that source is another PostgreSQL database — and once it's wired up, its tables look and behave like local ones.

Setup is four steps: enable the wrapper, declare the remote server (where), create a user mapping (who connects, with credentials stored once instead of in every query), then import the remote table definitions into a local schema.

-- PostgreSQL postgres_fdw — turn a REMOTE database into LOCAL tables.
-- "FDW" = Foreign Data Wrapper: a translator that lets your local
-- PostgreSQL read another data source as if it were just another table.

-- Step 1: enable the wrapper (one-time, per database)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Step 2: describe WHERE the remote server lives
CREATE SERVER analytics_srv
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', port '5432', dbname 'analytics');

-- Step 3: say WHO connects (credentials live in a user mapping, not in queries)
CREATE USER MAPPING FOR current_user
    SERVER analytics_srv
    OPTIONS (user 'reporter', password 'secret');

-- Step 4: pull the remote table definitions into a local schema called "remote"
IMPORT FOREIGN SCHEMA public
    LIMIT TO (page_views)              -- only import the tables you need
    FROM SERVER analytics_srv
    INTO remote;

-- After this, remote.page_views is queryable exactly like a local table.

Your Turn: wire up a foreign server

Fill in the three blanks to register a remote server, map your credentials, and declare a single foreign table by hand. The expected answers are in the comments.

-- 🎯 YOUR TURN — fill in the three blanks, then press "Try it Yourself".
-- Goal: register a remote PostgreSQL server and map your credentials.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE ___ billing_srv               -- 👉 keyword that registers a remote server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'billing.example.com', port '5432', dbname 'billing');

CREATE USER ___ FOR current_user     -- 👉 keyword that ties a login to the server
    SERVER billing_srv
    OPTIONS (user 'reporter', password 'secret');

CREATE FOREIGN ___ remote_invoices ( -- 👉 the object type a foreign column set lives in
    invoice_id INT,
    amount     NUMERIC
) SERVER billing_srv OPTIONS (schema_name 'public', table_name 'invoices');

-- ✅ Expected: 3 statements succeed; remote_invoices is now a foreign table.
--    Blanks are:  SERVER  ,  MAPPING  ,  TABLE

2. JOIN Local and Foreign Tables

This is the payoff: a foreign table behaves like any other table, so you can JOIN it to local data in one ordinary query. Below, the local customers table joins the foreign remote.page_views table to count views per customer.

-- A foreign table behaves like any other table — you can SELECT, JOIN,
-- filter and aggregate it. Here we JOIN a LOCAL table (customers) to a
-- FOREIGN table (remote.page_views) in a single, ordinary query.

SELECT
    c.name,
    COUNT(*)            AS views,
    MAX(v.viewed_at)    AS last_seen
FROM customers c                       -- local table
JOIN remote.page_views v               -- foreign table (lives on another server)
    ON v.customer_id = c.id
WHERE v.viewed_at >= DATE '2026-06-08' -- filter pushes to the remote side
GROUP BY c.name
ORDER BY views DESC;

Your Turn: join across the two tables

Two blanks — the keyword that combines tables, and the local column that lines up with v.customer_id.

-- 🎯 YOUR TURN — fill in the two blanks to JOIN local + foreign data.
-- customers is LOCAL; remote.page_views is a FOREIGN table.

SELECT c.name, COUNT(*) AS views
FROM customers c
___ remote.page_views v               -- 👉 the keyword that combines two tables
    ON v.customer_id = c.___           -- 👉 the local column that matches v.customer_id
GROUP BY c.name
ORDER BY views DESC;

-- ✅ Expected result (3 rows):
--    Ava | 5 ,  Liam | 3 ,  Noor | 1
--    Blanks are:  JOIN  ,  id

3. dblink — Ad-Hoc Remote Queries

dblink runs a single query on a remote PostgreSQL server without defining a foreign table first. It's handy for one-off admin tasks. The catch: you must declare the result columns and their types every time, so it's verbose for anything you run often.

-- dblink runs a one-off query on a remote server without setting up
-- a foreign table first. Good for ad-hoc/admin work; verbose for daily use.

CREATE EXTENSION IF NOT EXISTS dblink;

SELECT *
FROM dblink(
    'host=shop-db dbname=shop user=reader password=secret',
    'SELECT order_id, total FROM orders WHERE total > 100'
) AS t(order_id INT, total NUMERIC);   -- you MUST declare the column types here

4. SQL Server — Linked Servers, 4-Part Names & OPENQUERY

SQL Server's equivalent is a linked server. After registering one with sp_addlinkedserver, you can address remote tables with a 4-part name [Server].[Database].[Schema].[Table].

But 4-part names can drag a whole remote table back to be filtered locally. OPENQUERY instead sends your query string to run on the remote server, so the filtering and aggregation happen there and only the result returns — much better pushdown.

-- SQL Server: a "linked server" registers another instance once...
EXEC sp_addlinkedserver
    @server = 'ANALYTICS', @srvproduct = '',
    @provider = 'SQLNCLI', @datasrc = 'analytics.example.com';

-- ...then you query it two ways:

-- (a) 4-part name  [Server].[Database].[Schema].[Table]
SELECT *
FROM ANALYTICS.marketing.dbo.campaigns;

-- (b) OPENQUERY — sends the inner query to run REMOTELY (better pushdown)
SELECT *
FROM OPENQUERY(ANALYTICS,
    'SELECT campaign_id, name FROM campaigns WHERE active = 1');

5. MySQL — The FEDERATED Engine

MySQL offers the FEDERATED storage engine: a local table that stores no data of its own and instead forwards every read and write to a table on another MySQL server, named by a connection URL. It must be enabled on the server (it's off by default), and it has real limits — no transactions across the link and limited index pushdown — so treat it as a convenience, not a foundation.

-- MySQL FEDERATED engine: a local table that is really a "shortcut"
-- to a table on another MySQL server. No data is stored locally.

CREATE TABLE remote_orders (
    order_id INT PRIMARY KEY,
    total    DECIMAL(10,2)
)
ENGINE = FEDERATED
CONNECTION = 'mysql://reader:secret@shop-host:3306/shop/orders';

-- Now query it like any table — MySQL forwards the request to shop-host:
SELECT order_id, total FROM remote_orders WHERE total > 100;

The Trade-Offs (read this before production)

• Latency: every foreign query crosses a network. A JOIN that's instant on local tables can take seconds when one side is remote. Filter early and return as few rows as possible.
• Pushdown isn't guaranteed: the planner pushes simple filters and joins to the remote side, but functions, complex expressions, and some JOIN shapes are evaluated locally — meaning the full table is dragged over first. Always check the EXPLAIN plan.
• Transactions don't span systems: a single BEGIN…COMMIT does not give you atomicity across two databases. If you write to a local and a foreign table, one can succeed while the other fails (true distributed transactions need two-phase commit, which most FDWs don't fully provide).
• Security: credentials in a user mapping run as that remote user — scope it to read-only and least privilege, and never paste passwords into ad-hoc dblink strings that land in logs.

Common Errors (and the fix)

• "Query is slow / pulls millions of rows": don't assume predicate pushdown always happens. Wrapping a foreign column in a function (e.g. LOWER(v.country)) or using a type the remote can't match often forces a full fetch. Run EXPLAIN (VERBOSE) and keep filters simple so they push down.
• "Half my data was written, half wasn't": you expected a cross-system transaction. A local COMMIT can't roll back a foreign write. Write to one system per transaction, or design an idempotent retry — don't rely on atomicity across the wire.
• "password authentication failed for user" / "permission denied": the USER MAPPING credentials are wrong or the remote role lacks SELECT. Fix the mapping and grant least-privilege read access on the remote side.
• "could not connect to server" / timeouts: network latency or a firewall. The host/port in CREATE SERVER must be reachable from the database host (not your laptop), and the remote must allow the connection.
• "relation 'remote.page_views' does not exist": you queried before importing. Run IMPORT FOREIGN SCHEMA (or CREATE FOREIGN TABLE) into the schema you reference, and qualify it: remote.page_views, not just page_views.

Frequently Asked Questions

Q: Is a foreign table a copy of the remote data?

No. It stores no rows locally — it's a live pointer. Every query reaches across the network to the remote server, which is why latency and pushdown matter so much.

Q: postgres_fdw or dblink — which should I use?

Use postgres_fdw for anything recurring: you set it up once and then write normal SQL. Reach for dblink only for ad-hoc, one-off queries where defining a foreign table isn't worth it.

Q: Can I run one transaction across two databases?

Not safely with a plain BEGIN…COMMIT. A local commit can't undo a foreign write. True atomicity across systems needs two-phase commit, which most wrappers don't fully provide — so design writes to touch one system at a time.

Q: Why is my cross-database JOIN so slow?

Usually a filter didn't push down, so the whole remote table was fetched and filtered locally. Check the plan with EXPLAIN, keep WHERE conditions simple, and prefer OPENQUERY on SQL Server to force remote-side execution.

Mini-Challenge: Top Spenders Across Databases

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then adapt it for a real FDW setup to confirm.

-- 🎯 MINI-CHALLENGE
-- A foreign table remote.orders(customer_id INT, total NUMERIC) already exists.
-- Using ONLY this lesson's ideas (foreign tables behave like local ones):
--   1. JOIN local customers (id, name) to remote.orders on customer_id = id
--   2. Sum total per customer  -> column called spend
--   3. Keep only customers whose spend is over 500 (filter the GROUP with HAVING)
--   4. Order highest spend first
--
-- ✅ Expected: name + spend, e.g.  Ava | 920 ,  Noor | 610

-- your query here

🎉 Lesson Complete

• ✅ postgres_fdw setup is four steps: extension → SERVER → USER MAPPING → IMPORT FOREIGN SCHEMA
• ✅ A foreign table JOINs to local tables in one ordinary query
• ✅ dblink handles one-off remote queries; FDW is better for recurring ones
• ✅ SQL Server uses linked servers, 4-part names, and OPENQUERY; MySQL has the FEDERATED engine
• ✅ Mind the trade-offs: latency, no guaranteed pushdown, no cross-system transactions, least-privilege credentials
• ✅ Next: Performance Testing — measure and benchmark queries so you can prove these cross-database calls are fast enough

Practice quiz

Continue this course

• Previous: Database Design Patterns for Large Systems (OLTP vs OLAP)
• Next: Performance Testing: Benchmarking, Stress Testing & Load Tools — Benchmark SQL performance with pgbench, sysbench, and custom load tests
• Quick reference: SQL cheat sheet

Related lessons

• JOINs
• aggregate functions