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 }
  ]
}

what does NOT work

Correlated subqueries. Anything where the inner SELECT references an outer column - WHERE EXISTS (SELECT 1 FROM x WHERE x.id = o.x_id). Rewrite as a JOIN.
Scalar correlated subqueries. SELECT (SELECT COUNT(*) FROM y WHERE y.k = o.k) FROM o. Aggregate per group with GROUP BY in a separate query.