SELECT — read items

View .md

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 exprFROM "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 for projection and computed columns, Targeted reads for the WHERE reference.

Use cases

Fetch one item by its key Native

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 asQuery · KeyConditionExpression: pk = 'USER#42' AND sk = 'PROFILE'
  • Partition-key equality plus the sort key: DynamoDB touches exactly one item.
List a partition by sort-key prefix Native

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 DESCLIMIT 25
Executes asQuery · KeyConditionExpression: pk = 'Outbox#123' AND begins_with(sk, 'EVT#')
  • ORDER BY on the queried sort key stays native — DynamoDB returns index order.
Look up by a non-key attribute via a GSI Native

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 asQuery · 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.
Shape columns with expressions Lowered

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 statusFROM "stage.Users" WHERE pk = 'USER#42'
Executes asQuery · 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.
Count the rows in one partition Lowered

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 asQuery · 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.
Read rows whose keys come from another table Lowered

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 asSemi-join · 2 native requests — subquery drains, then key lookups

Try it

Try it · SELECTSample
Run it live in DynoStudio →
Executes asQuery · stage.Users
Query KeyConditionExpression: pk = 'USER#42' AND sk = 'PROFILE' · Projection: Name, Role
One item, one partition — the cheapest read there is.
#NameRole
1Ada Okaforadmin
Fetched 1 item in 9ms · 100% efficient · 1 RCU · 1 partition

Related: First queries · Targeted reads · Joins, subqueries & sets · Aggregates & profiling · keyword reference.

Was this page helpful?