SQL for DynamoDB: What Works, What Doesn't, and the Workbench

DynamoDB is a NoSQL key-value store, but it answers SQL-shaped questions more than people expect — and far less than they hope. This is the honest map: what SQL-on-DynamoDB you actually get out of the box, where it stops, and the few ways to run the JOIN / GROUP BY / aggregate queries the native surface can't express.

Can you query DynamoDB with SQL? The short answer

Partly. DynamoDB ships PartiQL, which the AWS docs describe as "a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB." So you can write SELECT * FROM "Orders" WHERE OrderID = 100 and it works.

But PartiQL is a SQL-compatible surface over the DynamoDB API, not a SQL engine. It speaks the syntax; it does not add relational query power. AWS is explicit that "Amazon DynamoDB supports a subset of the PartiQL query language" (reference). The moment you reach for a JOIN, a GROUP BY, or COUNT(*), you're outside what PartiQL can do — see PartiQL vs SQL for the full feature-by-feature comparison.

PartiQL: a SQL-compatible surface, not a SQL engine

PartiQL maps SQL-looking statements onto the same data-plane operations the SDK exposes. A SELECT with a partition-key equality compiles to a Query; a SELECT without one compiles to a Scan. Per the AWS SELECT reference:

Using the SELECT statement can result in a full table scan if an equality or IN condition with a partition key is not provided in the WHERE clause.

So the same access-pattern rules that govern Query and Scan still apply — PartiQL just hides them behind familiar syntax. It adds no query planner, no joins, and no set-based aggregation. Every statement collapses to one native operation:

If an operation doesn't reduce to a single Get/Query/Scan/Put/Update/Delete, PartiQL simply can't express it. Everything below is a consequence of that one fact.

What PartiQL covers

DynamoDB's PartiQL supports four DML/query statements:

• SELECT — read items (compiles to Query or Scan)
• INSERT — add an item (PutItem)
• UPDATE — modify an item (UpdateItem)
• DELETE — remove an item (DeleteItem)

It also supports transactions and batch operations. A well-formed read targets the partition key with an equality or IN:

SELECT OrderID, Total
FROM "Orders"
WHERE OrderID IN [1, 2, 3] ORDER BY OrderID DESC

ORDER BY is allowed, but the AWS reference restricts the ordering key to "a hash key or a sort key" — the partition or sort key, not arbitrary columns. That's the ceiling of what PartiQL's SELECT accepts. For copy-paste-ready statements, see PartiQL examples.

What PartiQL can't do

These are the things developers most often expect from "SQL," and PartiQL supports none of them:

• No JOIN. The PartiQL SELECT syntax is a single FROM {{table}}[.{{index}}] — one table or one index, never two tables related on a key. This is the single-table-design tradeoff: you model for your access patterns up front because the query layer can't reshape data afterward.
• No GROUP BY. It isn't in the grammar; there's no clause to group rows.
• No aggregate functions. The PartiQL functions reference lists exactly one function under "Aggregate functions": SIZE, which returns an attribute's size in bytes for a single item. There is no COUNT, SUM, AVG, MIN, or MAX across rows. AWS states plainly: "Any SQL functions that are not included in this list are not currently supported in DynamoDB."
• No LIKE, no subqueries, no UNION, no window functions. Pattern matching uses contains / begins_with; the rest have no equivalent at all.

So "total revenue by customer last month" — a one-line GROUP BY in any relational database — can't be expressed in PartiQL. You'd scan the data out and aggregate it in application code.

The only way to get real JOIN / GROUP BY / aggregate behavior over DynamoDB data is a tool that runs an actual SQL engine on top of it. There are two: Amazon Athena's federated connector, and DynoTable's SQL Workbench.

How to query DynamoDB with real SQL via Amazon Athena

