SELECT — read items

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

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

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

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.

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

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

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

SELECT * FROM "stage.Users"."ByOrg"WHERE 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.

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

SELECT ProductId, Title,       VECTOR_DISTANCE(Embedding, {queryVector}) AS scoreFROM "Products"."ProductEmbeddingIndex"WHERE Category = {category} AND Brand = 'Acme'ORDER BY VECTOR_DISTANCE(Embedding, {queryVector}) ASCLIMIT 10

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

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

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

• notes needs a SEARCH mapping; exact string filtering, sorting, and faceting on status need status FILTER. Full contract: OpenSearch indexes and queries.

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

SELECT sale_id, total_amount FROM dynamodb."Sales"UNION ALLSELECT sale_id, total_amount FROM s3tables."Sales"

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

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

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'

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

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

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

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

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.

SELECT * FROM "stage.Users"WHERE pk IN (SELECT 'User#{CustomerId}' FROM "stage.Orders"             WHERE status = 'FAILED' LIMIT 25)

• Joins, subqueries and set operations have their own guide: Joins, subqueries & sets.