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 PartiQL, the SQL-flavored query layer, doesn't
add one either: a PartiQL SELECT reads exactly one table.
Can DynamoDB do joins?
No, not through the API (GetItem, Query, Scan, BatchGetItem) and not
through PartiQL. Each read is a key lookup or a scan of one table or index; matching
two tables on a key happens in your code, after DynamoDB returns the items.
- Model the join away. AWS recommends you denormalize, or use
single-table design so related items live in
one partition you fetch with one
Query. - Stitch it in your app when you can't: read one side, then fetch the other by
key with
BatchGetItem. - Use a SQL engine for ad-hoc and analytical joins: Athena over an S3 export or its DynamoDB connector, or DynoTable's SQL Workbench against live tables.
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 of a multi-join query like this one:
SELECT * FROM tickets
INNER JOIN comments ON tickets.ticketId = comments.ticketId
INNER JOIN accounts ON accounts.accountId = tickets.accountId
INNER JOIN agents ON agents.agentId = comments.agentId
ORDER BY tickets.createdAt DESCIt 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 grows with the data, which is the property DynamoDB refuses to have.
DynamoDB is "built to minimize both [CPU and network] constraints by eliminating
JOINs (and encouraging denormalization of data)," answering a query "with a
single request to an item."
That buys fast reads at any scale: "the runtime complexity for DynamoDB operations is constant, regardless of data size, for common access patterns." There is no join engine and no foreign-key concept to plan against.
PartiQL reads one table per statement
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 GROUP BY, no subquery, no CTE. Run against DynamoDB
Local 3.3.1, the three ways of asking for a join fail like this.
An explicit JOIN:
SELECT t.ticketId FROM "tickets" t JOIN "accounts" a ON t.accountId = a.accountIdValidationException: Only select from a single table or index is supported.Two tables in FROM get the same rejection, so the engine is refusing the second
table, not the JOIN keyword:
SELECT * FROM "tickets", "accounts"ValidationException: Only select from a single table or index is supported.A subquery is rejected earlier, on the IN operand, with a message that never
mentions tables:
SELECT * FROM "tickets" WHERE accountId IN (SELECT accountId FROM "accounts")ValidationException: IN operator must have a left hand argument of type Variable Reference and right hand argument of type Seq with at least one memberFor the rest of the gap between PartiQL and SQL, see PartiQL vs SQL.
Four workarounds
1. Denormalize (copy the data in)
Store the fields you'd otherwise join onto the item directly. A ticket carries a
snapshot of the accountName and plan instead of an accountId you'd resolve
later. One read, no join.
Write-time fan-out is the cost. When the source changes you update every copy, typically from a DynamoDB Streams handler. You trade 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. An account and all its
tickets share PK = "ACCOUNT#acme", and one Query returns the account plus every
ticket.
Query PK = "ACCOUNT#acme"
→ ACCOUNT#acme / PROFILE (the account)
→ ACCOUNT#acme / TICKET#1001 (a ticket)
→ ACCOUNT#acme / TICKET#1002 (a ticket)
This is the standard 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. This uses the AWS SDK v3 document client:
import {DynamoDBClient} from '@aws-sdk/client-dynamodb';
import {DynamoDBDocument} from '@aws-sdk/lib-dynamodb';
const ddb = DynamoDBDocument.from(new DynamoDBClient({}));
// "Get each open ticket with its account name": the manual join.
// First page only; loop on LastEvaluatedKey for the rest.
const {Items: tickets} = await ddb.query({
TableName: 'tickets',
IndexName: 'byStatus',
KeyConditionExpression: '#s = :open',
ExpressionAttributeNames: {'#s': 'status'},
ExpressionAttributeValues: {':open': 'OPEN'}
});
const accounts = await Promise.all(
tickets.map((t) => ddb.get({TableName: 'accounts', Key: {accountId: t.accountId}}))
);
const joined = tickets.map((t, i) => ({
...t,
accountName: accounts[i].Item?.name
}));Fine for small fan-out. With many tickets it becomes an N+1 problem: one read to
list tickets, then one read per ticket. BatchGetItem cuts that second wave to one
call per 100 keys.
4. BatchGetItem (fewer round-trips, multiple tables)
BatchGetItem
returns "the attributes of one or more items from one or more tables," up to
100 items or 16 MB per call. It is not a join: you "identify requested items
by primary key," with no ON condition.
It can also return less than you asked for: it "returns a partial result if the
response size limit is exceeded, the table's provisioned throughput is exceeded…"
and lists the rest in UnprocessedKeys.
Retry those with backoff, or rows go missing. This replaces the Promise.all above:
const ids = [...new Set(tickets.map((t) => t.accountId))];
const byId = new Map();
for (let i = 0; i < ids.length; i += 100) {
let request = {accounts: {Keys: ids.slice(i, i + 100).map((accountId) => ({accountId}))}};
for (let attempt = 0; Object.keys(request).length > 0; attempt++) {
if (attempt === 8) throw new Error('BatchGetItem: keys still unprocessed');
if (attempt > 0) await new Promise((r) => setTimeout(r, Math.min(50 * 2 ** attempt, 5000)));
const res = await ddb.batchGet({RequestItems: request});
for (const a of res.Responses?.accounts ?? []) byId.set(a.accountId, a);
request = res.UnprocessedKeys ?? {};
}
}
const joinedTickets = tickets.map((t) => ({...t, accountName: byId.get(t.accountId)?.name}));When a real JOIN is unavoidable
The four workarounds cover production read paths. They fall down on the ad-hoc, exploratory, analytical query, the one you didn't model for:
- "Which enterprise accounts logged more than 500 support minutes last month?"
across a
ticketstable and anaccountstable. - 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.
You can't pre-bake these into a partition, because you didn't know you'd ask them.
Writing a JOIN is the right instinct; DynamoDB and PartiQL just can't run it.
The AWS-native answer is a SQL engine outside DynamoDB. For recurring reports, export the table to S3 and query the export with Athena, which reads no table capacity (the steps).
For an ad-hoc query against the live table, the Athena DynamoDB connector runs SQL,
JOIN included, over the table itself and bills the reads it scans
(setup). Both are plumbing for a question you want
answered now.
Running a real JOIN with DynoTable's SQL Workbench
DynoTable is a desktop DynamoDB client whose SQL Workbench runs
SQL with JOIN, GROUP BY and aggregate functions over your DynamoDB tables. It
reads the items through the DynamoDB API, then runs the relational part in the
client:
SELECT a.name, SUM(t.minutesSpent) AS minutes
FROM tickets t
JOIN accounts a ON t.accountId = a.accountId
WHERE a.plan = 'enterprise'
GROUP BY a.name
HAVING SUM(t.minutesSpent) > 500It works within DynamoDB's access-pattern rules, and the compiler enforces them:
- The joined table must be keyed by the
ONattribute: the partition key of a table with no sort key, or of exactly one GSI. Here that'saccounts, keyed onaccountId. Joining the other way needs a GSI ontickets.accountId. INNERandLEFTjoins only, and no self-joins.- A
WHEREthat sets theFROMtable's partition key (or a GSI partition key) with=turns its read into aQuery. This example has none, soticketsis a full scan, and each match is then looked up onaccountsby key. - The status bar estimates the base read before you run. Reads on the joined table
are flagged, not priced. An Execute fetches the first page; Run to end
(⌘⇧↩) reads the rest, and the
SUMshowspartialuntil it does.
AWS's own NoSQL Workbench runs single-table operations and PartiQL statements, with no JOIN.
Of the ten clients in our DynamoDB GUI comparison, DynoTable is the only one that joins live tables; every other SQL surface there is PartiQL underneath.
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 is supported. Same wall as the rest of the API.
Can you join two DynamoDB tables in one query?
Not natively. BatchGetItem can read items from multiple tables in one request, but
it has no ON condition and leaves the matching to you. A real JOIN … ON …
happens outside DynamoDB: in your app, in Athena, 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, not both joined together. A GSI lets you reach items by
a different key, which often removes the need for a join.
Can you join across two AWS accounts (or two tables in different accounts)?
Not natively. If the other table's resource-based policy grants your caller access
and your own IAM policy allows the call (since March 2024), BatchGetItem and Query take its full table ARN in place of the
name: read each side and merge the rows in your code. For SQL, the table's owner
shares an Athena DynamoDB connector with your account and your Athena runs the JOIN
(AWS walkthrough).
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 duplication, in exchange for single-request reads that scale flatly. The single-table design guide covers the trade-offs.
The expression builder writes the
KeyConditionExpression and FilterExpression for these reads, and
DynoTable runs the SQL when a workaround won't do.