Match any value in a list
The language tabs use earlier client interfaces. Check SDK versions and connection setup before running them.
Find orders with either of two statuses. IN keeps a short list of allowed values in one filter.
Before you run it: The notes below explain why NULL needs care with NOT IN.
- Read the sample ordersUse the order status column.
- Allow pending or paidSupply both accepted values to IN.
- Read the qualifying ordersThe sample returns the three matching orders once each.
Follow the example
- Register and seed the order table.
- Put the allowed statuses in the IN list.
- Run the query.
- Check the returned statuses against the list.
Set up the table
Save this definition as orders.toml and send it to POST /v1/tenants/:t/schemas with Content-Type: text/plain before loading rows. The schema walkthrough shows how to register a file; use this page's definition for its sample.
namespace = "shop"
table = "orders"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "str"
required = true
[[columns]]
name = "customer_id"
ty = "str"
[[columns]]
name = "amount_cents"
ty = "i64"
[[columns]]
name = "status"
ty = "str"
[[indexes]]
name = "by_status"
columns = ["status"]Load sample rows
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/_batch" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
-d '[
{ "id": "o_001", "customer_id": "c_1", "amount_cents": 4990, "status": "paid" },
{ "id": "o_002", "customer_id": "c_2", "amount_cents": 12900, "status": "pending" },
{ "id": "o_003", "customer_id": "c_1", "amount_cents": 1250, "status": "paid" }
]'Run the request
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT id, status FROM shop.orders WHERE status IN ('\''pending'\'', '\''paid'\'') LIMIT 50"
}'Check the result
Rows match when their status equals a listed value.
{
"kind": "select",
"rows": [
{ "id": "o_001", "status": "paid" },
{ "id": "o_002", "status": "pending" },
{ "id": "o_003", "status": "paid" }
]
}How the database handles this request
A row matches when its status is pending or paid. The supplied dataset has two paid orders and one pending order, so all three are returned. An index can help the database find those rows; the result still contains each matching row once.
Important limits and checks
- Long lists. Large lists add parsing and query work. For a repeatedly used set of IDs, consider a separate lookup table and a supported join; do not assume temporary-table syntax is available.
- NOT IN with NULL. If any value in the list is NULL, NOT IN returns no rows (standard SQL three-valued logic). Filter NULL explicitly before negating.