# SELECT — read items

Read from a DynamoDB table or GSI, a native vector index, an OpenSearch projection, an S3 Tables analytics replica, or a configured virtual hybrid table. The source decides the native operation, consistency, and meter; the Executes-as strip names that plane and every client-side composition before the run. 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] [PARALLEL n]

-- vector-index shape
SELECT projected_column,
       VECTOR_DISTANCE(vector_attr, {queryVector}) AS score
FROM "table"."vector-index"
[WHERE search_schema_attr = value [AND …]]
ORDER BY VECTOR_DISTANCE(vector_attr, {queryVector}) [ASC|DESC]
LIMIT 1..100

-- explicit query planes (Professional, read-only)
FROM dynamodb."table" | s3tables."table" | opensearch."index" | hybrid."table"
-- OpenSearch shorthand: FROM OPENSEARCH "index"
```

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://dynostudio.dev/docs/dynostudio-partiql-basics/) for projection and computed columns, [Targeted reads](https://dynostudio.dev/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://dynostudio.dev/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://dynostudio.dev/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://dynostudio.dev/docs/dynostudio-select/#gsi-lookup

### Find the ten nearest products

Rank projected products by a query embedding while pinning the index's category partition and optional brand filter.

```sql
SELECT ProductId, Title,
       VECTOR_DISTANCE(Embedding, {queryVector}) AS score
FROM "Products"."ProductEmbeddingIndex"
WHERE Category = {category} AND Brand = 'Acme'
ORDER BY VECTOR_DISTANCE(Embedding, {queryVector}) ASC
LIMIT 10
```

_Executes as:_ SearchVectors · ProductEmbeddingIndex · TopK 10 · eventually consistent · byte-metered

- `LIMIT` is required (`1`–`100`), selected fields must be projected, and the live descriptor must be ACTIVE with backfill finished. Full contract: [Vector similarity search](https://dynostudio.dev/docs/dynostudio-partiql-reads/#vector-similarity-search).

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-select/#vector-top-k

### Full-text search with highlights and facets

Search an eventually consistent order projection by analyzed notes while filtering, sorting, highlighting, and returning typed status buckets.

```sql
SELECT orderId, status, total
FROM OPENSEARCH "open-orders"
WHERE notes MATCH 'urgent refund' AND status = 'OPEN'
ORDER BY status ASC LIMIT 50
HIGHLIGHT (notes)
FACET status
```

_Executes as:_ OpenSearch Query DSL · match notes · term status · sort status · highlight notes · terms facet status

- `notes` needs a `SEARCH` mapping; exact string filtering, sorting, and faceting on `status` need `status FILTER`. Full contract: [OpenSearch indexes and queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/).

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-select/#opensearch-full-text

### Merge live and replica rows explicitly

Read the same projected columns from DynamoDB and its S3 Tables replica, preserving duplicates and showing each leg's freshness.

```sql
SELECT sale_id, total_amount FROM dynamodb."Sales"
UNION ALL
SELECT sale_id, total_amount FROM s3tables."Sales"
```

_Executes as:_ DynamoDB SELECT leg + Athena/Iceberg SELECT leg → client-side UNION ALL

- Both legs must stay bounded. Plain UNION, combined ORDER BY/LIMIT, and a leg that itself spans planes refuse. Full contract: [Hybrid and cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/).

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-select/#cross-plane-union-all

### 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://dynostudio.dev/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://dynostudio.dev/docs/dynostudio-partiql-aggregates/).

_Quote this example:_ https://dynostudio.dev/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.Users"
WHERE pk IN (SELECT 'User#{CustomerId}' FROM "stage.Orders"
             WHERE status = 'FAILED' 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://dynostudio.dev/docs/dynostudio-partiql-joins/).

_Quote this example:_ https://dynostudio.dev/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://dynostudio.dev/dynostudio/

> **The source is the bill:** For ordinary DynamoDB table and GSI reads, a partition-key equality or `IN` list keeps the access path a Query; otherwise it Scans. Vector search is byte-metered, OpenSearch uses search capacity, and S3 Tables runs through Athena. Cross-plane shapes pay for every native leg before the client-side merge. The editor names the chosen path first.

Related: [First queries](https://dynostudio.dev/docs/dynostudio-partiql-basics/) · [Targeted, vector & parallel reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/) · [OpenSearch indexes and queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/) · [Hybrid and cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/) · [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/) · [Aggregates & profiling](https://dynostudio.dev/docs/dynostudio-partiql-aggregates/) · [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/).

---
_Source: https://dynostudio.dev/docs/dynostudio-select/ · DynoStudio Docs_