DynamoDB PartiQL vs SQL: What's Different (and 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.

The mental model

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

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.

What's different — feature by feature

Each that DynoTable's SQL Workbench can run is marked. The Workbench materialises your tables through DynamoDB's real query runtime and runs real SQL on top — SQL within DynamoDB's access-pattern rules.

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.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customerId = c.PK

SELECT country, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY country

SELECT * FROM orders
WHERE customerId IN (SELECT PK FROM customers WHERE country = 'ES')

SELECT PK FROM orders
UNION
SELECT PK FROM archived_orders

SELECT * FROM orders

UPDATE orders SET status = 'shipped'
WHERE status = 'open'

SELECT * FROM users WHERE "name" = "Alice"

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, materialises the joined rows through DynamoDB's real Query/Scan runtime, then runs your full SQL (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 materialising 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.