# PartiQL: joins, subqueries & set operations

**Single-table design answers the questions you knew about. DynoStudio answers the rest.** The part of the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/) 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](https://www.kanject.com/docs/dynostudio-partiql-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.

**You'll learn**

- Compose **semi-joins and anti-joins** with `IN` / `NOT IN (SELECT …)`, and keep each off a Scan
- Use a **correlated** subquery (1 + N) and keep every per-row run a Query
- Join across tables five ways — `INNER`, `LEFT`, `RIGHT`, `FULL`, `CROSS` — and pick the shape by its cost
- Combine result sets with `UNION` / `INTERSECT` / `EXCEPT`
- Read the multi-request "Executes as" strip and respect the caps

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

```sql
-- outbox rows whose ids come from a DIFFERENT table
SELECT * FROM "stage.Outbox"
WHERE pk IN (SELECT 'Outbox#{Id}' FROM "stage.Events"
             WHERE EventType = 'Bounce' 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 Id`), SQL string concatenation (`SELECT 'Outbox#' || Id` — `||`, never `+`), or a **Kanject key template** (`SELECT 'Outbox#{Id}'`, multi-placeholder and transform-aware). Key templates are why this matters: stored keys are template-shaped, so bare-`Id` subqueries match nothing. Give the subquery a `LIMIT` — an unbounded Scan subquery draws an editor warning, because it runs in full before the outer read.

_Interactive on the web page: type an id and watch 'Customer#{CustomerId}' render the stored key. https://www.kanject.com/docs/dynostudio-partiql-joins/_

```sql
-- anti-join: keep the rows the subquery did NOT produce
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#live'
  AND sk NOT IN (SELECT 'Evt#{Id}' FROM "stage.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)

```sql
-- the subquery references the outer row → 1 + N requests
SELECT * FROM "stage.Orders" AS o
WHERE o.lineId IN (SELECT l.id FROM "stage.Lines" AS l WHERE l.orderId = o.id)
```

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

```sql
SELECT o.orderId, o.total, c.email
FROM "stage.Orders" AS o
LEFT JOIN "stage.Customers" AS c
ON c.pk = 'Customer#{CustomerId}'
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 (`o.customerId = c.customerId`) or the Kanject key-template form (`c.pk = 'Customer#{CustomerId}'`, the single-table workhorse, rendered per left row). 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.

**Try it · Key-aware join**

```sql
SELECT o.orderId, o.total, c.email
FROM "stage.Orders" AS o
LEFT JOIN "stage.Customers" AS c
ON c.pk = 'Customer#{CustomerId}'
WHERE o.status = 'FAILED'
```

_Executes as:_ 2 request groups · drain → BatchGetItem — The left side drains first (its WHERE rides along); the right side resolves per left row through its key — a BatchGetItem, not a Scan. LEFT: an order whose customer row is missing is still kept, with c.email blank.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

## RIGHT, FULL & CROSS joins

