# PartiQL: first queries

The front door of the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/): read items out of one table and choose what comes back. Two statements in, you'll also know how to read the **"Executes as"** strip — the habit that makes the whole dialect legible.

**You'll learn**

- Run your first `SELECT` against a base table and a GSI
- Project and alias columns, and de-duplicate results with `DISTINCT`
- Build computed columns from expressions, scalar functions, and `SELECT VALUE`
- Read the **"Executes as"** strip to see the exact native request before you run

## Your first SELECT

```sql
SELECT * FROM "stage.Users"                          -- base table
SELECT * FROM "stage.Users"."ByOrg"                  -- a GSI
```

- `FROM "table"` reads the base table; `FROM "table"."index"` reads a GSI. Quote names — especially namespaced ones containing dots (`"stage.Users"`).
- Keywords read case-blind everywhere; **attribute names are case-sensitive on the wire** (native re-emissions use your schema's canonical casing).

That's already a real query. Without a WHERE clause it reads the whole table — fine for a small dev stage, and exactly the thing [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) teaches you to avoid on a big one.

> **Pitfall:** A `SELECT` with no `WHERE` reads the **whole table**. On a small dev stage that's fine; on a large one it compiles to a full-table **Scan** that bills for every item read. The editor flags it amber *with the table's live item count* before you run — [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) is how you keep reads targeted.

## Projection: choose your columns

```sql
SELECT Name AS n, Age FROM "stage.Users"             -- projection + alias
SELECT DISTINCT Country FROM "stage.Users"           -- de-duplicated
```

- `SELECT *` or an attribute list. `AS` aliases are **lowered** — DynamoDB has no `AS`, so sources project natively and rename client-side per fetched item. Top-level attributes only; `ORDER BY` may reference the alias.
- `FROM` aliases (`FROM "stage.Users" AS x WHERE x.pk = …`) are likewise lowered: the alias strips and `x.`-qualified paths re-spell bare before the wire. Explicit `AS` only; paths inside string literals are never rewritten.
- `DISTINCT` de-duplicates each fetched page client-side with **deep value equality** — numbers by value, sets order-insensitive. Duplicates across page boundaries can reappear.

> **How aliases are lowered:** DynamoDB has no `AS`. The studio projects the **native attribute names** and renames each fetched item client-side — so `SELECT Name AS n` ships as a projection of `Name`, with the rename applied after the wire. `FROM` aliases behave the same way: `x.pk` re-spells to a bare `pk` before the request leaves. Every client-side step is disclosed in the "Executes as" strip. **Because it all happens *after* the read, client-side shaping — aliases, computed columns, `DISTINCT` — changes what you *see*, never what DynamoDB reads or bills.** What controls the bill is the `WHERE` clause — covered in [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/).

## Computed columns: expressions & functions

```sql
-- expressions in the SELECT list
SELECT Name, balance * 1.1 AS adjusted, address.city,
       Name || ' <' || email || '>' AS contact
FROM "stage.Users" WHERE pk = 'User#9'

-- SELECT VALUE: each row IS the value
SELECT VALUE {'who': Name, 'next': balance + 1} FROM "stage.Users"
```

A projected column can be an **expression**, not just an attribute: document paths (`address.city`, `tags[0]`), arithmetic, `||` concatenation, comparisons, and tuple/array constructors. The native read projects just the **root attributes** the expressions touch; each row is then shaped client-side, so a column whose value comes out MISSING is simply omitted from that row.

A library of **scalar functions** rides the same grammar — string (`upper`, `substring`), numeric (`abs`, `mod`), null-handling (`coalesce`), date/time (`year`, `to_epoch` / `from_epoch`, `date_add`), and regex (`regex_like`) — each evaluated per fetched row. **`SELECT VALUE`** projects each row *as* the evaluated value rather than a named-column tuple. The [keyword reference](https://www.kanject.com/docs/dynostudio-partiql-keywords/) lists every function with a one-line example; these are all **lowered** — computed client-side and disclosed in the strip.

> **Relabel with CASE:** A `CASE` expression maps a stored value to a label without leaving the query — `CASE Status WHEN 1 THEN 'Pending' WHEN 2 THEN 'Shipped' ELSE Status END AS status`. Both forms work: **simple** (`CASE attr WHEN value …`) and **searched** (`CASE WHEN condition …`). It's a SELECT-list expression like any other, evaluated per fetched row; an unmatched row with no `ELSE` makes that column **MISSING and omitted**. One rule worth knowing early: `CASE` in a *normal read* `WHERE` is **refused** — `WHERE` decides the Query/Scan plan, so it can't be a client-side fold. Full entry in the [keyword reference](https://www.kanject.com/docs/dynostudio-partiql-keywords/).

## Read the "Executes as" strip

Every statement you type is analysed at the keystroke. The **"Executes as"** strip shows the exact native DynamoDB request the statement translates to, live, with one-click copy — and the editor colours intent before you run: key conditions tint green, a predicate that degrades to a full-table Scan flags amber *with the table's live item count*, and anything that won't run is squiggled with the reason.

Make checking the strip a reflex now, while the statements are one-liners. Once joins, subqueries, and aggregates enter the picture, single statements compile to *multiple* native requests — the strip is how you always know what's about to ship.

**Try it · PartiQL**

_Keyed read — Query_

```sql
SELECT Name, balance, address.city
FROM "stage.Users"
WHERE pk = 'User#9'
```

_Executes as:_ Query · stage.Users — One-partition key read — a Query, not a Scan. Native: the statement compiles directly to this request.

_No WHERE — Scan_

```sql
SELECT Name, balance, address.city
FROM "stage.Users"
```

_Executes as:_ Scan · stage.Users — No key condition — the whole table is read and billed, then shaped client-side. The editor flags this amber before you run, sized with the table's live item count.

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

Flip between the two variants above: the **same projection**, but dropping the `WHERE` turns a 1-RCU Query into a 12,480-item Scan — the strip and the footer tell the story before you ever pay for it. Both samples are editable and copyable; live execution against your own tables happens in [DynoStudio](https://www.kanject.com/dynostudio/).

**AWS background**

- [PartiQL for DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) — AWS docs: native PartiQL syntax and DynamoDB's supported statement family.
- [Core components of Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html) — AWS docs: tables, items, attributes, primary keys, and secondary indexes.

**Recap**

- `FROM "table"` reads a base table; `FROM "table"."index"` reads a GSI — always quote namespaced names.
- Projection picks columns; `AS` and `FROM` aliases are lowered (renamed client-side); `DISTINCT` de-duplicates per page.
- Projected columns can be expressions and scalar functions; `SELECT VALUE` projects each row as its evaluated value.
- The **"Executes as"** strip is your ground truth — check it on every statement.

**Check yourself**

**1. Where does `SELECT Name AS n` do its renaming?**

- Client-side, after the read — `AS` is lowered ✓ — DynamoDB has no `AS`: the native request projects `Name`, and the studio renames per fetched item — disclosed in the "Executes as" strip.
- Server-side, inside the native request — DynamoDB has no `AS` — the projection ships native attribute names, and the rename happens after the wire.
- It isn't renamed — `AS` is refused — It runs fine; it's just **lowered**, and the "Executes as" strip says so.

**2. A `SELECT` with no `WHERE` clause compiles to…**

- A full-table Scan, flagged amber with the live item count ✓ — Reading the whole table is sometimes what you want on a small dev stage — the editor just makes the cost visible before you run.
- A Query on the primary key — A Query needs a partition-key equality to aim at — with no `WHERE` there's nothing to aim.
- An error — the statement is refused — It's a legal statement; DynoStudio warns about the cost rather than refusing the read.

**Try it yourself**

**1. Alias a column**

Return each user's `Name` (renamed to `who`) alongside their `balance` from `stage.Users`.

_Hint:_ PartiQL has no `AS` natively — the studio lowers it. You still write it the SQL way.

_Solution:_ The `AS` alias is **lowered**: `Name` is projected natively and renamed client-side per item.

```sql
SELECT Name AS who, balance FROM "stage.Users"
```

**2. De-duplicate a column**

List the distinct `Country` values across the table.

_Hint:_ Remember `DISTINCT` de-duplicates each *fetched page* client-side — duplicates can reappear across page boundaries.

_Solution:_ `DISTINCT` runs client-side with deep value equality.

```sql
SELECT DISTINCT Country FROM "stage.Users"
```

**3. Build a computed column**

Project a single `contact` column shaped like `Ada <ada@x.com>`, combining `Name` and `email`.

_Solution:_ Concatenate with `||`. The native read projects just the root attributes the expression touches (`Name`, `email`); the string is assembled client-side.

```sql
SELECT Name || ' <' || email || '>' AS contact
FROM "stage.Users" WHERE pk = 'User#9'
```

> **Where next?:** You can read a table and shape its columns. [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) is where the dialect starts saving you money: make DynamoDB read only the items you asked about.

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