DynamoDB PartiQL vs SQL: What Breaks

DynamoDB PartiQL borrows SQL's syntax, not its engine. People and AI assistants both write it like relational SQL, and DynamoDB answers with errors or, worse, an empty result that looks like "no matches".

How is DynamoDB PartiQL different from SQL?

Every PartiQL statement maps onto DynamoDB's native item operations: GetItem, Query, Scan, PutItem, UpdateItem, DeleteItem. It reads like SQL but can only do what those operations do, so there is no JOIN, GROUP BY, subquery or aggregate.

PartiQL has no upsert. When you need overwrite-or-create, call the native PutItem or UpdateItem. There is also no planner that can read two tables, build a hash join, or fold rows into a COUNT.

A SELECT without a partition-key = or IN is a full-table Scan, metered on every item it reads before any filter applies. The worked cost example puts numbers on it; price your own table in the pricing calculator.

What's different, feature by feature

Where the Workbench column says Yes against a PartiQL No, DynoTable's SQL Workbench fills the gap. It reads rows through DynamoDB's own operations, then runs SQL over them: SQL within DynamoDB's access-pattern rules.

Every No in the PartiQL column has a native way out:

• JOIN: query each side by key and merge in your code, or denormalize so one read returns both (JOIN guide).
• GROUP BY and aggregates: Select: 'COUNT' on a Query or Scan counts matches (it still reads each one). For totals, maintain them on write (GROUP BY, COUNT and SUM).
• Subqueries, CTEs, UNION: run each SELECT on its own. Feed the first one's keys into an IN [...] list (50 partition-key values at most) and merge the results in code.
• ORDER BY a non-key column, window functions: sort in code, or model the order as a sort key or GSI sort key.
• Real SQL on demand: the Amazon Athena DynamoDB connector runs joins, aggregates and window functions over your tables; zero-ETL to Redshift runs them on a replicated copy. See SQL for DynamoDB for how each one works.

What breaks, and why

Each of these traces to a DynamoDB constraint.

• SELECT * without a partition key is a hidden Scan. PartiQL won't error by default; it reads every item and filters afterward, the classic Query-vs-Scan footgun. To turn it into an error, deny dynamodb:PartiQLSelect when dynamodb:FullTableScan is true in IAM.
• UPDATE / DELETE need the full primary key. The WHERE must pin the partition key (and the sort key, on a composite-key table). To update every status = 'OPEN' row, query for the keys, then send one UPDATE per item in a batch (worked example).
• Double quotes are identifiers, not strings. "name" is an attribute or table name; 'name' is a string value. A double-quoted value doesn't error: DynamoDB compares against an attribute called ACCOUNT#acme, finds none, and returns zero rows.
• Write IN with square brackets, as AWS documents it: WHERE pk IN ['a','b'], with at most 50 partition-key values or 100 non-key values. Results come back in pages of up to 10 items, so follow the NextToken.
• 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 later.

Why AI assistants get this wrong

Models trained mostly on relational SQL emit JOIN, GROUP BY, LIKE and inline LIMIT, which DynamoDB rejects. They also double-quote string literals, which it doesn't reject: it reads "Alice" as an attribute name and returns nothing.

If your assistant writes "PartiQL" that reads like Postgres, check for those five patterns before you trust an empty result.

SELECT t.ticketId, a.name
FROM tickets t
JOIN accounts a ON t.accountId = a.accountId

SELECT status, COUNT(*) AS tickets, SUM(minutesSpent) AS minutes
FROM tickets
GROUP BY status

SELECT * FROM tickets
WHERE accountId IN (SELECT accountId FROM accounts WHERE country = 'ES')

SELECT ticketId FROM tickets
UNION
SELECT ticketId FROM archived_tickets

SELECT * FROM tickets

UPDATE tickets SET status = 'CLOSED'
WHERE status = 'OPEN'

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

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

DynoTable's PartiQL editor flags the patterns above as you type and offers fixes (IN (…) to […], LIKE to contains or begins_with, a double-quoted value to single quotes).

When you need the JOIN or GROUP BY itself, the SQL Workbench runs one SELECT over rows it reads from your tables. Its JOIN rules, limits and a worked sample live in SQL for DynamoDB.

To compose native KeyConditionExpression and FilterExpression strings instead, use the DynamoDB Expression Builder. Worked statements are in PartiQL examples.

FAQ

Is PartiQL the same as SQL? No. PartiQL is a SQL-compatible query language, but on DynamoDB it only exposes what GetItem, Query, Scan, PutItem, UpdateItem and DeleteItem can do. It has no joins, aggregates, subqueries, or GROUP BY.

Can DynamoDB PartiQL do a JOIN? No. DynamoDB PartiQL cannot join tables. Query each side by key and merge the results in your code, or run the join in SQL through the Amazon Athena DynamoDB connector. DynoTable's SQL Workbench also runs INNER/LEFT JOIN when the joined side is keyed on the partition key of a table without a sort key, or on the partition key of exactly one GSI.

Does DynamoDB PartiQL support GROUP BY or COUNT? No. There is no GROUP BY and no COUNT/SUM/AVG in DynamoDB PartiQL. For a count, use Select: 'COUNT' on a Query or Scan, which still reads every matched item. For totals, keep a counter item updated with an atomic ADD, or run the SQL in Athena or DynoTable's SQL Workbench.

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 = or IN predicate to avoid the scan.

Should I use single or double quotes in PartiQL? Single quotes for string values ('ACCOUNT#acme'), double quotes for identifiers like table and attribute names ("AppData"). A double-quoted value is read as an attribute name, so the query silently returns nothing.

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