AWS's own answer to "real SQL over DynamoDB" is the Amazon Athena DynamoDB connector, which "enables Amazon Athena to communicate with DynamoDB so that you can query your tables with SQL." Because Athena is a full SQL engine, this does get you JOIN and aggregates — AWS's walkthrough is titled "Access, query, and join Amazon DynamoDB tables using Athena."

The catch is setup and cost:

• It's a Lambda-based federated connector you deploy into your account (via the Athena console or the Serverless Application Repository), wired through AWS Glue for schema and spilling results to an S3 bucket (connector docs).
• Under the hood it still uses DynamoDB's Query and Scan API operations. AWS warns that "queries that use scans can consume a large number of read capacity units (RCUs)," so an analytical query over a big table reads — and meters — a lot of items (connector costs). Use the item-size calculator to gauge what a scan-heavy query will cost.
• Write operations like INSERT INTO are not supported through the connector.

Athena is the right tool for scheduled analytics and BI dashboards. It's heavy for the everyday "I just need to join two tables and eyeball the result" case — that's the gap the next section fills.

DynoTable SQL Workbench: SQL within DynamoDB's access-pattern rules

DynoTable's SQL Workbench runs real SQL — JOIN, GROUP BY, COUNT/SUM/AVG — against your live DynamoDB tables from a desktop client, with no Lambda, Glue, or S3 to stand up. It materializes the rows through DynamoDB's real Query/Scan runtime, then runs your full SQL in an in-memory engine on top:

-- Runs in the DynoTable Workbench (NOT in PartiQL):
SELECT c.country, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
INNER JOIN customers c ON o.customerId = c.PK
GROUP BY c.country
ORDER BY revenue DESC

The "within DynamoDB's access-pattern rules" part matters. The Workbench doesn't pretend DynamoDB is Postgres — it still reads through Query/Scan under the hood, so you stay aware of what each query costs, and it enforces DynamoDB's access model rather than hiding it:

• INNER JOIN and LEFT JOIN only — the ON target attribute must be a partition key or GSI partition key. No RIGHT / FULL / CROSS / comma-join.
• No self-joins yet, no subqueries, no derived tables, no window functions.
• Joins and projections operate on scalar attributes.

If you only need to compose the conditions and key expressions for the raw API — not a full SQL statement — the DynamoDB Expression Builder generates the correct FilterExpression / KeyConditionExpression without the PartiQL surface at all.

If your goal is a DynamoDB SQL client for exploring, debugging, and analyzing tables, the Workbench fills that gap — and the rest of DynoTable is a full DynamoDB GUI around it.

Try DynoTable to run real SQL against your own tables.

FAQ

Can you run SQL on DynamoDB? You can run PartiQL, a SQL-compatible subset (SELECT/INSERT/UPDATE/DELETE by key). For full SQL — JOIN, GROUP BY, aggregates — you need a SQL engine on top: the Amazon Athena DynamoDB connector, or DynoTable's SQL Workbench.

Does DynamoDB PartiQL support JOIN? No. The PartiQL SELECT syntax has a single FROM table or index and no join grammar. Joins require an engine layered over DynamoDB.

Does PartiQL support GROUP BY or aggregates like COUNT and SUM? No. There's no GROUP BY clause, and the only "aggregate" function is SIZE (an attribute's byte size for one item). COUNT, SUM, AVG, MIN, and MAX across rows aren't supported.

Is DynamoDB SQL or NoSQL? NoSQL — a key-value and document store. PartiQL adds a SQL-compatible query language on top, but DynamoDB has no relational engine, joins, or aggregates.

Is PartiQL good for ad-hoc queries? For key-based lookups, yes. For analytical ad-hoc queries (counts, rollups, joins), no — PartiQL can't express them, and unconstrained SELECTs silently become full table scans.

Is there a DynamoDB SQL client that handles JOIN and GROUP BY? Yes — DynoTable's SQL Workbench runs JOIN/GROUP BY/aggregates against live tables from the desktop, and Amazon Athena does it via a federated connector you deploy in your AWS account.