PartiQL: joins, subqueries & set operations

Single-table design answers the questions you knew about. DynoStudio answers the rest. The part of the dialect series where one statement compiles to multiple native requests. DynamoDB has no JOIN, can't run a subquery, and has no set operators — the studio composes all three from the reads you mastered in Targeted reads, key-aware and disclosed at every step. This is the page built for single-table designs, where related items reference each other through key templates.

IN / NOT IN (SELECT …): semi-joins & anti-joins

-- user profiles whose keys come from a DIFFERENT tableSELECT * FROM "stage.Users"WHERE pk IN (SELECT 'User#{CustomerId}' FROM "stage.Orders"             WHERE status = 'FAILED' LIMIT 25)

DynamoDB can't run a subquery, so the studio executes two dependent requests: the subquery first (paginated) into a distinct value set, then the outer read — inlined into a native IN list when the set is small and scalar (≤ 100 values), or applied as a disclosed client-side page filter otherwise. An empty value set short-circuits: no outer read at all. When the IN attribute is the partition key — the common case, because that's the shape stored keys take — the inlined form executes as key lookups, one per value, not a Scan. Past the 100-value inline cap a partition-key set fans out into chunked pk IN (≤ 100) key-lookup batches (still no Scan); only a non-key IN past the cap falls back to the full-read page filter, and the strip says which — labelling the run Semi-join · 2 native requests.

The inner projection takes three shapes: a bare attribute (SELECT CustomerId), SQL string concatenation (SELECT 'User#' || CustomerId — ||, never +), or a Kanject key template (SELECT 'User#{CustomerId}', multi-placeholder and transform-aware). Key templates are why this matters: stored keys are template-shaped, so bare-CustomerId subqueries match nothing. (The anti-join below projects sk bare — already key-shaped, no re-spelling needed.) Give the subquery a LIMIT — an unbounded Scan subquery draws an editor warning, because it runs in full before the outer read.

-- anti-join: keep the rows the subquery did NOT produceSELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#live'  AND sk NOT IN (SELECT sk FROM "stage.Outbox"                 WHERE pk = 'Outbox#processed')

NOT IN (SELECT …) is the mirror — an anti-join (the strip labels it Anti-join · 2 native requests). The subquery still runs first, but the outer read keeps the rows whose attribute is absent from the value set. DynamoDB has no negated key condition, so NOT IN never inlines: the outer read is a Scan with a client-side exclusion filter unless another predicate keys it — WHERE pk = 'X' AND sk NOT IN (SELECT …) stays a partition-scoped Query with the exclusion applied as a filter. Two semantics, both disclosed: a row whose attribute is absent or NULL is excluded (a missing value never satisfies NOT IN), and NULLs in the subquery are ignored rather than collapsing the whole result to empty — a friendlier departure from SQL's NOT IN NULL trap. An empty subquery set keeps every present-attribute row, so unlike IN there's no empty short-circuit.

Correlated subqueries (1 + N)

-- the subquery references the outer row → 1 + N requestsSELECT * FROM "stage.Orders" AS oWHERE o.pk = 'Tenant#acme'  AND o.couponCode IN (SELECT i.couponCode FROM "stage.Orders" AS i                       WHERE i.pk = o.pk AND i.status = 'refunded')

When the subquery references the outer row, it's correlated, and the execution inverts: drain the outer read first, then re-run the subquery once per outer row with the correlation value bound. The strip says Correlated semi-join · 1 + N requests; correlate on the partition key so each per-row run is a Query, not a Scan.

Any number of correlations work — inner.x = o.a AND inner.y < o.b — equality or inequality alike, because each o.attr reference is simply bound to that row's value. The outer must be SELECT * (so every correlation attribute is present) and is capped at 500 rows, each of which re-runs the subquery; past that it refuses with the fix. An outer row missing a correlation value binds nothing, so its subquery set is empty (IN → excluded, NOT IN → kept). NOT IN composes here too — a correlated anti-join.

