Keep records without a match

← SQL examples

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

List every customer, including customers who have no orders. LEFT JOIN keeps the left table’s rows when the right table has no match.

Before you run it: A right-table filter in WHERE can remove unmatched customers. Put match-specific filters in ON when those customers must remain.

The sample customer c_3 has no order. A LEFT JOIN keeps that customer and fills the missing order fields with null, meaning there is no matching value. An INNER JOIN would leave that customer out.

Follow the exampleCopy Anchor Link

1. Load the linked customer and order samples.
2. Put customers on the left of the join.
3. Run the query.
4. Find the customer whose order fields are null.

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 c.id, c.email, o.id FROM shop.customers c LEFT JOIN shop.orders o ON c.id = o.customer_id LIMIT 20"
  }'

Check the resultCopy Anchor Link

The sample keeps customer c_3 and fills missing order fields with null.

{
  "kind": "select",
  "rows": [
    { "c.id": "c_1", "c.email": "alice@example.com", "o.id": "o_001" },
    { "c.id": "c_1", "c.email": "alice@example.com", "o.id": "o_003" },
    { "c.id": "c_2", "c.email": "bob@example.com",   "o.id": "o_002" },
    { "c.id": "c_3", "c.email": "carol@example.com", "o.id": null }
  ]
}

Important limits and checksCopy Anchor Link

• Right-side predicates in WHERE. WHERE o.status = 'paid' after a LEFT JOIN turns it back into an INNER JOIN (because the unmatched rows have null status, which fails the predicate). Put right-side predicates in the ON clause if you want LEFT semantics preserved.
• Confusing LEFT and RIGHT. If you find yourself reaching for RIGHT JOIN, swap the table order and use LEFT - it reads more clearly.

ContinueCopy Anchor Link

Continue to summarize rows by status.