DynamoDB JOIN: How to Join Tables

There is no JOIN in DynamoDB. The API has no join operator, the data model
has no foreign keys, and — the part that surprises most people — PartiQL, the
SQL-flavoured query layer, doesn't add one either. A PartiQL SELECT reads
exactly one table.

If you came from a relational database, this is the first wall you hit. This
guide covers why the wall is there, the four things developers do instead, the
one case where you genuinely need a real join — and how to run one.

Can DynamoDB do joins?

No. DynamoDB cannot join tables — not through the low-level API (GetItem /
Query / Scan / BatchGetItem), not through PartiQL, and not through any
built-in query planner, because there is none. Every read maps to one table or
one of its indexes; combining two tables on a matching key is something you do
in your app after DynamoDB returns the items, never inside it.

• DynamoDB has no JOIN operator. It never has.
• PartiQL's SELECT is single-table only — the grammar is literally SELECT … FROM {{table}}[.{{index}}], and pointing it at two tables returns ValidationException: Only Select from a Single Table or index supported.
• The fix AWS recommends is to not need a join: denormalize, or use single-table design so related items live in one partition you fetch in a single request.
• For the genuine cross-table / ad-hoc case, you join outside DynamoDB — in your app, or with a tool that does it for you.

Why DynamoDB has no joins

A SQL JOIN asks the database to read multiple tables and assemble them at query
time. AWS's own
guide to modeling relational data
spells out the cost: a query like

SELECT * FROM Orders
  INNER JOIN Order_Items ON Orders.Order_ID = Order_Items.Order_ID
  INNER JOIN Products    ON Products.Product_ID = Order_Items.Product_ID
  INNER JOIN Inventories ON Products.Product_ID = Inventories.Product_ID
  ORDER BY Quantity_on_Hand DESC

is flexible, but "each join in the query increases the runtime complexity of the
query because the data for each table must stage and then be assembled." That
work is unbounded — its cost depends on the data, not on the query — which is
exactly the property DynamoDB refuses to have.

So AWS designed the constraint in. DynamoDB is, in their words, "built to
minimize both [CPU and network] constraints by eliminating JOINs (and
encouraging denormalization of data) and optimizing the database architecture to
fully answer an application query with a single request to an item." Those are
the qualities that buy single-digit-millisecond latency at any scale: the
runtime cost of a DynamoDB read is constant regardless of table size. There is
no join engine and no foreign-key concept to plan against — by design.

Note

This is a modelling philosophy, not a missing feature. The whole point of
DynamoDB is that you decide your access patterns up front and shape the data so
each one is a single key lookup. A join at read time means you didn't — or
couldn't — do that.

"But PartiQL is SQL, surely it joins?"

No. PartiQL gives you SELECT / INSERT / UPDATE / DELETE syntax over
DynamoDB, but it is SQL-compatible, not SQL. The
official SELECT grammar
is:

SELECT  {{expression}}  [, ...]
FROM    {{table}}[.{{index}}]
[ WHERE {{condition}} ]
[ ORDER BY {{key}} [DESC|ASC], ... ]

FROM takes one table (optionally one of its indexes). There is no second
FROM table, no JOIN, no subquery, no CTE. Point PartiQL at two tables and
DynamoDB rejects it
(reported on AWS re:Post):

ValidationException: Only Select from a Single Table or index supported

If you want the full reasoning on why PartiQL looks like SQL but can't behave
like it, see PartiQL vs SQL.

The 4 workarounds devs actually use

1. Denormalize (copy the data in)

Store the fields you'd otherwise join onto the item directly. An Order carries
a snapshot of the customerName and shippingAddress instead of a
customerId you'd resolve later. One read, no join.

The cost is write-time fan-out: when the source changes you update every copy
(typically via a DynamoDB Streams handler). You're trading read complexity for
write complexity — usually a good trade for a read-heavy app.

2. Single-table design (pre-join in the partition)

Put related entities in one table under a shared partition key so an
item collection is the joined result. A customer and all their orders share
PK = "CUSTOMER#42"; one Query returns the customer item plus every order
item — the "join" already happened at write time.

Example	Notes
Query PK = "CUSTOMER#42"
→ CUSTOMER#42 / PROFILE (the customer)
→ CUSTOMER#42 / ORDER#1001 (an order)
→ CUSTOMER#42 / ORDER#1002 (an order)

This is the canonical DynamoDB answer to one-to-many relationships. Full
walkthrough in single-table design.

3. Application-side join (two reads, stitch in code)

Read from table A, take the keys you got back, read from table B, and merge the
two result sets in your application. It's the relational join logic — just
running in your code instead of the database:

// "Get each order with its customer name" — the manual join.
const {Items: orders} = await ddb.query({TableName: 'Orders' /* … */});

const customers = await Promise.all(
  orders.map((o) => ddb.get({TableName: 'Customers', Key: {id: o.customerId}}))
);