Key-aware joins

SELECT o.orderId, o.total, u.emailFROM "stage.Orders" AS oLEFT JOIN "stage.Users" AS uON u.pk = 'User#{CustomerId}' AND u.sk = 'PROFILE'WHERE o.status = 'FAILED'

DynamoDB has no JOIN — the studio drains the left side (its WHERE and LIMIT ride along), then resolves the right side per left row through its key, four ways:

• BatchGetItem when the ON conditions cover the full primary key — chunked at 100 keys, unprocessed keys drained.
• One Query per distinct partition value when only the base partition key binds — a left row merges once per right match.
• Query on a GSI when the ON binds a GSI's partition key instead of the base key — one Query per distinct value on that index; the strip names it (Query · <index>).
• A refusal when the ON reaches neither the base key nor any GSI key — a materialized-view recommendation instead of an unsafe scan.

ON conditions are equalities, plain (u.tenantId = o.tenantId) or the Kanject key-template form (u.pk = 'User#{CustomerId}', the single-table workhorse, rendered per left row — bind the sort key too and the right side resolves as a BatchGetItem). An INNER join drops a left row that finds no right match; a LEFT [OUTER] join keeps it, with the right columns omitted. (RIGHT, FULL and CROSS take a different path — see below.) One JOIN per statement, explicit AS aliases, and projections name their columns (alias.attr [AS name]) because both sides may share attribute names — * is ambiguous and refused. WHERE filters the left side only.

SELECT o.orderId, o.total, u.email
FROM "stage.Orders" AS o
LEFT JOIN "stage.Users" AS u
ON u.pk = 'User#{CustomerId}' AND u.sk = 'PROFILE'
WHERE o.status = 'FAILED'

RIGHT, FULL & CROSS joins

-- keep users with no orders too (unmatched right rows)SELECT o.orderId, u.emailFROM "stage.Orders" AS oRIGHT JOIN "stage.Users" AS u ON u.pk = 'User#{CustomerId}';​-- every pairing, no ONSELECT a.sku, b.region FROM "stage.Skus" AS a CROSS JOIN "stage.Regions" AS b;

These can't be served by probing the right side per left row — a RIGHT or FULL join has to surface right rows no left row reached, and CROSS pairs everything with everything. So the studio drains the entire right side once — a Scan, capped — and joins in memory: RIGHT keeps unmatched right rows (left columns omitted), FULL keeps unmatched rows from both sides, and CROSS is the Cartesian product (no ON, capped at 10,000 output rows). The strip shows the right step as a Scan and the editor warns — every right item is read and billed, so reach for these only when the right table is small or well-bounded.

With a live connection the editor goes one step further: it refuses up front when the right table is already larger than the 10,000-item cap (sized from the live count), so a doomed Scan never bills before it fails — the same safety stance the rest of the dialect takes on a full-table read.

Multi-join chains

SELECT o.orderId, u.Name, m.NameFROM "stage.Orders" AS oJOIN "stage.Users" AS u ON u.pk = 'User#{CustomerId}' AND u.sk = 'PROFILE'JOIN "stage.Users" AS m ON m.OrgId = u.OrgIdWHERE o.status = 'FAILED'

Two or more JOINs in one statement run as a chain: the base table drains, then each table resolves per accumulated row through its key — BatchGetItem for a full key, one Query per partition value, or a Query on a GSI when the step binds an index key (the strip names it Query · <index>). The accumulated row carries every table's columns alias-qualified, so three or more tables can share an attribute name without colliding, and the final alias.attr projection runs once the chain is assembled. INNER semantics — a row that finds no match at a step drops.

