Courses/SQL/Cross-Database Queries

Lesson 45 • Advanced Track

Cross-Database Queries & Foreign Data Wrappers

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.

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

Before you start: you should already be comfortable with JOINs and aggregate functions, since a cross-database query is just a JOIN where one table happens to live elsewhere. The in-browser editor lets you write and edit SQL. The setup statements below (CREATE SERVER, CREATE USER MAPPING) need a real server with FDW configured to actually run, so treat them as worked patterns to read and adapt — the query examples and Your Turn answers are shown inline so you can self-check.

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.

Result — local: customers:

id	name
1	Ava
2	Liam
3	Noor

Result — foreign: remote.page_views:

customer_id	viewed_at
1	2026-06-10
1	2026-06-11
2	2026-06-12
…	…

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.

postgres_fdw setup

SERVER → USER MAPPING → IMPORT FOREIGN SCHEMA.

Try it Yourself »

SQL

-- 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 'analyti
...

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: CREATE SERVER / MAPPING / TABLE

Replace the ___ blanks with the right keywords.

Try it Yourself »

SQL

-- 🎯 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
    OPTI
...

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.

Pushdown: when PostgreSQL can, it sends your WHERE filter to the remote server so only matching rows travel back. Always filter on the foreign table — it's the single biggest speed-up.

JOIN local + foreign

Count page views per customer across two databases.

Try it Yourself »

SQL

-- 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-
...

Result — 3 rows:

name	views	last_seen
Ava	5	2026-06-11
Liam	3	2026-06-12
Noor	1	2026-06-12

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: JOIN local + foreign

Combine customers with remote.page_views.

Try it Yourself »

SQL

-- 🎯 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 one-off query

Run a query on a remote server inline.

Try it Yourself »

SQL

-- 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.

Linked server + OPENQUERY

4-part naming vs. remote-side execution.

Try it Yourself »

SQL

-- 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 WH
...

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 table

A local shortcut to a remote MySQL table.

Try it Yourself »

SQL

-- 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.

📘 Quick Reference

Tool	Engine	Best for
postgres_fdw	PostgreSQL	Remote PG tables queried like local ones
CREATE USER MAPPING	PostgreSQL	Stored credentials for a foreign server
dblink(...)	PostgreSQL	One-off ad-hoc remote queries
Linked server + 4-part name	SQL Server	Cross-instance access
OPENQUERY	SQL Server	Push the query to run remotely
FEDERATED engine	MySQL	A local shortcut to a remote MySQL table

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

JOIN local customers to foreign remote.orders, sum spend, keep over 500.

Try it Yourself »

SQL

-- 🎯 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 que
...

🎉 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

Cross-Database Queries & Foreign Data Wrappers

What You'll Learn

Real-World Analogy

Our Two Tables: one local, one foreign

1. PostgreSQL FDW — Set Up a Foreign Server

postgres_fdw setup

Your Turn: wire up a foreign server

🎯 Your Turn: CREATE SERVER / MAPPING / TABLE

2. JOIN Local and Foreign Tables

JOIN local + foreign

Your Turn: join across the two tables

🎯 Your Turn: JOIN local + foreign

3. dblink — Ad-Hoc Remote Queries

dblink one-off query

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

Linked server + OPENQUERY

5. MySQL — The FEDERATED Engine

MySQL FEDERATED table

The Trade-Offs (read this before production)

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Top Spenders Across Databases

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings