Intermediate10 min read

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 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 , 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 DESC

It 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.accountId
ValidationException: 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 member

For 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 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 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 tickets table and an accounts 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.

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) > 500

It works within DynamoDB's access-pattern rules, and the compiler enforces them:

  • The joined table must be keyed by the ON attribute: the partition key of a table with no sort key, or of exactly one GSI. Here that's accounts, keyed on accountId. Joining the other way needs a GSI on tickets.accountId.
  • INNER and LEFT joins only, and no self-joins.
  • A WHERE that sets the FROM table's partition key (or a GSI partition key) with = turns its read into a Query. This example has none, so tickets is a full scan, and each match is then looked up on accounts by 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 SUM shows partial until 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.

By Dmitriy · Updated