SQL for DynamoDB and the Limits of PartiQL

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?

Partly. DynamoDB ships PartiQL, a SQL-compatible language for
SELECT/INSERT/UPDATE/DELETE by key, so SELECT * FROM "Orders" WHERE
OrderID = 100 works. But it's a SQL-compatible surface over the DynamoDB API,
not a SQL engine — AWS supports only a subset, so JOIN, GROUP BY, and
COUNT(*) are out. For those you need an engine layered on top.

AWS describes PartiQL as
"a SQL-compatible query language, to select, insert, update, and delete data in
Amazon DynamoDB",
but is equally explicit that "Amazon DynamoDB supports a subset of the PartiQL
query language." 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.

Note

Is DynamoDB SQL or NoSQL? NoSQL. DynamoDB is a managed key-value and document
store with no relational engine, no schema, and no query planner. PartiQL gives
it a SQL-looking query language; it does not make it a relational database.

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:

A SELECT without a partition-key equality compiles to a full-table Scan. In
us-east-1 on-demand that bills 0.5 RCU per 4 KB eventually-consistent for
every item examined — a 500 MB table of 2 KB rows is roughly 62,500
RCU before any WHERE filter narrows the result set. Line-rate PartiQL-shaped
reads in the pricing calculator.

You write	DynamoDB runs
SELECT … WHERE PK = …	GetItem or Query
SELECT … (no PK)	Scan (reads the whole table)
INSERT INTO …	PutItem
UPDATE … WHERE PK=… AND SK=…	UpdateItem (one item)
DELETE … WHERE PK=… AND SK=…	DeleteItem (one item)

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.

Warning

Writing SELECT * FROM "Orders" WHERE Total > 500 looks like SQL, but Total
is a non-key attribute — the AWS docs list this exact Total > 500 predicate
among the SELECT statements that "will result in a full table scan"
(reference).
Same for WHERE Total IN [500, 600] or WHERE Total BETWEEN 500 AND 600 on a
non-key attribute. SQL syntax doesn't change DynamoDB's economics. See
Query vs Scan.

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. For interactive,
ad-hoc queries there are two: Amazon Athena's federated connector, and
DynoTable's SQL Workbench. (For scheduled analytics, DynamoDB's zero-ETL
integration to Amazon Redshift also runs SQL joins and aggregates.)

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 a single SELECT over them
locally on your desktop:

-- 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 JOIN, GROUP BY and aggregates you need a SQL engine on top: the Amazon
Athena DynamoDB connector, or DynoTable's SQL Workbench — a single SELECT dialect
with INNER/LEFT JOIN, no CTEs, unions or subqueries.

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.