const joined = orders.map((o, i) => ({
  ...o,
  customerName: customers[i].Item?.name
}));

Fine for small fan-out. With many orders it becomes an N+1 problem — one read
to list orders, then one read per order — which is slow and burns read capacity.
BatchGetItem (next) collapses that second wave into one round-trip.

4. BatchGetItem (one round-trip, multiple tables)

BatchGetItem
is the closest the API gets to "touch two tables at once": one
request returns "the attributes of one or more items from one or more
tables," up to 100 items or 16 MB per call, whichever it hits first. It
cuts the round-trips of an app-side join — but it is not a join. You
"identify requested items by primary key"; there is no ON condition and no
relational matching. You still have to know the keys up front and stitch the
responses together yourself.

Warning

BatchGetItem and app-side joins both require you to already know the keys on
both sides. They do not discover relationships — there's no WHERE a.id =
b.fk for DynamoDB to resolve. If you don't have the foreign keys in hand,
neither helps.

When a real JOIN is unavoidable

The four workarounds cover production read paths well. Where they fall down is
the ad-hoc, exploratory, analytical query — the one you didn't model for:

• "Which customers in the EU placed an order over $500 last month?" across an Orders table and a Customers table.
• A one-off data-quality check joining two entity types.
• Reporting and aggregates (GROUP BY, SUM, COUNT) — which DynamoDB has no operator for at all.

These are exactly the queries you can't pre-bake into a partition, because by
definition you didn't know you'd ask them. The relational instinct — write a
JOIN — is the right one here. DynamoDB just can't serve it natively, and
neither can PartiQL.

The usual heavyweight answer is to
export to S3 and query with Athena
(or use Athena's federated query connector to JOIN a live table), or pipe into
a warehouse. That's correct for true analytics at scale, but it's a
lot of plumbing for a question you want answered now, against your live table.

Running a real JOIN with DynoTable's SQL Workbench

DynoTable is a desktop DynamoDB client whose SQL Workbench runs
actual SQL — including JOIN, GROUP BY, and aggregate functions — over your
DynamoDB tables. It reads the items through the normal DynamoDB API, then
executes the relational parts of the query in the client. So you can write:

SELECT  c.name, SUM(o.total) AS spend
FROM    Customers c
JOIN    Orders o ON o.customerId = c.id
WHERE   c.region = 'EU'
GROUP BY c.name
HAVING  SUM(o.total) > 500

— and get a result set, against tables that have no relationship defined and a
query engine that has no JOIN keyword.

The honest caveat — "within DynamoDB's access-pattern rules": the Workbench
still reads through DynamoDB, so an unbounded join is an unbounded read. The
fastest queries are the ones where the WHERE clause (or the join's ON
attribute) hits a partition key or a GSI on at least one
side, so DynamoDB runs a Query rather than a full table
scan before the join executes. The Workbench doesn't
repeal the constraints in this guide — it just lets you ask the SQL question
instead of hand-writing the stitch yourself, and tells you what it's doing
underneath.

Among GUI clients, it's the only "yes, you can join" that's actually true: PartiQL and AWS's own
NoSQL Workbench
— whose operation builder runs single-table operations and PartiQL statements
(no JOIN, no multi-table SELECT) — both stop at the single-table wall, as do most
other GUI clients. See how DynoTable compares as a
DynamoDB GUI.

FAQ

Does PartiQL support JOIN?
No. PartiQL's SELECT reads a single table (or one of its indexes). A
multi-table query returns ValidationException: Only Select from a Single Table
or index supported. Same wall as the rest of the API.

Can you join two DynamoDB tables in one query?
Not natively. The DynamoDB API has no statement that reads two tables and matches
them on a key. BatchGetItem can read items from multiple tables in one request,
but it has no ON condition — it returns the items you named by primary key and
leaves the matching to you. A real JOIN … ON … only happens outside DynamoDB:
in your app, or in DynoTable's SQL Workbench.

Can you join a table to its GSI?
No — a Global Secondary Index isn't a separate table you
join to; it's an alternative key view of the same items. You Query either
the table or the index in a given SELECT, not both joined together. A GSI
lets you reach items by a different key, which often removes the need for a
join in the first place.

Can you join across two AWS accounts (or two tables in different accounts)?
Not natively — there's no cross-account join primitive. BatchGetItem can reach
another account's table if that table's resource-based policy grants your caller
access (since March 2024), but it still has no ON condition, so it's a
multi-table read, not a join. You'd read each side and join the results in your
application or in a tool like DynoTable's Workbench.

Is denormalization really better than a join?
For DynamoDB's target workload — predictable, high-volume reads — yes. You move
the cost to write time (and accept some data duplication) in exchange for
single-request reads that scale flatly. The
single-table design guide covers the trade-offs.

---

Building the keys and conditions for these reads by hand is fiddly — the
expression builder generates the
KeyConditionExpression / FilterExpression syntax for you, and
DynoTable runs the real SQL when a workaround won't cut it.