examples · sql · 6 / 13

6. LEFT JOIN - keep unmatched left rows

what this does

Every row from the left side (customers) comes back, even if no matching row exists on the right (orders). When there's no match, the right-side columns are null. In the response below, customer c_3 has no orders, so o.id is null for them.

when to use it

You want to keep the parent row even if there are no child rows. Think "all customers, with their orders if any".
To find rows on the left that have no match on the right - add WHERE o.id IS NULL after the join. (Note: filtering on the right side of a LEFT JOIN can defeat the LEFT semantics if done wrong - put right-side predicates in the ON clause.)

Mirror variants: RIGHT JOIN keeps every row from the right; FULL OUTER JOIN keeps every row from both sides. Both are supported.

the request

POST /v1/tenants/:t/sql

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

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

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

result, _ := db.SQL(ctx, `
    SELECT c.id, c.email, o.id
      FROM shop.customers c
      LEFT JOIN shop.orders o ON c.id = o.customer_id
     LIMIT 20
`)

what you get back

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

common mistakes

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.