DynamoDB PartiQL vs SQL: What Breaks

The single biggest source of confusion with DynamoDB PartiQL — for humans and AI
assistants alike — is treating it as relational SQL. It isn't. PartiQL is a
SQL-compatible surface over DynamoDB's existing operations, not a query engine
that can join, group, or aggregate. The familiar keywords hide a very different
machine underneath.

How is DynamoDB PartiQL different from SQL?

PartiQL borrows SQL's syntax but not its engine. On DynamoDB every statement maps to a single native operation — GetItem, Query, Scan, PutItem, UpdateItem, or DeleteItem — so there is no JOIN, GROUP BY, subquery, or aggregate. It reads like SQL but can only do what those key-value operations already do.

Every PartiQL statement compiles down to one of DynamoDB's native operations:

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)

There is no planner that can read from two tables, build a hash join, or fold rows
into a COUNT. If an operation doesn't map to a single Get/Query/Scan/Put/Update/
Delete, PartiQL simply can't express it. That's the whole story — everything below
is a consequence of this one fact.

The same mapping, as a flow — the WHERE clause decides whether a SELECT is a
cheap Query or a whole-table Scan:

Each statement resolves to exactly one native operation — that one-to-one mapping is
why PartiQL can't join, group, or aggregate.

A SELECT without a partition-key predicate 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 — filters do not reduce the metered read. Size a row with the
item-size calculator and line-rate the
scan in the pricing calculator before you
run SQL-shaped syntax that hides the cost.

What's different — feature by feature

Wherever the Workbench column says Yes against a PartiQL No, that's a gap DynoTable's SQL Workbench closes. The Workbench
materializes your tables through DynamoDB's real query runtime and runs real SQL on top —
SQL within DynamoDB's access-pattern rules.

Feature	Standard SQL	DynamoDB PartiQL	DynoTable Workbench
JOIN … ON …	Yes	No	Yes — INNER / LEFT (to a PK or GSI partition key)
RIGHT / FULL / CROSS / comma-join	Yes	No	No
Self-join	Yes	No	No (not yet)
Subqueries / derived tables	Yes	No	No
CTEs (WITH …)	Yes	No	No
UNION / INTERSECT / EXCEPT	Yes	No	No
GROUP BY / HAVING	Yes	No	Yes
Aggregates (COUNT/SUM/AVG/MIN/MAX)	Yes	No	Yes
DISTINCT	Yes	No	Yes
CASE / CAST	Yes	No	Yes
Window functions	Yes	No	No
ORDER BY	Yes, any column	Partial — sort key only (needs partition-key WHERE)	Yes, any column
LIMIT	Yes	No inline (use the request limit param)	Yes
LIKE	Yes	No (use contains / begins_with)	Yes
IS NULL / IS NOT NULL	Yes	Yes (absent attributes are MISSING, not NULL — use IS MISSING)	Yes
SELECT * without a PK	scans	Partial — silent full-table Scan	Yes (with cost visibility)

What breaks, and why

These are the failures DynoTable's PartiQL validator flags before the query ever
reaches the wire — each traces to a real DynamoDB constraint.

• SELECT * without a partition key is a hidden Scan. PartiQL won't error; it just reads every item and filters afterward, which is the classic Query-vs-Scan cost footgun behind friendly syntax.
• UPDATE / DELETE need the full primary key. They map to a single-item UpdateItem/DeleteItem, so the WHERE must pin the partition key (and the sort key, on a composite-key table). You can't "update all rows where status = 'open'" in one statement.
• Double quotes are identifiers, not strings. DynamoDB PartiQL follows the SQL standard here: "name" is a column/table name, 'name' is a string value. Quoting a value with double quotes is the most common beginner error — the validator's message is literally "Double quotes delimit identifiers in DynamoDB PartiQL, not strings. Use single quotes for string values."
• IN uses brackets, not parentheses: WHERE pk IN ['a','b'], capped at 50 PK values / 100 non-key values.
• No JOIN, no aggregates. There is no engine to combine tables or fold rows. This is the single-table-design tradeoff: you model for your access patterns up front because the query layer can't reshape data after the fact.

Why AI assistants get this wrong

LLMs are trained on oceans of relational SQL, so they confidently emit JOIN,
GROUP BY, LIKE, inline LIMIT, and double-quoted string literals against
DynamoDB — all of which DynamoDB rejects. DynoTable's own model-query autofix exists
precisely because cheap models reliably produce these patterns: it strips
double-escaped quotes, rewrites LIKE '%x%' → contains, IS NULL →
attribute_not_exists, and hoists inline LIMIT to the request parameter. If your
AI is generating "PartiQL" that reads like Postgres, that's the tell.

DynoTable's SQL Workbench: the queries PartiQL can't run

When you genuinely need a JOIN or a GROUP BY, DynoTable's SQL Workbench is
the answer. It validates each JOIN's to-side against a partition key, materializes
the joined rows through DynamoDB's real Query/Scan runtime, then runs a single
SELECT (aggregates, GROUP BY, DISTINCT, CASE, CAST) on top — SQL within
DynamoDB's access-pattern rules.

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

Honest constraints (the Workbench enforces DynamoDB's access model, it doesn't
pretend to be Postgres):

• INNER JOIN and LEFT JOIN only — the ON to-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 conditions and key expressions for the raw API, the
DynamoDB Expression Builder generates the
correct FilterExpression / KeyConditionExpression without the PartiQL surface at
all. For PartiQL done right, see the worked PartiQL examples;
to size what any query will cost, use the
item-size calculator. Note that PartiQL
never changes the wire format — values still travel as DynamoDB-JSON.
Choosing a client? See where the Workbench lands against a
plain DynamoDB GUI or Dynobase.

FAQ

Is PartiQL the same as SQL?
No. PartiQL is a SQL-compatible query language, but on DynamoDB it only exposes
operations that map to a single Get/Query/Scan/Put/Update/Delete. It has no joins,
aggregates, subqueries, or GROUP BY.

Can DynamoDB PartiQL do a JOIN?
No. DynamoDB PartiQL cannot join tables. DynoTable's SQL Workbench can run
INNER/LEFT JOIN (to a partition key or GSI partition key) by materializing the
data through DynamoDB's real query runtime.

Does DynamoDB PartiQL support GROUP BY or COUNT?
No — there are no aggregates or GROUP BY in DynamoDB PartiQL. Use DynoTable's SQL
Workbench for COUNT/SUM/AVG/GROUP BY/HAVING queries.

Why does my SELECT * cost so much?
Without a partition key in the WHERE, PartiQL runs a full-table Scan and meters
every item read before the filter applies. Add a partition-key predicate to turn it
into a Query.

Should I use single or double quotes in PartiQL?
Single quotes for string values ('CUSTOMER#42'), double quotes for identifiers
like table and attribute names ("AppData"). Double-quoting a value is the most
common PartiQL mistake.

Ready to run real SQL against DynamoDB? Download DynoTable and open a
Workbench tab.