pettiJoin two SQLite tables and find missing matches
An order stores a customer ID, but a report needs a name. Join the two tables without copying names into every order.
1. Identify the relationship
Open customers and note its id and name columns. Open orders and find customer_id. In this sample, an order’s customer_id refers to customers.id. Repeated customer IDs in orders are expected: one customer can place several orders.
2. Show the customer for every order
In Query, run this statement while the database remains locked. The short names o and c are aliases for the two tables.
SELECT o.id, c.name, o.status, o.total_cents
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;Expect six rows. Orders 101 and 103 belong to Maya Rao, 102 and 105 to Alex Chen, and 104 and 106 to Sam Rivera. JOIN here is an inner join: it keeps matching pairs.
3. Find a customer with no orders
A left join keeps every customer, including one with no matching order. Checking the joined order’s primary key for NULL selects just that missing-match case.
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;The answer is 4, Noor Ali. This is different from a customer with a zero-value order. No order exists for Noor in this fixture.
4. Avoid accidental duplicate totals
One customer can appear on several joined rows because the relationship is one-to-many. If you later join order line items, one order may appear several times too. Summing an order-level total after that join can overcount it. Decide what one output row represents before adding another table or calculating a sum.
Use a declared key relationship when it exists. Similar-looking column names are only a clue in an unfamiliar database. Inspect Structure and confirm the owning application’s schema before assuming the relationship is correct.
Use the result
This pattern answers practical questions: which customer placed an order, which records have no match, and whether a migration left broken references. It does not modify either table. Continue with a monthly summary to turn individual records into totals.
Updated