Filter with another query’s results

← SQL examples

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

Find orders belonging to customers in a selected region. The inner query returns customer IDs; the outer query uses those IDs as its filter.

Before you run it: Compare a large intermediate result with a join. Correlated queries have different execution costs.

Think of this as two questions: first, which customer IDs have region IN; second, which orders carry one of those IDs? The inner query supplies the allowed values automatically instead of typing them into an IN list.

Follow the exampleCopy Anchor Link

1. Load the linked customer and order samples.
2. Check the inner query’s customer IDs.
3. Run the complete IN (SELECT …) query.
4. Compare orders with those customer IDs.

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.

# Orders for customers in the 'IN' region.
# The inner SELECT is uncorrelated - it doesn't reference outer columns.
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 customer_id IN (SELECT id FROM shop.customers WHERE region = '\''IN'\'')"
  }'

Check the resultCopy Anchor Link

The inner query runs once because it does not refer to the outer row. Its result is held in memory.

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

Queries that refer to the outer rowCopy Anchor Link

This page shows the uncorrelated shape (the inner runs once, result inlined). Correlated subqueries - where the inner SELECT references an outer column - also execute server-side, evaluated per outer row. You don't have to flatten them to a JOIN (though a JOIN is often the faster plan for large inputs).

• Correlated EXISTS / NOT EXISTS. WHERE EXISTS (SELECT 1 FROM x WHERE x.id = o.x_id) - runs as a semi-join (anti-join for NOT EXISTS).
• Correlated IN. WHERE col IN (SELECT k FROM y WHERE y.j = o.j) - evaluated per outer row.
• Scalar correlated subqueries. WHERE col = (SELECT COUNT(*) FROM y WHERE y.k = o.k) - compared against the per-row scalar.

ContinueCopy Anchor Link

Continue to combine matching records.