# SELECT — read items

Read items from a table or a GSI. A `SELECT` with a partition-key equality executes as a native **Query**; without one it's a **Scan** that reads — and bills — every item, flagged amber before it runs. Every example below is quotable: the link icon copies a permanent URL to that exact query.

## Syntax

```sql
SELECT [DISTINCT] column | expr [AS name], …   -- or SELECT VALUE expr
FROM "table" | "table"."index"
[WHERE predicates]
[GROUP BY keys [HAVING condition]]
[ORDER BY key [ASC|DESC] [NULLS FIRST|LAST]]
[LIMIT n] [OFFSET n]
```

Quote table names — especially namespaced ones containing dots (`"stage.Users"`). Keywords read case-blind; **attribute names are case-sensitive on the wire**. Guides: [First queries](https://www.kanject.com/docs/dynostudio-partiql-basics/) for projection and computed columns, [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) for the WHERE reference.

## Use cases

### Fetch one item by its key

The bread-and-butter point read — profile lookups, order details, config rows. The complete key makes it a one-item Query.

```sql
SELECT * FROM "stage.Users"
WHERE pk = 'USER#42' AND sk = 'PROFILE'
```

_Executes as:_ Query · KeyConditionExpression: pk = 'USER#42' AND sk = 'PROFILE'

- Partition-key equality plus the sort key: DynamoDB touches exactly one item.

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#get-item-by-key

### List a partition by sort-key prefix

All events for one order, newest first — `begins_with` on the queried sort key narrows the **key condition itself**, so only the matching slice is read.

```sql
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND begins_with(sk, 'EVT#')
ORDER BY sk DESC
LIMIT 25
```

_Executes as:_ Query · KeyConditionExpression: pk = 'Outbox#123' AND begins_with(sk, 'EVT#')

- `ORDER BY` on the queried sort key stays native — DynamoDB returns index order.

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#list-by-prefix

### Look up by a non-key attribute via a GSI

Find users by organization when the base table is keyed by user id — the `FROM "table"."index"` form reads the alternate key path.

```sql
SELECT * FROM "stage.Users"."ByOrg"
WHERE OrgId = 'ORG#kanject'
```

_Executes as:_ Query · stage.Users.ByOrg · KeyConditionExpression: OrgId = 'ORG#kanject'

- A GSI obeys the same rule: equality on the *index's* partition key is a Query; filtering the index on a non-key attribute is still a Scan of the index.

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#gsi-lookup

### Shape columns with expressions

Build display values in the query — document paths, `||` concatenation, and `CASE` relabelling, all computed client-side after the read and disclosed.

```sql
SELECT Name || ' <' || email || '>' AS contact,
       address.city,
       CASE Status WHEN 1 THEN 'Pending' WHEN 2 THEN 'Shipped' ELSE Status END AS status
FROM "stage.Users" WHERE pk = 'USER#42'
```

_Executes as:_ Query · Projection: Name, email, address, Status → shaped client-side per fetched row

- Client-side shaping changes what you *see*, never what DynamoDB reads or bills — the `WHERE` controls the bill.

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#computed-columns

### Count the rows in one partition

How many events does this order have? The fold has no native form — the partition drains as a Query, then `COUNT` computes client-side.

```sql
SELECT COUNT(*) AS total FROM "stage.Outbox"
WHERE pk = 'Outbox#123'
```

_Executes as:_ Query · stage.Outbox → client fold: COUNT(*)

- Every folded item is read and billed. `SELECT COUNT(*)` with no `WHERE` folds over a full-table Scan — bound it. Full treatment in [Aggregates & profiling](https://www.kanject.com/docs/dynostudio-partiql-aggregates/).

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#count-partition

### Read rows whose keys come from another table

A semi-join: the subquery runs first, its ids re-spelled through a key template; a partition-key `IN` 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)
```

_Executes as:_ Semi-join · 2 native requests — subquery drains, then key lookups

- Joins, subqueries and set operations have their own guide: [Joins, subqueries & sets](https://www.kanject.com/docs/dynostudio-partiql-joins/).

_Quote this example:_ https://www.kanject.com/docs/dynostudio-select/#semi-join-keys

## Try it

**Try it · SELECT**

_Point read_

```sql
SELECT Name, Role FROM "stage.Users"
WHERE pk = 'USER#42' AND sk = 'PROFILE'
```

_Executes as:_ Query · stage.Users — One item, one partition — the cheapest read there is.

_GSI lookup_

```sql
SELECT Name, OrgId FROM "stage.Users"."ByOrg"
WHERE OrgId = 'ORG#kanject'
```

_Executes as:_ Query · stage.Users.ByOrg — The same table read through its alternate key path — still a Query.

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

> **The cost rule, in one line:** A **partition-key equality or `IN` list** is the only thing that keeps a `SELECT` a cheap Query — everything else Scans, and `OR` / `NOT` around key predicates degrade the read too. The editor names the culprit predicate before anything runs.

Related: [First queries](https://www.kanject.com/docs/dynostudio-partiql-basics/) · [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) · [Joins, subqueries & sets](https://www.kanject.com/docs/dynostudio-partiql-joins/) · [Aggregates & profiling](https://www.kanject.com/docs/dynostudio-partiql-aggregates/) · [keyword reference](https://www.kanject.com/docs/dynostudio-partiql-keywords/).

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