5. INNER JOIN - two tables

Combine rows from two tables that share a key. Here, orders + customers joined on o.customer_id = c.id. Returns only orders that do have a matching customer.

• You want each row from one table augmented with related data from another.
• Both sides should have a match. If you want unmatched left rows too, use LEFT JOIN.
• OriginChain supports up to 32 joined tables in a single query.

Uses the shop.orders and shop.customers schemas from Examples 1 and 3.

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

result = db.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
""")

const result = await db.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
`);

result, _ := db.SQL(ctx, `
    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
`)

{
  "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.

• 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.