PartiQL & SQL

You paste SQL habits into PartiQL, run SELECT * FROM tickets WHERE status = 'OPEN', and DynamoDB maps it to a table Scan that bills every item. PartiQL borrows SQL syntax; it does not borrow a relational planner.

PartiQL runs SELECT, INSERT, UPDATE and DELETE against one table per statement. There is no JOIN, no GROUP BY, and no aggregate function at all.

For those you have native options: pre-computed aggregate items, an export to S3 queried with Athena, or the Athena DynamoDB connector. DynoTable's SQL Workbench runs them on your desktop: it scans the FROM table and reads a keyed JOIN's other side by key.

What this section teaches you to do

Predict whether a PartiQL statement becomes a Query or a full-table Scan.
Know that DynamoDB applies PartiQL DML immediately, and stage it in DynoTable when you want a diff first.
Get JOIN, GROUP BY and aggregate answers natively or in the Workbench.
Explain to teammates why "SQL on DynamoDB" still reads by key or by scan, and what each one costs.

Reading order

SQL for DynamoDB: what SQL you get on DynamoDB, and where to run JOIN and GROUP BY: Athena, Redshift, Hive on EMR, the Workbench.
PartiQL vs SQL: the syntax overlap, the engine limits, and what breaks.
PartiQL examples: SELECT/INSERT/UPDATE/DELETE patterns that map cleanly to DynamoDB APIs.
JOIN: denormalize, single-table design, app-side joins with BatchGetItem, and SQL engines for the ad-hoc join.
GROUP BY: atomic counters, Streams + Lambda rollups, export to S3 + Athena, and app-side grouping.
COUNT, SUM, aggregates: what Select=COUNT costs, why ItemCount is stale, and where SUM/AVG come from.

0 of 7 readQuiz

SQL for DynamoDB and the Limits of PartiQL

Can you query DynamoDB with SQL? PartiQL handles SELECT, INSERT, UPDATE and DELETE by key, but not JOIN, GROUP BY or COUNT(*). Here is what works.

Beginner8 min read

DynamoDB PartiQL vs SQL: What Breaks

DynamoDB PartiQL looks like SQL but has no JOIN, GROUP BY, subqueries or aggregates. Each statement maps to native item operations. Here is what breaks.

Beginner8 min read

PartiQL for DynamoDB by Example

PartiQL for DynamoDB by example — SELECT, INSERT, UPDATE, DELETE with SQL-like syntax, plus the gotchas around partition keys and full-table scans.

Beginner6 min read

DynamoDB JOIN: How to Join Tables

DynamoDB has no JOIN and PartiQL doesn't add one. Why, the four workarounds devs use, and how DynoTable's SQL Workbench runs a real JOIN.

Intermediate10 min read

DynamoDB GROUP BY: How to Aggregate Instead

DynamoDB has no GROUP BY, SUM or AVG, and PartiQL adds none. Atomic counters, Streams + Lambda rollups, export to S3 + Athena, and app-side grouping.

Intermediate11 min read

How to COUNT, SUM and Aggregate in DynamoDB

How to COUNT, SUM and aggregate in DynamoDB: what Select=COUNT costs, why there is no native SUM or AVG, and the patterns that give you real totals.

Beginner6 min read

Knowledge checkTake the quiz

Check what you’ve learned in this section.

SELECT count(*) FROM events doesn't run at all: PartiQL rejects it with ValidationException: Unexpected path component at 1:8:5. The native count is Select=COUNT on a Scan or Query, which still bills every item it evaluates.

A Workbench JOIN reads the FROM table, then looks up each match on the joined table by key. Narrow the FROM read with a key condition first; the Workbench joins and groups only what it fetched.

Try it in DynoTable

Download DynoTable. The SQL Workbench editor is available on free read-only plans with autocomplete powered by a local inferred schema. Paid plans add running Workbench queries and exports.

PartiQL SELECT is free to run. PartiQL DML (INSERT, UPDATE, DELETE) stages like any other edit: Commit changes writes it in one step, Stage changes leaves a reviewable diff, and either way the write is journaled.

The cost status bar on Workbench and PartiQL tabs shows ~N items · ~R RCU when the plan is known, so a statement that becomes a Scan shows before you run it. For a JOIN it covers the FROM read; joined lookups are flagged, not priced.

A Workbench Execute reads one page; Run to end reads every page, with no item cap. On 500k items of ~2 KB, a keyless full read is about 125,000 read request units ($0.016 at us-east-1 list price); a keyed Query takes a handful.

The PartiQL examples work through that cost, and the pricing calculator prices your own table.

Read the SQL Workbench docs for JOIN and aggregate limits, and use the query builder for the same access pattern without hand-written API JSON.