Find rows with an exact value
The language tabs use earlier client interfaces. Check SDK versions and connection setup before running them.
Find customers whose region is IN. An index is a lookup structure that can find matching rows without reading every row.
Before you run it: String equality is case-sensitive.
- Load the customer sampleEach customer has a region value.
- Filter region to INUse the exact value in the WHERE condition.
- Read Alice's matching rowCheck that customers from other regions are excluded.
Follow the example
- Load the customers from the linked setup.
- Send the query with the region in single quotes.
- Check the returned region.
- Use EXPLAIN to check whether the index is used.
Run the request
First register and load the customers sample, which defines id, email and region and supplies three rows.
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, email, region FROM shop.customers WHERE region = '\''IN'\'' LIMIT 10"
}'Check the result
The sample returns Alice’s customer record. Without an index, the query may scan the table.
{
"kind": "select",
"rows": [
{ "id": "c_1", "email": "alice@example.com", "region": "IN" }
]
}How the database handles this request
The database needs only rows whose region is IN. The sample schema defines by_region, an index on that field. An IndexScan in the plan means the engine uses that lookup; a Scan means it reads rows and tests the filter. Check the actual plan instead of inferring it from the query text.
Run EXPLAIN SELECT ... on the same query to see whether your indexes are being used.
Important limits and checks
- Quoting strings as double-quoted. SQL strings use single quotes -
WHERE region = 'IN'. Double-quoted identifiers refer to columns, not strings. - Case sensitivity. String equality is case-sensitive:
'IN'doesn't match'in'. Lowercase at write time if you want case-insensitive lookups. - Matching several values in one column. Use an IN list to keep that filter concise.