Combine matching records

← SQL examples

The language tabs use earlier client interfaces. Check SDK versions and connection setup before running them.

Join each order to its customer. A join matches rows from two tables using a shared value, here the customer ID.

Before you run it: Keep the matching rule explicit so the query combines the intended records.

For the sample, two paid orders carry customer_id: "c_1", which matches Alice's id. Each order becomes one result row containing its amount and Alice's email. The customer is not copied into the order table; the query brings the fields together.

Follow the exampleCopy Anchor Link

1. Create and load both tables.
2. Match orders.customer_id to customers.id in ON.
3. Select the columns you need.
4. Read the combined rows.

Run the requestCopy Anchor Link

First load the customers sample and orders sample. This query matches shop.orders.customer_id to shop.customers.id; keep those field names when using the sample. The general schema walkthrough uses a different order field named customer.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT o.id, o.amount_cents, c.email FROM shop.orders o INNER JOIN shop.customers c ON o.customer_id = c.id WHERE o.status = '\''paid'\'' LIMIT 50"
  }'

Check the resultCopy Anchor Link

Only orders with a matching customer appear. Qualified names distinguish fields from the two tables.

{
  "kind": "select",
  "rows": [
    { "o.id": "o_001", "o.amount_cents":  4990, "c.email": "alice@example.com" },
    { "o.id": "o_003", "o.amount_cents":  1250, "c.email": "alice@example.com" }
  ]
}

Note that result keys are dotted - "o.id", "c.email" - because the engine carries the table alias through to disambiguate same-named columns on both sides.

Understand the behaviorCopy Anchor Link

• The planner picks the smaller side (here customers) to build a hash table.
• It then scans the larger side and probes the hash table for each row.
• The WHERE predicate is pushed into the orders-side scan, so only paid orders enter the join.

Important limits and checksCopy Anchor Link

• No ON condition. Use an explicit ON condition for this INNER JOIN. A CROSS JOIN is a different operation and can multiply the result size; check its supported scope before using it.
• Ambiguous column names. If both sides have a column called id, qualify with the alias - o.id, c.id.

ContinueCopy Anchor Link

Continue to keep records without a match.