examples · sql · 10 / 13

10. Uncorrelated IN (SELECT ...)

what this does

Two-step query: the inner SELECT returns the list of customer IDs in region 'IN'; the outer SELECT returns orders whose customer_id is in that list. This is the uncorrelated shape - the inner query doesn't reference any column from the outer query, so it runs once and its result is reused.

when to use it

"Find rows in table A where some column matches a result set from table B".
The inner result is small enough to fit in memory (the engine materializes it).
You could also write this as an INNER JOIN with SELECT DISTINCT - they're equivalent. Pick whichever reads more naturally.

the request

POST /v1/tenants/:t/sql

# 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'\'')"
  }'

result = db.sql("""
    SELECT id, amount_cents
      FROM shop.orders
     WHERE customer_id IN (
       SELECT id
         FROM shop.customers
        WHERE region = 'IN'
     )
""")

const result = await db.sql(`
  SELECT id, amount_cents
    FROM shop.orders
   WHERE customer_id IN (
     SELECT id
       FROM shop.customers
      WHERE region = 'IN'
   )
`);

result, _ := db.SQL(ctx, `
    SELECT id, amount_cents
      FROM shop.orders
     WHERE customer_id IN (
       SELECT id
         FROM shop.customers
        WHERE region = 'IN'
     )
`)

what you get back

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

correlated subqueries also execute

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.