examples · sql · 4 / 13

4. WHERE col BETWEEN low AND high

what this does

Match rows whose column value falls inside a range. Both bounds are inclusive: BETWEEN 1000 AND 10000 is the same as >= 1000 AND <= 10000.

when to use it

Price ranges, age ranges, date ranges (with timestamps stored as u64 epoch ms).
Anywhere you'd otherwise write col >= X AND col <= Y.

the request

Uses the same shop.orders schema and seed from Example 3.

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, amount_cents FROM shop.orders WHERE amount_cents BETWEEN 1000 AND 10000 LIMIT 50"
  }'

result = db.sql("""
    SELECT id, amount_cents
      FROM shop.orders
     WHERE amount_cents BETWEEN 1000 AND 10000
     LIMIT 50
""")

const result = await db.sql(`
  SELECT id, amount_cents
    FROM shop.orders
   WHERE amount_cents BETWEEN 1000 AND 10000
   LIMIT 50
`);

result, _ := db.SQL(ctx, `
    SELECT id, amount_cents
      FROM shop.orders
     WHERE amount_cents BETWEEN 1000 AND 10000
     LIMIT 50
`)

what you get back

{
  "kind": "select",
  "rows": [
    { "id": "o_001", "amount_cents":  4990 },
    { "id": "o_003", "amount_cents":  1250 }
  ]
}

common mistakes

Off-by-one. BETWEEN is inclusive on both ends. If you mean exclusive, use col > low AND col < high.
Money as floats. Always store money as integer minor units (i64 cents). Floats accumulate rounding error - never reliable for range predicates on money.