```sql
-- keep customers with no orders too (unmatched right rows)
SELECT o.orderId, c.email
FROM "stage.Orders" AS o
RIGHT JOIN "stage.Customers" AS c ON c.customerId = o.customerId

-- every pairing, no ON
SELECT 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.

> **Choosing a join shape by cost:** `INNER` and `LEFT` resolve the right side **per left row through its key** — a `BatchGetItem`, or a Query per partition value — targeted and cheap. `RIGHT`, `FULL` and `CROSS` can't: they **drain the whole right side as a Scan** and join in memory. So reach for the outer/cross shapes only when the right table is small or well-bounded. When it isn't, either **invert the join** (make the small side the left) or add the **GSI** that lets the right side resolve by key — the same Scan-to-Query move from [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/), one level up.

## Multi-join chains

```sql
SELECT o.orderId, c.email
FROM "stage.Orders" AS o
JOIN "stage.Lines" AS l ON l.orderId = o.orderId
JOIN "stage.Customers" AS c ON c.customerId = l.customerId
```

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 c.customerId = l.customerId`) or a **Kanject key template** (`ON c.pk = 'Customer#{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

```sql
SELECT * FROM "stage.Active"  UNION      SELECT * FROM "stage.Archived"
SELECT * FROM "stage.A"       INTERSECT  SELECT * FROM "stage.B"
SELECT * FROM "stage.All"     EXCEPT     SELECT * FROM "stage.Excluded"

-- combined sort + cut, and parenthesized mixing
(SELECT * FROM "stage.A" UNION SELECT * FROM "stage.B")
EXCEPT SELECT * FROM "stage.C"
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.

> **Every shape here folds after a read:** Everything on this page shares one cost shape — drain (capped), then combine client-side — so **every row read is billed, even the ones the fold discards**. A `RIGHT` / `FULL` / `CROSS` join Scans and bills the entire right table; a `UNION` of two Scans is two Scans; a correlated subquery re-reads once per outer row. Keep the drained side a key-condition Query wherever you can, and treat the caps below as the seatbelt, not the target.

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

**AWS background**

- [Creating a single-table design with Amazon DynamoDB](https://aws.amazon.com/blogs/compute/creating-a-single-table-design-with-amazon-dynamodb/) — AWS Compute Blog: access-pattern modeling, composite keys, and single-table relationship patterns.
- [Single-table vs. multi-table design in Amazon DynamoDB](https://aws.amazon.com/blogs/database/single-table-vs-multi-table-design-in-amazon-dynamodb/) — AWS Database Blog: why DynamoDB often avoids joins by modeling reads up front.

**Recap**

- `IN (SELECT …)` is a **semi-join**, `NOT IN (SELECT …)` an **anti-join** — both run the subquery first; only a partition-key `IN` gets the no-Scan key-lookup form.
- A **correlated** subquery inverts to **1 + N** — correlate on the partition key so each per-row run stays a Query.
- `INNER` / `LEFT` resolve the right side per left row through a key (cheap); `RIGHT` / `FULL` / `CROSS` drain the right side as a capped Scan.
- `UNION` / `INTERSECT` / `EXCEPT` run one read per operand and combine client-side; mix them only through parentheses.
- Everything here folds **after** a read — every matched, filtered, or drained row is billed, and each cap refuses with a named fix.

**Try it yourself**

**1. Compose a semi-join**

From `stage.Outbox`, read only the rows whose partition key matches a bounced event in `stage.Events` — stored keys are shaped like `Outbox#<id>`.

_Hint:_ Stored keys are template-shaped, so a bare `SELECT Id` subquery matches nothing. And give the subquery a `LIMIT`.

_Solution:_ The key template re-spells each event id into the stored key shape, and because the outer `IN` lands on the **partition key**, the outer read executes as key lookups — not a Scan.

```sql
SELECT * FROM "stage.Outbox"
WHERE pk IN (SELECT 'Outbox#{Id}' FROM "stage.Events"
             WHERE EventType = 'Bounce' LIMIT 25)
```

**2. Pick the cheap join shape**

This drains all of `stage.Customers` as a capped Scan: `SELECT o.orderId, c.email FROM "stage.Orders" AS o RIGHT JOIN "stage.Customers" AS c ON c.customerId = o.customerId`. You only need customers that *have* failed orders — make the right side resolve by key.

_Hint:_ Only `RIGHT` / `FULL` / `CROSS` drain the right side. `INNER` and `LEFT` resolve the right side per left row through its key.

_Solution:_ Invert the join: with `Orders` on the left, the left side drains once (its WHERE rides along) and each customer resolves through its key — a `BatchGetItem`, not a Scan.

```sql
SELECT o.orderId, c.email
FROM "stage.Orders" AS o
JOIN "stage.Customers" AS c
ON c.pk = 'Customer#{CustomerId}'
WHERE o.status = 'FAILED'
```

**3. Mix set operators safely**

Combine `stage.Active` and `stage.Archived`, drop everything in `stage.Excluded`, and return the first 10 rows by `name`.

_Hint:_ Un-parenthesized operator mixing refuses by design. A trailing `ORDER BY … LIMIT` applies to the combined set.

_Solution:_ Parentheses make the precedence explicit — an operand may itself be a set expression — and the trailing `ORDER BY name LIMIT 10` sorts, then cuts, the **combined** set client-side.

```sql
(SELECT * FROM "stage.Active" UNION SELECT * FROM "stage.Archived")
EXCEPT SELECT * FROM "stage.Excluded"
ORDER BY name LIMIT 10
```

> **Where next?:** You can now read across entities. [Aggregates & profiling](https://www.kanject.com/docs/dynostudio-partiql-aggregates/) folds what you select into answers — counts, sums, groups, and a schema profile of data you've never seen before.

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-joins/ · Kanject Docs_