examples · sql · 3 / 13

3. WHERE col IN (...)

what this does

Return rows whose status matches any value in a small set of literals. IN (...) is the OriginChain-supported way to express what x = 'pending' OR x = 'paid' would mean in traditional SQL - OR in WHERE isn't supported yet, so use IN for value alternatives.

when to use it

"Match one of these values" - small enumerations, status fields, category lists.
As a replacement for OR-of-equalities.
The IN list is short (under ~100 values). For larger sets, consider a JOIN against a temp table.

the schema

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"]

seed data

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" }
  ]'

the request

POST /v1/tenants/:t/sql

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"
  }'

result = db.sql("""
    SELECT id, status
      FROM shop.orders
     WHERE status IN ('pending', 'paid')
     LIMIT 50
""")
for row in result.rows:
    print(row["id"], row["status"])

const result = await db.sql(`
  SELECT id, status
    FROM shop.orders
   WHERE status IN ('pending', 'paid')
   LIMIT 50
`);
if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row.id, row.status);
  }
}

result, _ := db.SQL(ctx, `
    SELECT id, status
      FROM shop.orders
     WHERE status IN ('pending', 'paid')
     LIMIT 50
`)
for _, row := range result.Rows {
    fmt.Println(row["id"], row["status"])
}

what you get back

{
  "kind": "select",
  "rows": [
    { "id": "o_001", "status": "paid" },
    { "id": "o_002", "status": "pending" },
    { "id": "o_003", "status": "paid" }
  ]
}

how it works

The translator folds status IN ('pending', 'paid') into a disjunction over equalities internally and dispatches the index lookups in parallel. From the engine's perspective each candidate value is treated like a separate IndexScan and the results are merged.

common mistakes

Very long IN lists. A few hundred values is fine. Thousands? Bulk-insert the list into a temp table and JOIN. The engine accepts long IN lists but the parser overhead grows with size.
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.