ON conditions are attribute equality (ON m.OrgId = u.OrgId — here binding the ByOrg GSI's partition key, so the step runs as Query · ByOrg) or a Kanject key template (ON u.pk = 'User#{CustomerId}', the single-table-design form — placeholders resolve against the accumulated row, latest joined table first), binding the step table's base key or a GSI key. INNER and LEFT joins chain — a LEFT step keeps an accumulated row that finds no match (this table's columns then absent), so a chain can surface gaps; a RIGHT, FULL, or CROSS join inside a chain refuses. WHERE filters the base table only, and columns name alias.attr. Caps: 10,000 base rows · 1,000 queries and 10,000 items per step · 100,000 joined rows.

Set operations

SELECT * FROM "stage.Users" WHERE status = 'active'    UNION      SELECT * FROM "stage.Users" WHERE Role = 'admin';SELECT * FROM "stage.Users" WHERE Country = 'Nigeria'  INTERSECT  SELECT * FROM "stage.Users" WHERE Role = 'admin';SELECT * FROM "stage.Users" WHERE visits > 10          EXCEPT     SELECT * FROM "stage.Users" WHERE status = 'inactive';​-- combined sort + cut, and parenthesized mixing(SELECT * FROM "stage.Users" WHERE status = 'active' UNION SELECT * FROM "stage.Users" WHERE status = 'inactive')EXCEPT SELECT * FROM "stage.Users" WHERE Role = 'admin'ORDER BY Name LIMIT 10;

UNION / UNION ALL / INTERSECT / EXCEPT have no native form, so the studio runs one read per operand and combines the result sets client-side by deep item equality: UNION de-duplicates, UNION ALL concatenates, INTERSECT keeps rows present in every operand, EXCEPT keeps the first operand's rows that appear in none of the rest. Each operand drains under the usual cap (10,000 items / 100 pages) — every operand's matched items are read and billed.

A trailing ORDER BY [keys] [LIMIT n] after the last operand applies to the combined set: a client-side sort, then the cut. Mixed operators compose through parentheses — (A UNION B) EXCEPT C runs, because an operand may itself be a set expression. What still refuses, by design, is un-parenthesized mixing (A UNION B INTERSECT C): precedence isn't inferred, so parenthesize to say what you mean.

Caps for these features

Multi-request lowerings are capped; every breach refuses with a named fix rather than running away with your bill:

• Semi-join: 10,000 distinct values · 100 pages · inline IN ≤ 100 values. Past the inline cap a partition-key set fans out into chunked key-lookup batches; a non-key set falls back to a disclosed full-read page filter.
• Correlated subquery: 10,000 distinct values per run · outer capped at 500 rows (each one re-runs the subquery).
• Joins: 10,000 left rows · 1,000 right-side queries (INNER/LEFT) · 10,000 right items. The right-side-queries cap refuses with bind the sort key for a BatchGetItem; a join whose ON reaches neither the base key nor a GSI key refuses with the GSI or materialized-view recommendation. RIGHT/FULL/CROSS drain the right side as a capped Scan (10,000 items); a CROSS product caps at 10,000 output rows, and any many-to-many match caps at 100,000 output rows.
• Set operations: each operand 10,000 items · 100 pages.
• Unknown tables, malformed subqueries and unbounded Scan subqueries are flagged in the editor before anything reaches the wire.
• On Professional, these multi-request caps are tunable per install (Settings → Querying) — raising one can increase a query's read cost.

SELECT * FROM "stage.Users"WHERE pk IN (SELECT 'User#{CustomerId}' FROM "stage.Orders"             WHERE status = 'FAILED' LIMIT 25)

SELECT o.orderId, u.emailFROM "stage.Orders" AS oJOIN "stage.Users" AS uON u.pk = 'User#{CustomerId}' AND u.sk = 'PROFILE'WHERE o.status = 'FAILED'

(SELECT * FROM "stage.Users" WHERE status = 'active' UNION SELECT * FROM "stage.Users" WHERE status = 'inactive')EXCEPT SELECT * FROM "stage.Users" WHERE Role = 'admin'ORDER BY Name LIMIT 10