DynamoDB JOIN: How to Join Tables (and Why You Usually Can't)

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

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

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.getItem({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.

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

It's the only "yes, you can join" that's actually true: PartiQL and AWS's own NoSQL Workbench — whose operation builder is limited to single-table data-plane operations (Query / Scan / GetItem) — 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, and not with BatchGetItem either — a single request can't span credentials, and there's no cross-account join primitive. You'd read each table with its own account's credentials 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.